HOW TO: Programmatically create SSIS packages: Data Flow Task

Hi Friends,

In this article, let us now check how to programmatically create a package containing a Data Flow Task.

Let us start with creating a simple Console Application for it.



First of all, we need to add the following references to your project:

1. Microsoft.SqlServer.DTSPipelineWrap.dll
2. Microsoft.SQLServer.DTSRuntimeWrap.dll
3. Microsoft.SQLServer.ManagedDTS.dll

The reference dlls can be found in the following location:
C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

You must then add the namespace references in the Program.cs file as follows:

#region

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Xml;

#endregion

The following function will explain you How to create data flow task programmatically with

• MS SQL as Source Component
• MS SQL as Destination Component

public bool MSSQLSource_to_MSSQLDestination()
{
try
{
Package package = new Package();

ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB Connection Manager";
cm.ConnectionString = "Data Source=local;Initial Catalog=MyDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

IDTSComponentMetaData90 source = dataFlow.ComponentMetaDataCollection.New();
source.Name = "OLEDBSource";
source.ComponentClassID = "DTSAdapter.OleDbSource.1";

CManagedComponentWrapper sourceinstance = source.Instantiate();
sourceinstance.ProvideComponentProperties();

//Insert Output Column in Source OutputCollection's OutputColumnCollection

sourceinstance.InsertOutputColumnAt(source.OutputCollection[0].ID, 1, "ColumnName", ""); //(type varchar)

source.OutputCollection[0].OutputColumnCollection[1].SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR, 50, 0, 0, 1252);
sourceinstance.InsertExternalMetadataColumnAt(source.OutputCollection[0].ID, 1, "ColumnName ", ""); //(type varchar)

source.OutputCollection[0].ExternalMetadataColumnCollection[0].DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
source.OutputCollection[0].ExternalMetadataColumnCollection[0].Length = 50;
source.OutputCollection[0].ExternalMetadataColumnCollection[0].CodePage = 1252;
source.OutputCollection[0].OutputColumnCollection[0].ExternalMetadataColumnID =
source.OutputCollection[0].ExternalMetadataColumnCollection[0].ID;

source.OutputCollection[0].OutputColumnCollection[0].ExternalMetadataColumnID =
source.OutputCollection[0].ExternalMetadataColumnCollection[0].ID;

IDTSOutputColumn90 column = source.OutputCollection[1].OutputColumnCollection.New();
column.Name = "ColumnName";

if (source.RuntimeConnectionCollection.Count > 0)
{
source.RuntimeConnectionCollection[0].ConnectionManagerID = "OLEDB Connection Manager";
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}

sourceinstance.SetComponentProperty("AccessMode", 2);
sourceinstance.SetComponentProperty("SqlCommand", "Select * from MyDataSrcTable");

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

IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();
destination.Name = "OLEDBDestination";
destination.ComponentClassID = "DTSAdapter.OleDbDestination.1";

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

if (destination.RuntimeConnectionCollection.Count > 0)
{

destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);

oledbDestination.RuntimeConnectionCollection[0].ConnectionManagerID = "OLEDB Connection Manager";

}

destinationinstance.SetComponentProperty("OpenRowset", "MyDataDestTable");
destinationinstance.SetComponentProperty("AccessMode", 0);

IDTSPath90 path_From_Source_To_Destination = dataFlow.PathCollection.New();
path_From_Source_To_Destination.AttachPathAndPropagateNotifications(source.OutputCollection[0], destination.InputCollection[0]);

destination.InputCollection[0].InputColumnCollection[0].Name = "";
destination.InputCollection[0].InputColumnCollection[1].Name = "";
destination.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = true;

IDTSExternalMetadataColumn90 exCol = destination.InputCollection[0].ExternalMetadataColumnCollection.New();
exCol.Name = "ColumnName";
exCol.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
exCol.Length = 10;
destination.InputCollection[0].InputColumnCollection[1].ExternalMetadataColumnID = exCol.ID;

IDTSOutputColumn90 column1 = op.OutputColumnCollection.New();
column1.Name = "ColumnName";
column1.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR, 50, 0, 0, 1252);

IDTSInput90 input = destination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
IDTSInputColumn90 vCol = destinationinstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
destinationinstance.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[0].ID);
}

destination.InputCollection[0].InputColumnCollection[0].ExternalMetadataColumnID = input.ExternalMetadataColumnCollection[0].ID;

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

Application app1 = new Application();
app.SaveToXml(@"E:\MyPackage.dtsx", package, null);

Application app2 = new Application();
Package newPkg = app2.LoadPackage(@"E:\MyPackage.dtsx", null);

DTSExecResult resultPkgExec = newPkg.Execute();

if (resultPkgExec == DTSExecResult.Success)
return true;
else
return false;
}

catch(Exception ex)
{
throw ex;
}

}





Click on the graphic to vote for this

page as a Starting Point Hot Site.