Tag: SSIS

Excel Source on SSIS server fails.

Recently I had a strange issue with an Excel Source in a SSIS package.
While developing the SSIS package everything worked perfectly. Even after the initial deployment to an Acceptance stage and various user tests everything was working fine.

Until one day. Shortly after we got a new excel source data file. When the package was executed by a developer it worked well. But when a business user started the package it ran into the follwing error.

….Description: Opening a rowset for “Sheet$” failed.
Check that the object exists in the database…..

Of course there was a sheet in the excel file named “Sheet”.

It took nearly a whole day of reserach until we found the real problem behind.
Best help was this blog post here :
Strange Error Loading Excel Files (xlsx) using SQL Server Integration Services

Reason:
The reason for failing is that the package is executed in the context of the windows user (which is intended in our case) and the business (windows domain) user does not have any permission on a general temp folder on the server which the MS Access/Excel driver is utilizing when a excel source file is getting larger than a specific threshold.

Solution:
So you can fix that problem by granting write permissions on the folder
C:\Users\Default\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO
to all users executing any packages with Excel Sources.

Happy packaging….

Make hidden properties accessible in SSIS Packages.

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.
SAP_BW_Source_DataFlow

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”.

SSIS_Property_ExpressionType 

 

 

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.
DataFlowTask

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

ParametrizeDataFlow

 

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.