Share via

Using SSIS to read data from any table returned by an RFC/BAPI with SAP ADO

  • Article

EDIT: In the Adapter Pack V2, this option is supported out-of-the-box (the EXEC syntax in the SAP ADO Provider supports specifying which resultset should be returned as the first one). Please refer to the documentation on the supported syntax for this.

SSIS source data flow components, by default return only the first result set returned by the underlying ADO. When working with BAPIs and RFCs that return multiple tables, the SSIS component would take the first table that it comes across. To demonstrate how you can access other tables, we have put together a sample SSIS source data flow component that takes as input the ADO query and the desired table’s name in the configuration stage. When it is run, it skips the result sets as they are returned by the ADO till it comes across the table that was specified in the configuration stage, making it possible to use any table returned by an RFC or BAPI in SSIS and not just the first one.

Here is a screenshot of how the component configuration property grid looks like:

SAP ADO SSIS Sample

To use this custom component, after downloading and unzipping the attached file, you need to follow these steps:

1. Build the project or use the pre-built assembly in the bin\debug folder

2. Add the assembly to GAC as well as C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents

3. Open up an Integration Services project

4. Go to the Data Flow View

5. In Data Flow sources toolbox, right click and select ‘Choose Items..’

6. Go to the SSIS Data Flow Items Tab

7. Select “SAP ADO Sample Component”

SAPAdoSource.zip

Comments

  • Anonymous
    March 16, 2010
    The comment has been removed
  • Anonymous
    December 26, 2010
    Hi,try to re-build the project, you will find that there is a missing reference.Regards,Ahmad Elayyan
  • Anonymous
    December 26, 2010
    to solve this problem, you should to all add the SAP reference from Biztalk adapter, also you need to change some pic of code to take 100 instead in 90 in reference.this Solution from Abdullah Zarour.Regards,Ahmad El.
  • Anonymous
    September 30, 2012
    Thanks guys
  • Anonymous
    August 30, 2013
    pls share query syntax in the custom properties