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 (5)

  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. Dinesh Vishe says:

    Good task.