OLEDB transformation in SSIS SQL Server.

Scenario :

There are situations where the parameters from different sources in Data flow task  need to be passed to the sql server. They can be either to an sql statement or a stored procedure. This blog explains all about it how to pass the columns from different sources to the stored procedures in SSIS.

Step by Step:

1) Create a data flow task with OLEDB connection source.

2) Drag and drop OLEDB Command transformation.

3) Connect both of the OLEDB source and OLEDB command transformation.

4) Pull some data for the source. I used the following data from AdventureWorks2008R2


SELECT * FROM Person.CountryRegion


5) The data above is fed to the OLEDB transformation as shown above.

6) A stored procedure can be used in OLEDB transformation with parameters which are fed from the OLEDB source.

For Eg:

CREATEPROC spTEST @name NVARCHAR(MAX)

AS

BEGIN

SELECT * FROM Person.CountryRegion WHERE Name = @NAME

END


7) Now, the most important part in this is the mapping between columns received and the variables in the stored procedure.

Observe as below :

 Thus the OLEDB transformation can execute sql or stored procedures with the parameters fed from OLEDB source or matter any source in SSIS.

 Good luck everyone.