In this step by step SSIS Tutorial I will show you how to use SSIS OLE DB Command Transformation . For the purpose of this tutorial I will use SSIS 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
In this tutorial I will explain how to use OLE DB Command so lets start from replaying what is OLE DB Command. OLE DB Command allow us to execute our SQL for each row it receives from the Input.
So we have got OLE DB Source with connection to AdventureWorksDW2012.
My SQL shows EmployeeKey and SalariedFlag set to False. My task in this article will be to change False to Truth only for only for this three EmployeeKey. And I will do that using OLE DB Command. So OLE DB Command will receive those three rows with EmployeeKey and It will executed three times for each row.
Very often in OLE DB Command We have to perform some kind of updates depending on our source so in this case we only perform updates based for EmployeeKey. So my updates update DimEmployee we Set the False to one which is True and Where EmployeeKey equals question mark. So the question mark is my parameter. So we will use this question,mark to map it to our source and more specifically EmployeeKey So we only update for each row we receive we only update the same EmployeeKey ans set the flag to True.
So let's drag OLE DB Command and create data path. I double click on OLE DB Command so Advanced Editor should be appears.
In Advanced Editor I go to first tab Connection Menages and I set up Connection AdventureWorksDW2012.
Once connection is setup I go to Component Properties tab and In SQLCommand I click on SQLCommand to open String Value Editor and I paste the SQL. So we have got the SQL that will be executed for each row it receives but the important bit is WHERE EmployeeKey = ? so we will perform Mapping using column Mapping tab.
No I need to decided which value I want to use for Param0 and for Param0 I want to use EmployeeKey That is past during the execution so I mup it and click ok.
So I execute it and we can see 3 rows where past to OLE DB command which means that OLE DB command executed 3 times.
So lets check the result so that is our previous SQL before we have 3 rows for 3 EmployeeKeys with the flag set to False so we should have the flag set to True. So that is how OLE DB command works. It allows you to execute SQL For each row.