SSIS Package implementation programmatically - Data Access Technologies - Site Home

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

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