Create SSIS Data Flow Task Package Programmatically

By Hardik Dangar

Jul 27, 2020

Create SSIS Data Flow Task Package Programmatically

In this article, we will review how to create a data flow task package of SSIS in Console Application with an example.

Requirements

Microsoft Visual Studio 2017
SQL Server 2014
SSDT

Article

Done with the above requirements? Let's start by launching Microsoft Visual Studio 2017.

Create a new Console Project with .Net Core.

After creating a new project, provide a proper name for it.

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

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

To import above namespaces we need to import below refrences.

We need to keep in mind that, above all references should have same version.

After importing namespaces, ask user for the source connection string, destination connection string and table that will be copied to destination.

string sourceConnectionString, destinationConnectionString, tableName;
Console.Write("Enter Source Database Connection String: ");
sourceConnectionString = Console.ReadLine();
Console.Write("Enter Destination Database Connection String: ");
destinationConnectionString = Console.ReadLine();
Console.Write("Enter Table Name: ");
tableName = Console.ReadLine();

After Declaration, create instance of Application and Package.

Application app = new Application();
Package Mipk = new Package();
Mipk.Name = "DatabaseToDatabase";

Create OLEDB Source Connection Manager to the package.

ConnectionManager connSource;
connSource = Mipk.Connections.Add("ADO.NET:SQL");
connSource.ConnectionString = sourceConnectionString;
connSource.Name = "ADO NET DB Source Connection";

Create OLEDB Destination Connection Manager to the package.

ConnectionManager connDestination;
connDestination= Mipk.Connections.Add("ADO.NET:SQL");
connDestination.ConnectionString = destinationConnectionString;
connDestination.Name = "ADO NET DB Destination Connection";

Insert a data flow task to the package.

Executable e = Mipk.Executables.Add("STOCK:PipelineTask");
TaskHost thMainPipe = (TaskHost)e;
thMainPipe.Name = "DFT Database To Database";
MainPipe df = thMainPipe.InnerObject as MainPipe;

Assign OLEDB Source Component to the Data Flow Task.

IDTSComponentMetaData100 conexionAOrigen = df.ComponentMetaDataCollection.New();
conexionAOrigen.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
conexionAOrigen.Name = "ADO NET Source";

Get Design time instance of the component and initialize it.

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

Specify the Connection Manager.

conexionAOrigen.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connSource);
conexionAOrigen.RuntimeConnectionCollection[0].ConnectionManagerID = connSource.ID;

Set the custom properties.

instance.SetComponentProperty("AccessMode", 0);
instance.SetComponentProperty("TableOrViewName", "\"dbo\".\"" + tableName + "\"");

Reinitialize the source metadata.

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

Now, Add Destination Component to the Data Flow Task.

IDTSComponentMetaData100 conexionADestination = df.ComponentMetaDataCollection.New();
conexionADestination.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.ADONETDestination, Microsoft.SqlServer.ADONETDest, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
conexionADestination.Name = "ADO NET Destination";

Get Design time instance of the component and initialize it.

CManagedComponentWrapper instanceDest = conexionADestination.Instantiate();
instanceDest.ProvideComponentProperties();

Specify the Connection Manager.

conexionADestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connDestination);
conexionADestination.RuntimeConnectionCollection[0].ConnectionManagerID = connDestination.ID;

Set the custom properties.

instanceDest.SetComponentProperty("TableOrViewName", "\"dbo\".\"" + tableName + "\"");

Connect the source to destination component:

IDTSPath100 union = df.PathCollection.New();
union.AttachPathAndPropagateNotifications(conexionAOrigen.OutputCollection[0], conexionADestination.InputCollection[0]);

Reinitialize the destination metadata.

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

Map Source input Columns and Destination Columns

foreach (IDTSOutputColumn100 col in conexionAOrigen.OutputCollection[0].OutputColumnCollection)
{
   for (int i = 0; i < conexionADestination.InputCollection[0].ExternalMetadataColumnCollection.Count; i++)
   {
       string c = conexionADestination.InputCollection[0].ExternalMetadataColumnCollection[i].Name;
       if (c.ToUpper() == col.Name.ToUpper())
       {
           IDTSInputColumn100 column = conexionADestination.InputCollection[0].InputColumnCollection.New();
           column.LineageID = col.ID;
           column.ExternalMetadataColumnID = conexionADestination.InputCollection[0].ExternalMetadataColumnCollection[i].ID;
       }
   }
}

Save Package into the file system.

app.SaveToXml(@"D:\Workspace\SSIS\Test_DB_To_DB.dtsx", Mipk, null);

Execute package.

Mipk.Execute();

Conclusion

In this article, we have explained one of the alternatives for creating SSIS packages using .NET console application. In case you have any questions, please feel free to ask in the comment section below.

TAGS SQL-Jobs Scheduler trending SQL SSIS Business-Intelligence-Development Interview Recruitment Productivity Efficiency .NET-5 Programmatically Ole-Db Data-Flow-Task Package Dynamic C# Code

About the Author

Hardik Dangar

Project Lead in Magnusminds

Hardik is working as Project Lead of MSBI in INDIA. Hardik started his career working on SQL Server and MSBI. Hardik is having 5+ years of experience. In the starting of his career he was working on SQL Server, SSIS and SSRS. Hardik likes to explore technical things on SQL Server.