This describes how to consume data from SAP without using a custom component. There are several prerequisites to get this working.
- SAP adapter packs from the MS Biztalk server.
- SAP client DLLs from SAP.
- Acess to BAPIs in SAP to call.
Download the Biztalk server demo (BizTalk Server 2013 R2 Evaluation Edition) from http://www.microsoft.com/en-au/download/details.aspx?id=43381 Install only the adapter packs (32bit and 64bit).
Extract librfc32u.dll and librfc32.dll from SAP (64 bit into C:\Windows\system32 and 32 bit into C:\Windows\SysWOW64). Do the same for the unicode libraries icuuc40.dll icudt40.dll icuin40.dll. They can be obtained from http://icu-project.org/ and the version depends on the version required by librfc32u.dll (both 32bit and 64bit).
Note: 32 bit versions of dlls and adapters are required on 64 bit systems for Visual Studio development.
Add the ADO.NET source component to the SSIS workflow. Choose SAP ADO.NET as the source and configure connection details. Use the following syntax to call the BAPI
exec ZBAPI_FETCH_GLPCT
@YEAR=2012 ,
@RECORDTYPE = '1' ,
@RECORDVERSION = '000'
OPTION 'disabledatavalidation, firstresultset IT_OUTPUT_GLPCT'
Notes on the exec:
- The ADO NET component can only return the first result set which by default is the RETURN table. This only returns the metadata on the resultsets returned.
- Using OPTION 'firstresultset TABLENAME' changes the first result set to be the table or structure that should be returned first.
- Using OPTION 'disabledatavalidation' passes most fields as string and conversion has to be done manually within the workflow. This is required for dealing with bad dates. Remove this option to quickly check the metadata of each field in the dataflow.
Note: The syntax shown on the Microsoft page for quoting OPTION settings is incorrect.
It's been moved.. http://www.microsoft.com/en-au/download/details.aspx?id=43381