Using SSIS to read data from any table returned by an RFC/BAPI with SAP ADO
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:
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”
Attachment(s): SAPAdoSource.zip
