Programmatically Create Data Flow Task with Row Count Transformation in SSIS Package Using C#

By | June 23, 2011
Abstract
This article explains how to programmatically create data flow task with row count transformation in SQL Server Integration Services Package Using C#.
Requirements
Article
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 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:
After declarations, create an instance of 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\””);

Conclusion
It’s that simple!
You can now execute your script task and the package will be created in location you specified.

13 thoughts on “Programmatically Create Data Flow Task with Row Count Transformation in SSIS Package Using C#

  1. subhransu

    Hi Sifiso,
    Thanks for your reply.Any other way to get the number of row count.i am waiting to your valued input.Thanks!!!

    Best Regards
    subhransu

    Reply
  2. subhransu

    Hi Sifiso,
    Its a great topic..I understand lot of hard work has been done to create a topic like this.I am having one question , when i am capturing the value of the variable” int v = (int)p.Variables[“User::var_counter”].Value;”, value is showing zero(0)…why the value always zero, it should show the exact row count.

    Regards
    subhransu

    Reply
  3. subhransu

    Hi Sifiso,
    This is a great topic,lot of hard work has been done to write the code here.
    I am having one question,when i capturing the varibale “int v = (int)p.Variables[“User::var_counter”].Value” always getting value zero(0)….why i am not getting the exact value of rowcount?

    Regards
    subhransu

    Reply
  4. Zennoposter Templates

    Hey there, You have performed an excellent job. I’ll certainly digg it and in my view recommend to my friends. I’m confident they will be benefited from this website.

    Reply
  5. asset based lending

    It is in reality a great and helpful piece of information. I am happy that you simply shared this useful information with us. Please keep us informed like this. Thank you for sharing.

    Reply

Leave a Reply