This is about a problem my colleagues and I researched several days until a solution was found.

The Problem:
For a big customer we are creating several solutions with large data transfers.
Despite the fact we are using Microsoft BizTalk Server as the main platform for our solutions we prefer SSIS (SQL Server Integration Services) for processing large data in projects (for obvious reasons).
This big customer has a bunch of SAP environments as well as some SAP Business Information Warehouse systems.
So we planned to extract “some data” from one of the SAP BW with a SSIS package using the Microsoft Connector for SAP BI which works pretty fine in general.
But there is one big pain with this component. Depending on the mode you choose, different properties appear which have to be set.
In my case it was the “SAP BW Source” Dataflow Component.

If you choose the mode “E – Extract” in design time you have to set the following values:
OHS destination and RequestID.

For the “OHS destination” you normally have a fixed value, but for the RequestID you will receive a value on runtime. So I can not hard-code the value in there.
The regular way to solve this in SSIS packages is to define an expression which sets the value on runtime depending on a variable.
But in this case Microsoft denied that. You can not set the value.

That makes the Microsoft Connector for SAP BI pretty unusable for me.

The Solution:
After some days of extensive search I figured out the following. There is a simple solution (which is def. not supported by Microsoft!) which enables you to make a “hidden” property available for expressions. You just have to open your package (which is an XML internally) as a text file (either in Visual Studio or any other text editor) and add/change the following value.
Navigate to the property you want to make “visible” (just search for it) in my case it is “RequestID” and add or change the attribute “expressionType” on the element to “Notify”.

 

 

 

Save the modified package and re-open it in the Visual Studio Package Designer.
Now go ahead and choose “Parameterize” from the context menu of the Data Flow task where the SAP BW Source Component resides in.

Choose the the newly visible property from the list on top and assign a new or already existing variable.

 

Press OK and you’re done!

That’s it. Normally your attribute changes are kept even if you do some other changes on the package. In case it got lost, just do it again.

Please feel free to leave a comment below about your ideas, thoughts, critics.