SSIS Package implementation programmatically

It’s Simple to develop SSIS package using BIDS, but in this blog I am concentrating on how to develop SSIS package programmatically.


Here is the background of SSIS package that I will be implementing in C# code.


SSIS Packageto transfer the data from Excel sheet to SQL Server database with Data conversion on one column.


Excel contains 2 columns (CustomerName , CustomerEmail)


SQL has below table structure


CREATE TABLE [dbo].[OLE DB Destination](

      [CustomerName] [nvarchar](255) NULL,

      [Customeremail] [nvarchar](255) NULL



Final package looks like below








Program written in C# code.


Here is the code


using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;


namespace SSISProgramatically


    public partial class DCExceltoSQL


        public DCExceltoSQL()



            Package package = new Package();


            // Add Data Flow Task

            Executable dataFlowTask = package.Executables.Add("STOCK:PipelineTask");

            Microsoft.SqlServer.Dts.Runtime.Application App = new Microsoft.SqlServer.Dts.Runtime.Application();

            // Set the name (otherwise it will be a random GUID value)

            TaskHost taskHost = dataFlowTask as TaskHost;

            taskHost.Name = "Data Flow Task";


            // We need a reference to the InnerObject to add items to the data flow

            MainPipe pipeline = taskHost.InnerObject as MainPipe;



            // Add SQL connection manager


            ConnectionManager connection = package.Connections.Add("OLEDB");

            connection.Name = "localhost";

            connection.ConnectionString = "Data Source=localhost;Initial Catalog=test;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;";



            ConnectionManager excelconnection = package.Connections.Add("Excel");

            excelconnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\2013\SSIS\NewContImp.xlsx;Extended Properties=""EXCEL 12.0;HDR=YES""";            



            // Add Excel Source


            IDTSComponentMetaData100 ExcelSource = pipeline.ComponentMetaDataCollection.New();

            ExcelSource.ComponentClassID = "DTSAdapter.EXCELSource";

            ExcelSource.ValidateExternalMetadata = true;

            ExcelSource.Name = "EXCEL Source";

            ExcelSource.Description = "Source data in the DataFlow";

            IDTSDesigntimeComponent100 instance = ExcelSource.Instantiate();


            instance.SetComponentProperty("AccessMode", 0);

            instance.SetComponentProperty("OpenRowset", "Sheet1$");


            ExcelSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(excelconnection);

            ExcelSource.RuntimeConnectionCollection[0].ConnectionManagerID = excelconnection.ID;


            // Acquire Connections and reinitialize the component






            // Add transform data conversion



            IDTSComponentMetaData100 dataConvertComponent = pipeline.ComponentMetaDataCollection.New();

            dataConvertComponent.ComponentClassID = "DTSTransform.DataConvert";

            dataConvertComponent.Name = "Data Convert";

            dataConvertComponent.Description = "Data Conversion Component";

            CManagedComponentWrapper dataConvertWrapper = dataConvertComponent.Instantiate();



            // Connect the source and the transform

            pipeline.PathCollection.New().AttachPathAndPropagateNotifications(ExcelSource.OutputCollection[0],                                                                dataConvertComponent.InputCollection[0]);



            // Configure the transform



            IDTSVirtualInput100 dataConvertVirtualInput = dataConvertComponent.InputCollection[0].GetVirtualInput();

            IDTSOutput100 dataConvertOutput = dataConvertComponent.OutputCollection[0];

            IDTSOutputColumnCollection100 dataConvertOutputColumns = dataConvertOutput.OutputColumnCollection;

            int sourceColumnLineageId = dataConvertVirtualInput.VirtualInputColumnCollection["CustomerName"].LineageID;








            IDTSOutputColumn100 newOutputColumn = dataConvertWrapper.InsertOutputColumnAt(dataConvertOutput.ID, 0, "CustomerName", string.Empty);

            newOutputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 50, 0, 0, 0);

            newOutputColumn.MappedColumnID = 0;









            // Add OLEDB Destination



            IDTSComponentMetaData100 destComponent = pipeline.ComponentMetaDataCollection.New();

            destComponent.ComponentClassID = "DTSAdapter.OleDbDestination";

            destComponent.ValidateExternalMetadata = true;


            IDTSDesigntimeComponent100 destDesignTimeComponent = destComponent.Instantiate();


            destComponent.Name = "OleDb Destination";


            destDesignTimeComponent.SetComponentProperty("AccessMode", 3);

            destDesignTimeComponent.SetComponentProperty("OpenRowset", "[OLE DB Destination]");


            // set connection

            destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);

            destComponent.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;


            // get metadata






            // Connect source (data conversion) and destination



            IDTSPath100 path = pipeline.PathCollection.New();

            path.AttachPathAndPropagateNotifications(dataConvertComponent.OutputCollection[0], destComponent.InputCollection[0]);



            // Configure the destination



            IDTSInput100 destInput = destComponent.InputCollection[0];

            IDTSVirtualInput100 destVirInput = destInput.GetVirtualInput();

            IDTSInputColumnCollection100 destInputCols = destInput.InputColumnCollection;

            IDTSExternalMetadataColumnCollection100 destExtCols = destInput.ExternalMetadataColumnCollection;

            IDTSOutputColumnCollection100 sourceColumns = dataConvertComponent.OutputCollection[0].OutputColumnCollection;


            IDTSOutputColumnCollection100 excsourceColumns = ExcelSource.OutputCollection[0].OutputColumnCollection;




            // The OLEDB destination requires you to hook up the external data conversion columns

            foreach (IDTSOutputColumn100 outputCol in sourceColumns)


                // Get the external column id

                IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)destExtCols[outputCol.Name];

                if (extCol != null )


                    // Create an input column from an output col of previous component.

                    destVirInput.SetUsageType(outputCol.ID, DTSUsageType.UT_READONLY);

                    IDTSInputColumn100 inputCol = destInputCols.GetInputColumnByLineageID(outputCol.ID);

                    if (inputCol != null)


                        // map the input column with an external metadata column

                        destDesignTimeComponent.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);




            // The OLEDB destination requires you to hook up the external Excel source columns

            foreach (IDTSOutputColumn100 outputCol in excsourceColumns)


                // Get the external column id

                IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)destExtCols[outputCol.Name];

                if (extCol != null)


                    // Create an input column from an output col of previous component.

                    destVirInput.SetUsageType(outputCol.ID, DTSUsageType.UT_READONLY);

                    IDTSInputColumn100 inputCol = destInputCols.GetInputColumnByLineageID(outputCol.ID);

                    if (inputCol != null)


                        // map the input column with an external metadata column

                        destDesignTimeComponent.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);




            App.SaveToXml(@"D:\Test3.dtsx", package, null);




This code will save the packahe test3.dtsx in D drive, which can we executed directly or in SQL job.

Reference links


Happing coding!!!!!

Author : Archana(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Debarchan(MSFT), SQL Developer Engineer, Microsoft

Comments (6)

  1. ArthurZ says:

    On a related topic: is there anywhere a document outlining exactly what SSIS tasks/components have been exposed to the programmatic approach?

  2. Archana CM says:

    Hello Arthurz,…/ms403344.aspx , this article should help you as a starting point.

  3. ArthurZ says:

    Thank you Archana CM, that high level article is helpful, but not quite.

    A BI developer must know what components he or she will be able to create programmatically before drafting a solution. What I see is happening right now: one begins coding to just later realize a specific component is exposed.

    For example, the very much in demand Script Component ( reference ) is not.

  4. DebarchanS says:


    I understand and agree to your point. While we provide a managed wrapper layer for the runtime/control flow, you have to use the lower level COM wrappers (DTSPipelineWrap) to create your data flows – the usability of which could definitely be improved. Our SSIS Program Manager Matt Masson encourages using the EzAPI framework. It is well implemented and documented, personally I find it really helpful:…/ezapi-alternative-package-creation-api.aspx

  5. Jitendra Sankhla says:

    Hi Archana,

    Can you please tell me what is to be added in the following code if want to create the tables on the fly. There is a scenario, where I don’t have a SQL Table that should be created according to the excel source. The Table name should be same as the Excel filename and the fields in the table also should be the same as the Excel columns.

    Thanks in Advance