There are two components (generic) we can use in SSIS to add scripting, a Script Task and a Script Component. The Script Component exists within a Data Flow and can act as a Data Source, a Transformation somewhere between a Source and Destination, or a Destination. In this case we would add a Script Component and choose to configure it as a Destination meaning it would take a data input and act as the endpoint of that SSIS pipeline meant to result in the application of that data to a database or some other data store.

As for the package itself it might look something like this:

And here is some pseudo code for the Script Component:

/* 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

{

#region added by opc.three

private int rowCount;

private string destinationTableName;

#endregion

public override void PreExecute()

{

base.PreExecute();

#region added by opc.three

rowCount = 0;

#endregion

}

public override void PostExecute()

{

base.PostExecute();

}

public override void Input0_ProcessInputRow(Input0Buffer Row)

{

#region added by opc.three

if (rowCount % 1000 == 0)

{

// todo: create a new table and store name in destinationTableName

}

// todo: insert row into destinationTableName

// increment counter

rowCount += 1;

#endregion

}

}