SSIS Script Component

In SQL Server Integration services, we have Script Component, which can be used in Data Flow Task as Source, Destination or Transformation.  This one task needs little more understanding than the other components we have in SSIS.

1. How you want to use the Script Component?

Script Component as Source

If you selected Source, configure the Column of the source in the Script Component Editor.  You can have one more by using OUT OUTPUT buffer.  For each output buffer add COLUMN and configure the data types of columns.  Click on EDIT SCRIPT to provide details.

    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void CreateNewOutputRows()
    {
        for (int i = 1; i <= 100; i++)
        {
            Output0Buffer.AddRow();
            Output0Buffer.RollNum = i;
            Output0Buffer.Name = "Ram " + i.ToString();
        }
    }

In the generated code (main.cs) you will see three place holder methods. 

• PreExecute – Execute once at the starting of the component.  Useful to set variable values.



• PostExecute – Executes once at the end of component execution.



• CreateNewOutputRows – This is the method you can generate the new rows.  In the above code I am using a loop to generate 100 rows of dummy data.

Based on the name you provided in in “Input and Output Column” setting of Script Component Editor.  The name is chosen.  In the above example it was Output0Buffer, which is default.

Script Component as Transformation

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.CustomerName = Row.CustomerName + " - " + Row.CustomerCode;
    }

You can mark the columns ReadWrite and edit the value using the ProcessInputRow method placeholder.  You can set ExclusionGroup property to non-zero value to create more OUTPUT buffers.

Script Component as Destination

It is very similar to SC as Transformation, except that you can not have more output buffers as it is irrelevant.  You can process the data row by row using ProcessInputRow method.

Refer Script task as Source