Spliting records into multiple rows using SSIS Script Component

Hi folks.
This is a post in response to Add Interim Rows Depending on Max and Min Values question asked in MSDN SSIS Forum, but it could be applied to any similar situation and problem. HEre I am gonna describe the steps needs to be taken to split the records coming from a data base table into multiple records and insert them into a destination table.

Let’s say we have a table with the following data in it:

Key  min  max
A     0    3
B     2    5

and we need to get:

Key  Values
A      0
A      1
A      2
A      3
B      2
B     …
to
B     5

out of it. This situation could be easily addressed using SSIS Script Component. The steps to achieve that are as follows:

1- Open BIDS, create a new Integration Services project, and open Package.dtsx file;

2- Add a new Data Flow Task to the package, and double-click on it to navigate to Data Flow tab;

3- Add a Script Component to your data flow. When the “Select Script Component Type” dialog box opens, select Source and hit OK;

4- Open Script Component by double-clicking on it and navigate to “Inputs and Outputs” tab. Add 2 Output columns by selecting “Output Columns” folder and hitting Add Column button, as displayed in the following picture:

5- Add a new Conenction Manager to “Connection Managers” tab to point to your instance of SQL Server DB:

6- Now navigate to the Script tab, and hit Edit Script. This will get you to Visual Studio Tools for Applications (VSTA), where you can edit your script’s code. Replace the code with the following snippet:

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Data.SqlClient;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public classScriptMain : UserComponent

{

          privateSqlConnection cnn;

          publicoverridevoid AcquireConnections(object Transaction)

{

                      //base.AcquireConnections(Transaction);

                      IDTSConnectionManager100 cnnMgr = this.Connections.Connection;

                      string cnnstr = cnnMgr.ConnectionString.Substring(cnnMgr.ConnectionString.IndexOf(“Data Source”), cnnMgr.ConnectionString.IndexOf(“Provider”));

cnnstr +=“Integrated Security=SSPI;”;

                   cnn = new SqlConnection(cnnstr);

}

           publicoverridevoid PreExecute()

{

                        base.PreExecute();

cnn.Open();

}

          publicoverridevoid PostExecute()

{

                           base.PostExecute();

cnn.Close();

}

        publicoverridevoid CreateNewOutputRows()

{

                          SqlCommand cmd = newSqlCommand(“SELECT * FROM ss”, cnn);

                         SqlDataReader reader = null;

reader = cmd.ExecuteReader();

                          while (reader.Read())

{

                                          for (int i = (int)reader[1]; i <= (int)reader[2]; i++)

{

Output0Buffer.AddRow();

Output0Buffer.oName = (string)reader[0];

Output0Buffer.oValue = i;

}

}

}

}

7- Add a new OLE DB Destination to the Data Flow tab. Get the Output (green arrow) of the Script component and conenct it to OLE DB Destination just added. Configure OLE Db Destination to insert data into the destination table:

Run the package. You’ll get the splitted records into your destination table.