Script Component Task

Introduction

Script component is a SSIS transformation component whose task is to run custom script code. Many times it happens that for some situation we do not have a built in transformation component; however, we can do so by writing some code snippet for the needed transformation. Script component come into play in such situations.

Background

Transformation is an integral part of most of the SSIS life cyle. Once the raw data comes to our hand, it is responsibility of the transformation components to make the needed morphisms and bring the data in the needed format. Though various kind of transformations are available in SSIS, but some time we need to have some kind of custom transformation for which no component is available. We can either use Script component in such situations or make our own custom component. In this article, we will look into the script component transformation into action by using two real time examples while usage of custom component will be discussed in another article.

Example 1: A String Splitter Program in SSIS using Script Component Transformation.

Context

In this program we will read the file contents which is given as under

 Collapse | Copy Code

Id Value
1 Name1, Name2, Name3
2 Name4, Name5, Name1

and by using Script Component transformation we will bring the below output

Step to be carried out

Step 1:

Open Bids.Choose Integration Services Project from the available project type.Drag and drop a Dataflow Task in the control flow designer.

Drag and drop Flat File Source in the Data Flow designer.Right click on the Flat File Source component and from the popup, click Edit… to bring the Flat File Source Editor. Alternatively we can double click on the Flat File Source component for bringing up the Flat File Source Editor. In the connection manager of the Flat File Source Editor, click on the New button and specify the source file

In the Columns tab, the Row delimiter should be {CR}{LF} while the Columns delimiter should be Tab {t}

Click OK button.

Step 2:

Add a Script Component Transformation and set it as transformation.

Step 3:

Add precedence constraint from Flat file Source to the Script component.Right click on Script component and from the popup, click Edit… to bring up the Script Transformation Editor.

Step 4: Configuring the script component

In the Input Columns tab, add the two available columns: Column 0, Column 1

In Input and Outputs tab, select the Output 0, and rename it as Result.Set the SynchronousInputID property toNone which will rather change the script component to asynchronous.

N.B. ~ there are two types of transformation in SSIS.

Synchronous Transformation

The output is synchronized with the input and the input data will be processed on a row by row basis.

Asynchronous Transformation

The output is not synchronized with the input. All the input data will be fetched initially, then all the rows will be read and followed by the output generation.

Add the below output columns under Result

Column name	DataType
ID	string [DT_STR]
CustomerName	string [DT_STR]

In Script tab, set Script Language as Microsoft Visual C# 2008, and let's click on Edit Script button

Override the Input0_ProcessInput method to fetch all data till end of file as below:

 Collapse | Copy Code

public override void Input0_ProcessInput(Input0Buffer Buffer)
    {
        while (Buffer.NextRow())
        {
            Input0_ProcessInputRow(Buffer);
        }

        if (Buffer.EndOfRowset())
        {
            ResultBuffer.SetEndOfRowset();
        }
    }

Next we need to override the Input0_ProcessInputRow method to add new rows to output as below:

 Collapse | Copy Code

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
      var arr = Row.Column1.Split(','); // Splitting the rows of Names column

      foreach (string str in arr)
      {
         ResultBuffer.AddRow(); //Adding rows to the Result Buffer

         //If the Names are not empty or Null, then set the values to the  
         //  corresponding Result Buffer properties
            if (!string.IsNullOrEmpty(Row.Column1)) 
            {
                ResultBuffer.ID = Row.Column0;
                ResultBuffer.CustomerName = str;
            }
        }
    }

Build the application and close it.

Step 5:

Add a Row Sampling and enable the data viewer. The final package design looks as under

Let us now run the package and we will the below output