Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

SSIS - Data Provider Does Not Allow Parameters in OLE DB Source

I recently worked on a project that involved loading a Data Warehouse from a DB2 source.  In this project we used the Microsoft OLE DB Provider for DB2 Data Provider for the ETL process in SSIS. 

Unfortunately, one of the limitations to using this Data Provider is the the OLE DB Source does not allow you to pass in parameters which I do frequently in Data Warehouses for incremental loading.  The work around I did for this was to store the query I wanted to use in my source in a table on the SQL Server side with a place holder for for my parameter, something like:

Select *

From DimEmployee

Where RowStartDate > %parameter% 

Then I used a stored procedure with a REPLACE function to change my placeholder with the value I want in SSIS. 

After doing the prep work of storing the query and writing the stored procedure I used SSIS to execute the stored procedure in an Execute SQL Task and pass in the parameter I want using the task.  Store the results in a variable and then you can use that variable as your source query in the OLE DB Source.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.