Share via

SSIS Package implementation programmatically

  • Article

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

) ON [PRIMARY]

 

Final package looks like below

 

clip_image002[4]

clip_image003[4]

 

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

        {

            InitializeComponent();

            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;";

 

           // aDD eXCEL cONNECTION MANAGER

            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.ProvideComponentProperties();        

            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

            instance.AcquireConnections(null);

            instance.ReinitializeMetaData();

            instance.ReleaseConnections();

 

            //

            // 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();

            dataConvertWrapper.ProvideComponentProperties();

 

            // 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;

 

            dataConvertWrapper.SetUsageType(

                    dataConvertComponent.InputCollection[0].ID,

                    dataConvertVirtualInput,

                    sourceColumnLineageId,

                    DTSUsageType.UT_READONLY);

 

            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;

 

            dataConvertWrapper.SetOutputColumnProperty(

                    dataConvertOutput.ID,

                    newOutputColumn.ID,

                    "SourceInputColumnLineageID",

                    sourceColumnLineageId);

 

            //

            // Add OLEDB Destination

            //

        

            IDTSComponentMetaData100 destComponent = pipeline.ComponentMetaDataCollection.New();

            destComponent.ComponentClassID = "DTSAdapter.OleDbDestination";

            destComponent.ValidateExternalMetadata = true;

 

            IDTSDesigntimeComponent100 destDesignTimeComponent = destComponent.Instantiate();

            destDesignTimeComponent.ProvideComponentProperties();

            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

            destDesignTimeComponent.AcquireConnections(null);

            destDesignTimeComponent.ReinitializeMetaData();

            destDesignTimeComponent.ReleaseConnections();

 

            //

            // 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

https://msdn.microsoft.com/en-us/library/ms135946.aspx

https://msdn.microsoft.com/en-us/library/ms136093.aspx

https://msdn.microsoft.com/en-us/library/ms136086.aspx

 

Happing coding!!!!!

Author : Archana(MSFT) SQL Developer Engineer, Microsoft

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

Comments

  • Anonymous
    November 26, 2013
    The comment has been removed

  • Anonymous
    November 27, 2013
    Hello Arthurz, technet.microsoft.com/.../ms403344.aspx , this article should help you as a starting point.

  • Anonymous
    November 27, 2013
    The comment has been removed

  • Anonymous
    December 17, 2013
    Arthur, 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: blogs.msdn.com/.../ezapi-alternative-package-creation-api.aspx

  • Anonymous
    May 09, 2015
    Good task.

  • Anonymous
    September 07, 2016
    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