Create SSIS Data Flow Task Package Programmatically

Jul 27, 2020

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.

 

RELATED BLOGS:

Basics of SSIS(SQL Server Integration Service)

Hardik Dangar

About the Author

Hardik Dangar

Detailed-oriented, Responsible and Committed Full Stack Developer and Junior Team Lead at MagnusMinds IT Solution having 2+ years of experience. Skilled in C#, .Net Core, Web API, ASP.NET, jQuery, SQL Server, SSIS, SSRS.