http://www.sqlservercentral.com/blogs/dknight/2009/11/07/ssis-_2D00_-data-provider-does-not-allow-parameters-in-ole-db-source/

Printed 2014/09/01 05:32AM

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

2009/11/07

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.