The Script Component is one of the most powerful tools in SSIS. It can be used as a Source, Destination, and Transformation.
In this blog, I will show you how to use the SSIS Script Component as a Transformation with a example. Normally, we can use Script Component to do some task that is not possible for the built-in transformations, or we can use SSIS Script Component as a Transformation to combine the work of multiple transformations in one place.
Example Data
Before we start creating the SSIS Script Component as a Transformation package, Let us see what the data we are going to use. If you want to know what the example doing you can look into like SSIS : Compare two flat files
We have one of big Merge Full Outer Join result and need to compare ‘field'(come from left join) and ‘field(1)’ (come from right join). I am going to using Script Component to do this compare. Below the the merge output screenshot, there are more than hundred field in both left and right join table.
Create Script Component as transformation
To create Script Component as transaformation:
Step 1 : Drag and drop Script Component in the SSIS toolbox to the data flow region. Once you drop the Script component, a new pop up window called Select Script Content Type opened. Then selecting the Transformation option
Step 2 : Double click on the Script component will open the following editor to configure the properties.
Step 3: Within the SSIS Script Component as Transformation Input Columns tab, you can cross-check the input columns.
Step 4: Within the Input and Outputs tab, Go to Output Columns to add output columns.
Step 5 : Within the Script tab, please click on the Edit Script.. button to write the actual C# Script
Once you click on the Edit Script, it will open the main.cs class file to write the C# code. Please write your custom code inside the Input0_ProcessInputRow(Input0Buffer Row) function
Step 6 : Add your custom C# code here. For this example, I am using C# reflection to do comparison. In this way, I don’t need to compare field by field, even if column name changed we don’t need to change the comparison code.
Step 7 : Once you finished editing the Script, Please close the main.cs file. Next, drag the OLE DB Destinations, and join the script component Output Arrow to this new OLE DB Destination
