If the above requirements are all met, we will begin by launching Microsoft Visual Studio 2008.

Create a new project Integration Services Project which is located under Business Intelligence Projects.

After you have named the new project, proceed to click and drag the script task in the Control Flow pane of the new package.

Right-click the script task and click on “Edit”

Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2008”.

In Project Explorer import relevant references and ensure that you have declared namespaces as below:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;

After declarations, create an instance of the application and package:

Insert Data Flow Task:

Insert your connection:

The rest of the code should look as follows:

Variable Counter = p.Variables.Add(“var_counter”, false, “User”, 0);

IDTSComponentMetaData100 dataConvertComponent = dataFlowTask.ComponentMetaDataCollection.New();
dataConvertComponent.ComponentClassID = “DTSTransform.RowCount”;
dataConvertComponent.Name = “get row counter”;
dataConvertComponent.Description = “gets row counter”;

CManagedComponentWrapper dataConvertWrapper = dataConvertComponent.Instantiate();
dataConvertWrapper.ProvideComponentProperties();
dataConvertWrapper.SetComponentProperty(“VariableName”, “User::var_counter”);

// Connect the source and the transform
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(component.OutputCollection[0],
dataConvertComponent.InputCollection[0]);

IDTSComponentMetaData100 destination =
dataFlowTask.ComponentMetaDataCollection.New();
destination.ComponentClassID = “DTSAdapter.OleDbDestination”;
destination.Name = “OLEDBDestination”;

CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();

destination.RuntimeConnectionCollection[0].ConnectionManagerID = cm_DES.ID;
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(cm_DES);

destDesignTime.SetComponentProperty(“OpenRowset”, “your_destination_table”);
destDesignTime.SetComponentProperty(“AccessMode”, 3);
destDesignTime.SetComponentProperty(“FastLoadOptions”, “TABLOCK,CHECK_CONSTRAINTS”);

IDTSPath100 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(dataConvertComponent.OutputCollection[0], destination.InputCollection[0]);

IDTSInput100 destinationInputerr = destination.InputCollection[0];
IDTSVirtualInput100 destinationVirtualInputerr = destinationInputerr.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumnserr =
destinationVirtualInputerr.VirtualInputColumnCollection;

destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();

IDTSInput100 input = destination.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
// Select column, and retain new input column
IDTSInputColumn100 inputColumn = destDesignTime.SetUsageType(input.ID,
vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
// Find external column by name
IDTSExternalMetadataColumn100 externalColumn =
input.ExternalMetadataColumnCollection[inputColumn.Name];
// Map input column to external column
destDesignTime.MapInputColumn(input.ID, inputColumn.ID, externalColumn.ID);

}

ConnectionManager ConMgr = p.Connections.Add(“OLEDB”);
ConMgr.ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “your_server”, “your_database”);
ConMgr.Name = “ConMgr”;

Executable exec = p.Executables.Add(“STOCK:SQLTask”);
TaskHost th = exec as TaskHost;
th.Properties[“Name”].SetValue(th, “insert row count”);
th.Properties[“Description”].SetValue(th, “insert row count”);
th.SetExpression(“SqlStatementSource”, “\”insert into your_table_name values (‘\” + (DT_WSTR, 100) @[User::var_counter] +\”‘)\””);
th.SetExpression(“Connection”, “\ConMgr\”);

PrecedenceConstraint pcFileTasks3 = p.PrecedenceConstraints.Add((Executable)e, (Executable)exec);
pcFileTasks3.Value = DTSExecResult.Success;
We then save the package into a file system.

Dts.TaskResult = (int)ScriptResults.Success;
SIFISO_app.SaveToXml(“C:\\TEMP\\pkg_create_DTS.dtsx”, dyna_pkg, null);

It’s that simple!

You can now execute your script task and the package will be created in the location you specified.