Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

SSIS Package implementation programmatically

SSIS Package implementation programmatically

Rate This
  • Comments 4

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

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

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

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

Leave a Comment
  • Please add 6 and 4 and type the answer here:
  • Post
  • On a related topic: is there anywhere a document outlining exactly what SSIS tasks/components have been exposed to the programmatic approach?

  • Hello Arthurz,

    technet.microsoft.com/.../ms403344.aspx , this article should help you as a starting point.

  • 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 http://goo.gl/rN3ZFh ) is not.

  • 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

Page 1 of 1 (4 items)