SSIS - How To Use Script Component AS Source [Generate Numbers In Data Flow]

Scenario:

We want to generate Numbers from 1-99 inside Data Flow Task without using any Source such as OLE DB or File.

Solution:

We can use Script Component Transformation as Source and generate numbers. 

Step 1: 
Create a new SSIS Package and Drag Data Flow Task on Control Flow Pane. Inside Data Flow Task, bring Script Component Transformation and choose to use as Source.

Step 2 :
Double Click on Script Component Transformation. Go to Inputs and Outputs Tab and add a new column SeqNo as shown below


Step 3: 
Click on Edit Tab and then Click on Edit Script and paste below Code. The only Code I added is in Red.


/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    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()
    {
        for (int i = 1; i < 100; i++)
        {
            Output0Buffer.AddRow();
            Output0Buffer.SeqNo = i;
        }
        /*
          Add rows by calling the AddRow method on the member variable named "Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
    }

}


Step 4:
Drag Multicast Transformation to Data Flow Pane and connection Script Component Transformation to it.Add Data Viewer between them and execute Package.


4 comments: