How to use a Script Component as a Data Source in SSIS

Script component in SSIS can be used as Data Source, Data Transformation and Data Destination. Here is small tutorial on how to use Script Component as data source in SSIS package. See below step by step guide to add script component as data source in SSIS data flow task.

1.    Add Data flow task to Control Flow of your package.

2.    Right Click on Data Flow Task and click on Edit.

3.    Add Script Component to your Data Flow. It will popup window to ask to set the role of script task in data flow (see below image).

4.    Select Source click Ok.

5.    Now right click on Script Component and click on Edit.

6.    Click on Inputs and Oputputs. Add few columns to default output (Output 0). You can change name of the output but I kept it as default for this example. If you want script task to return multiple data tables, then you can add more outputs and their columns.

7.    Now Add few columns to your Output. Here I have added 3 columns to Output 0 named Column, Column 1 and Column 2. You can change name and data type of the column from right side property window. I have kept the default name and default type (Integer) for this example.

8.    Click on Script tab and click on Edit Script… button to add the rows to your output.

9.  Edit Script will open new visual studio project and opens main.cs file (for C#, it will be main.cv for VB). This file contails ScriptMain class, which has 3 predefined methods as shown below.

public override void PreExecute()

This method is used to perform any prexeuction task like opening connection, initialization valiables etc.

public override void PostExecute()

This method is used to perform any posexecution task like closing connection, dispising objects etc. This method also be used to set ReadWrite variable defined in your script.

     

public override void CreateNewOutputRows()

      This method is used to actually returns the rows to your ourput. See below example where I have added 10 rows to our Output 0

      public override void PreExecute()

      {

            base.PreExecute();

            /*

            Add your code here for preprocessing or remove if not needed

            */

      }

      public override void PostExecute()

      {

            base.PostExecute();

            /*

             Add your code here for postprocessing or remove if not needed

            You can set read/write variables here, for example:

            Variables.MyIntVar = 100

            */

      }

      public override void CreateNewOutputRows()

      {

            /*

            Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".

            For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".

            */

            for (int i = 0; i < 10; i++)

            {

                  Output0Buffer.AddRow();

                  Output0Buffer.Column = (i + 1);

                  Output0Buffer.Column1 = (i + 1) * 10;

                  Output0Buffer.Column2 = (i + 1) * 100;

            }

      }

10.Add sample code as shown above and close visual studio project for script component. Click on Script Component Edit dialog window. Now your script task is ready to use as Data Flow Source. Start using it as normal source component.