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


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


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;

public class ScriptMain : UserComponent

    public override void PreExecute()
          Add your code here for preprocessing or remove if not needed

    public override void 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.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.

1 comment:

  1. The information that you have shared is really useful for everyone. If someone wants to know about EHS Software and Occupational Safety Softwares I think this is the right place for you.