SSIS 2008 now talks to SAP BI Database..

Microsoft now offers a connector for SAP BI in Sql 2008 Integration Services (Enterprise and Developer Editions only). The Microsoft Connector for SAP BI is a set of managed components for transferring data to or from SAP NetWeaver BI version 7.0 systems.

This connector comes as a part of the Microsoft Sql Server 2008 Feature Pack, make sure you have the latest Feature pack of April, 2009 from:

https://www.microsoft.com/downloads/details.aspx?familyid=B33D2C78-1059-4CE2-B80D-2343C099BCB4&displaylang=en

Couples of notes before you get started:

Required SAP Files:

To use the Microsoft Connector for SAP BI, you do not have to install the SAP Front End software (SAP GUI) on the local computer.

However you must copy the SAP .NET connector file, librfc32.dll, into the system subfolder in the Windows folder. (Typically, this folder location is C:\Windows\system32.)

Considerations for 64-bit Computers:

The Microsoft Connector for SAP BI fully supports the 64-bit version of Microsoft Windows. On a 64-bit computer, the Microsoft Connector for SAP BI has the following additional requirements:

1) To run packages in 64-bit mode on any 64-bit Windows operating system, copy the 64-bit version of the SAP GUI file, librfc32.dll, into the system32 folder of the Windows folder. (Typically, this file location is C:\Windows\system32.)

2) To run packages in 32-bit mode on any 64-bit Windows operating system, copy the SAP GUI file, librfc32.dll, into the SysWow64 folder of the Windows folder. (Typically, this folder location is C:\Windows\SysWow64.)

After getting these all set, I created my first SSIS Package and configured it to pull data from SAP BI Database using the SAP BI Source component and insert it in a Sql Server Destination. When I ran the package in BIDS, the source component turns yellow and hangs there forever without any activity. I checked the Progress tab and found the last message to be “The component is waiting for a notification”. Having no clues, I tried running the package from command line using DTExec.exe and got the following error:

===========================================================================

System.OutOfMemoryException

at System.String.PadHelper(Int32 totalWidth, Char paddingChar, Boolean isRightPadded)

at Microsoft.SqlServer.Dts.SapBw.Connectivity.RFCStructure..ctor(RFCTableColumnCollection Columns)

at Microsoft.SqlServer.Dts.SapBw.Connectivity.RFCParameter.RetrieveColumnsByStructure(R3Connection Connection, String StructureName)

at Microsoft.SqlServer.Dts.SapBw.Connectivity.R3Connection.AddParametersAndTablesToUndefinedFunctionObject(RFCFunction& Dest, String Name)

at Microsoft.SqlServer.Dts.SapBw.Connectivity.R3Connection.CreateFunction(String Name)

at Microsoft.SqlServer.Dts.SapBw.Components.OHSExtractor.CheckMetadata()

at Microsoft.SqlServer.Dts.SapBw.Components.OHSExtractor.ExtractNow(Boolean InRuntimeMode, String GatewayHost, String GatewayService, String ProgramID, String ProcessChain, String DestinationName, String ExecutionMode, String PropertyRequestID, Int32 Timeout)

===========================================================================

I tried this on a couple of other machines as well but was not able to execute the package successfully. However, I noticed one difference in the error message based on the processor architecture. In x86 machines I got the error stated above, but in x64 machines I got:

===========================================================================

System.ArgumentOutOfRangeException: Non-negative number required.

Parameter name: totalWidth

at System.String.PadHelper(Int32 totalWidth, Char paddingChar, Boolean isRightPadded)

at Microsoft.SqlServer.Dts.SapBw.Connectivity.RFCStructure..ctor(RFCTableColumnCollection Columns)

at Microsoft.SqlServer.Dts.SapBw.Connectivity.RFCParameter.RetrieveColumnsByStructure(R3Connection Connection, String StructureName)

at Microsoft.SqlServer.Dts.SapBw.Connectivity.R3Connection.AddParametersAndTablesToUndefinedFunctionObject(RFCFunction& Dest, String Name)

at Microsoft.SqlServer.Dts.SapBw.Connectivity.R3Connection.CreateFunction(String Name)

at Microsoft.SqlServer.Dts.SapBw.Components.OHSExtractor.CheckMetadata()

at Microsoft.SqlServer.Dts.SapBw.Components.OHSExtractor.ExtractNow(Boolean InRuntimeMode, String GatewayHost, String GatewayService, String ProgramID, String ProcessChain, String DestinationName, String ExecutionMode, String PropertyRequestID, Int32 Timeout)

===========================================================================

Note in both the case it was complaining about a parameter called “totalWidth” (I DO NOT have any such parameter anywhere in my package).

We discussed this with our SSIS Product group and confirmed that this issue occurs because of a anomaly that causes the connector to incorrectly recognize whether the server uses a big-endian or little-endian architecture. Therefore, when allocating the memory, the memory size is parsed incorrectly. This leads to a System.OutOfMemoryException or System.ArgumentOutOfRangeException. For example, the memory size that should be allocated is 40(0x 00 00 00 28). However, the memory size is recognized incorrectly as 671088640(0x 28 00 00 00).

Come on; Its gotta work!!

Good news is that Microsoft has implemented a fix for this issue in Microsoft.SqlServer.SapBiComponents.dll. This fix is released as a part of Cumulative Update 4 for Sql Server 2008 SP1 and can be downloaded from:

https://support.microsoft.com/kb/973602

This update should upgrade the version of Microsoft.SqlServer.SapBiComponents.dll to version 10.0.2734, under default installation of the connector this dll can be found at:

C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents

Note – It’s been reported by few customers that even after applying the Cumulative Update, the version of Microsoft.SqlServer.SapBiComponents.dll does not get upgraded and the issue persists. I am researching more into this and update my blog as soon as I have some obvious result.

Author : Debarchan (MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Malcom (MSFT) , SQL Escalation Services, Microsoft