Skip to main content

Hello everybody!

 

I’m facing the following situation when trying to execute an RFC trought CData connector for SAP ERP...

In SAP, I've created an RFC to retrieve additional data for our needs, it works and have these parameters:

 

The PT_DOCNUM has the following structure:

 

It receives a list of documents and fiscal years to further processing as input in PT_DOCNUM. The returning data is in CT_DATA.

According to this page (https://cdn.cdata.com/help/RYJ/rssis/pg_datamodel.htm) I can call the function trough EXEC statement.

I’m using SSIS inside Visual Studio 2019 to achieve this step. The test flow is as follows (Using the CData Source component):

 

The first attempt was to fill the DOCS#TEMP table and execute the RFC in the same call:

 

When I execute the above, the RFC is called but no parameter is received on the function (PT_DOCNUM headerline and table are both empty):

 

The second approach, If I use the static JSON command to pass the values it works, for example:

 

Data is retrieved with a bottom line including only the parameters:

 

I tried to use different components like the CData SAPERP Lookup, no avail. And I also tried to put out of the dataflow, by using CData Tasks in the control flow:

 

Where the first task selects the data and saves into the DOCS#TEMP table and the second task EXECs using the temp table, also it doesn’t work...

 

Task 1:

 

Task 2:

So my question is: How can I use the CData (or other SSIS components) to pass the result of a SELECT into a temporary table as the parameter for na EXEC call?

Hi @cbaptista,

 

You might want to explore the ResultSet section as a potential solution. Here is what I mean by that. In this scenario, I'm demonstrating the utilization of the Sharepoint provider, specifically with a stored procedure named DownloadAttachment, which requires two parameters:

  1. File → The path of the file to be saved.
  2. RemoteFile → The path of the file on the server. This can be the full URL or simply the file name. If you use the name of the file, the latest version is downloaded.

Consider this SQL Query → SELECT URL FROM Files WHERE Name='Account.csv'

By executing this query, I extract the value from the URL column in Sharepoint and designate it as the RemoteFile parameter.

Visualize the following flow:

In the first Task, I have utilized the following properties:

  • SQLSourceType → Direct Input
  • Command Type → Command Text
  • SQLStatement → SELECT URL FROM Files WHERE Name='Account.csv' (Considering that this query yields a single result in my particular scenario)
  • ResultSet → Single Row (reflecting the fact that only one record is expected)

In the Result Set section, I'll establish a variable called User::URL:

As for the second Task, I have utilized these properties:

  • SQLSourceType → Direct Input
  • Command Type → Stored Procedure
  • SQLStatement → eCData].iREST].SDownloadAttachment] (this is the name of the procedure which I was able to select from the dropdown list)

In the Parameter Mapping section, I've linked the Parameter Name RemoteFile to the value of User::URL retrieved from the previous section. (Additionally, I've assigned a local variable called File_Name to the File Parameter Name in this example.)

Upon saving these configurations, you can go ahead and start the process and your stored procedure will get successfully executed.

If the Stored Procedure doesn't appear in the drop-down list, you can utilize the following format:

In the Parameter Mapping section (please ensure to use the correct Parameter Name in this case):

This example should guide you in utilizing the stored procedure as needed. The distinction in your case lies in utilizing two parameters simultaneously, necessitating the addition of another parameter in the Result Set section when employing the query → SELECT DOCNUM, GJARH FROM ZVBW_NFDOC WHERE CREDAT >= format(getdate(),'01.MM.yyyy') AND CREDAT <= format(getdate(),'dd.MM.yyyy')

 

Kindly keep in mind that you can execute one Stored Procedure at the time. Therefore, if the result obtained from this query exceeds one, adjustments to your flow are necessary. For instance, you can implement a For/ForEach Loop to execute the Stored Procedure for each set of values obtained from the Result Set. Additionally, you would need to change the ResultSet from Single Row to Full ResultSet to accommodate multiple records.


Reply