Programmatically Create Data Flow Task inside a Sequence Container Using C#

By | June 3, 2011

Abstract

This article explains how to create an SSIS package with a Data Flow Task inside a Sequence Container using C# programming language.

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:

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 application and package:
Application SIFISO_app = new Application();
Package dyna_pkg = new Package();

Create a connection to AdventureWorks2008R2 database.
ConnectionManager ConMgr = dyna_pkg.Connections.Add(“OLEDB”);
ConMgr.ConnectionString = “Provider=SQLOLEDB.1;” +
“Integrated Security=SSPI;Initial Catalog=AdventureWorks2008R2;” +
“Data Source=(local);”;
ConMgr.Name = “ConMgr_OLEDB”;
ConMgr.Description = “OLE DB connection to the AdventureWorks2008R2 database.”;

Insert a Sequence container:
Sequence exec_SEQ = (Sequence)dyna_pkg.Executables.Add(“STOCK:SEQUENCE”);
exec_SEQ.FailPackageOnFailure = true;
exec_SEQ.FailParentOnFailure = true;
exec_SEQ.Name = @”select SIFISO Sequence Container”;
exec_SEQ.Description = @”select SIFISO Sequence Container”;

Add the following Execute SQL Tasks:
Executable exec = exec_SEQ.Executables.Add(“STOCK:SQLTask”);
TaskHost th = exec as TaskHost;
th.Properties[“Name”].SetValue(th, “Create View”);
th.Properties[“Description”].SetValue(th, “Drops and Create SQL View which based on Adventureworks database”);
th.Properties[“Connection”].SetValue(th, “ConMgr_OLEDB”);
th.Properties[“SqlStatementSource”].SetValue(th, “CREATE OR REPLACE VIEW v_Sales as select * from Employee”);

Executable exec2 = exec_SEQ.Executables.Add(“STOCK:SQLTask”);
TaskHost th2 = exec2 as TaskHost;
th2.Properties[“Name”].SetValue(th2, “select from view”);
th2.Properties[“Description”].SetValue(th2, “select from view”);
th2.Properties[“Connection”].SetValue(th2, “ConMgr_OLEDB”);
th2.Properties[“SqlStatementSource”].SetValue(th2, “SELECT * FROM v_Sales”);

Executable exec3 = dyna_pkg.Executables.Add(“STOCK:SQLTask”);
TaskHost th3 = exec3 as TaskHost;
th3.Properties[“Name”].SetValue(th3, “delete View”);
th3.Properties[“Description”].SetValue(th3, “delete View”);
th3.Properties[“Connection”].SetValue(th3, “ConMgr_OLEDB”);
th3.Properties[“SqlStatementSource”].SetValue(th3, “DROP VIEW v_Sales”);

Insert a data flow task:
Executable e = exec_SEQ.Executables.Add(“STOCK:PipelineTask”);
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
thMainPipe.Name = “selectSIFISO data Flow”;

Add a source OLEDB connection manager to the package:

ConnectionManager cm = dyna_pkg.Connections.Add(“OLEDB”);
cm.Name = “OLEDB ConnectionManager”;
cm.ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “your_source_server”, “your_db”);

Add a destination OLEDB connection manager to the package:

// Add an OLEDB connection manager to the package.
ConnectionManager cm_DES = dyna_pkg.Connections.Add(“OLEDB”);
cm_DES.Name = “DES OLEDB”;
cm_DES.ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “your_destination_server”, “your_db”);

Add an OLE DB source to the data flow:

IDTSComponentMetaData100 component =
dataFlowTask.ComponentMetaDataCollection.New();
component.Name = “OLEDBSource”;
component.ComponentClassID = “DTSAdapter.OleDbSource.2”;

Get the design time instance of the component and initialize the component:

CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();

Specify the connection manager:

if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.GetExtendedInterface(dyna_pkg.Connections[0]);
component.RuntimeConnectionCollection[0].ConnectionManagerID =
dyna_pkg.Connections[0].ID;
}

Set the custom properties:

instance.SetComponentProperty(“AccessMode”, 2);
instance.SetComponentProperty(“SqlCommand”,
“SELECT * FROM your_source_table”);

Reinitialize the source metadata:

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

Add the destination component:

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

Get destination design-time instance and initialise component:

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

Set destination connection:

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

Set destination table name:

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

Connect the source to destination component:

dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(component.OutputCollection[0],
destination.InputCollection[0]);

Get input and virtual input for destination to select and map columns:

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

Reinitialize the destination metadata:

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

Get the destination’s default input and virtual input:

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

Iterate through the virtual input column collection:

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);

Join the Execute SQL Tasks:
PrecedenceConstraint pcFileTasks3 =
exec_SEQ.PrecedenceConstraints.Add((Executable)exec, (Executable)e);
pcFileTasks3.Value = DTSExecResult.Success;

PrecedenceConstraint pcFileTasks =
exec_SEQ.PrecedenceConstraints.Add((Executable)e, (Executable)exec2);
pcFileTasks.Value = DTSExecResult.Success;

PrecedenceConstraint pcFileTasks2 =
dyna_pkg.PrecedenceConstraints.Add((Executable)exec_SEQ, (Executable)exec3);
pcFileTasks2.Value = DTSExecResult.Success;

SIFISO_app.SaveToXml(“C:\\TEMP\\pkg_create_DTS_Seq_Sql_Tasks.dtsx”, dyna_pkg, null);

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);

Conclusion

It’s that simple!

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

6 thoughts on “Programmatically Create Data Flow Task inside a Sequence Container Using C#

  1. Beata Linkowski

    I’ve been on your site for a while, and really like it. I guess its about time I let you know my appreciation. Keep up the good work. This answered some questions I had. Is there an RSS feed I can subscribe to?

    Reply
  2. knee brace

    What i discover troublesome is to find a weblog that can capture me for a minute but your blog is different. Bravo.

    Reply
  3. pozycjonowanie stron

    I love the helpful information one deliver in the posts. I’ll take note of your blog post and check ever again these routinely. I am rather several I most certainly will learn about plenty of completely new goods in this case! Best of luck for an additional!

    Reply

Leave a Reply