SSIS Package implementation programmatically

Published Jan 15 2019 03:25 PM 398 Views
First published on MSDN on Nov 26, 2013

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

%3CLINGO-SUB%20id%3D%22lingo-sub-317956%22%20slang%3D%22en-US%22%3ESSIS%20Package%20implementation%20programmatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-317956%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Nov%2026%2C%202013%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIt%E2%80%99s%20Simple%20to%20develop%20SSIS%20package%20using%20BIDS%2C%20but%20in%20this%20blog%20I%20am%20concentrating%20on%20how%20to%20develop%20SSIS%20package%20programmatically.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EHere%20is%20the%20background%20of%20SSIS%20package%20that%20I%20will%20be%20implementing%20in%20C%23%20code.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3ESSIS%20Packageto%20transfer%20the%20data%20from%20Excel%20sheet%20to%26nbsp%3BSQL%20Server%20database%20with%20Data%20conversion%20on%20one%20column.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EExcel%20contains%202%20columns%20(CustomerName%20%2C%20CustomerEmail)%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3ESQL%20has%20below%20table%20structure%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3ECREATE%20TABLE%20%5Bdbo%5D.%5BOLE%20DB%20Destination%5D(%3C%2FP%3E%0A%20%20%3CP%3E%5BCustomerName%5D%20%5Bnvarchar%5D(255)%20NULL%2C%3C%2FP%3E%0A%20%20%3CP%3E%5BCustomeremail%5D%20%5Bnvarchar%5D(255)%20NULL%3C%2FP%3E%0A%20%20%3CP%3E)%20ON%20%5BPRIMARY%5D%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EFinal%20package%20looks%20like%20below%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CP%3E%3C%2FP%3E%0A%20%20%20%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F67816iC4E20C6559EDFA41%22%20%2F%3E%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3CTD%3E%3CP%3E%3C%2FP%3E%0A%20%20%20%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F67818iE307843ADFE55D4C%22%20%2F%3E%3C%2FP%3E%0A%20%20%20%20%20%3CP%3E%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EProgram%20written%20in%20C%23%20code.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EHere%20is%20the%20code%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Eusing%20System%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20System.Collections.Generic%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20System.ComponentModel%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20System.Data%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20System.Drawing%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20System.Linq%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20System.Text%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20Microsoft.SqlServer.Dts.Runtime%3B%3C%2FP%3E%0A%20%20%3CP%3Eusing%20Microsoft.SqlServer.Dts.Pipeline.Wrapper%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Enamespace%20SSISProgramatically%3C%2FP%3E%0A%20%20%3CP%3E%7B%3C%2FP%3E%0A%20%20%3CP%3Epublic%20partial%20class%20DCExceltoSQL%3C%2FP%3E%0A%20%20%3CP%3E%7B%3C%2FP%3E%0A%20%20%3CP%3Epublic%20DCExceltoSQL()%3C%2FP%3E%0A%20%20%3CP%3E%7B%3C%2FP%3E%0A%20%20%3CP%3EInitializeComponent()%3B%3C%2FP%3E%0A%20%20%3CP%3EPackage%20package%20%3D%20new%20Package()%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Add%20Data%20Flow%20Task%3C%2FP%3E%0A%20%20%3CP%3EExecutable%20dataFlowTask%20%3D%20package.Executables.Add(%22STOCK%3APipelineTask%22)%3B%3C%2FP%3E%0A%20%20%3CP%3EMicrosoft.SqlServer.Dts.Runtime.Application%20App%20%3D%20new%20Microsoft.SqlServer.Dts.Runtime.Application()%3B%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Set%20the%20name%20(otherwise%20it%20will%20be%20a%20random%20GUID%20value)%3C%2FP%3E%0A%20%20%3CP%3ETaskHost%20taskHost%20%3D%20dataFlowTask%20as%20TaskHost%3B%3C%2FP%3E%0A%20%20%3CP%3EtaskHost.Name%20%3D%20%22Data%20Flow%20Task%22%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20We%20need%20a%20reference%20to%20the%20InnerObject%20to%20add%20items%20to%20the%20data%20flow%3C%2FP%3E%0A%20%20%3CP%3EMainPipe%20pipeline%20%3D%20taskHost.InnerObject%20as%20MainPipe%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Add%20SQL%20connection%20manager%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3EConnectionManager%20connection%20%3D%20package.Connections.Add(%22OLEDB%22)%3B%3C%2FP%3E%0A%20%20%3CP%3Econnection.Name%20%3D%20%22localhost%22%3B%3C%2FP%3E%0A%20%20%3CP%3Econnection.ConnectionString%20%3D%20%22Data%20Source%3Dlocalhost%3BInitial%20Catalog%3Dtest%3BProvider%3DSQLNCLI10.1%3BIntegrated%20Security%3DSSPI%3BAuto%20Translate%3DFalse%3B%22%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20aDD%20eXCEL%20cONNECTION%20MANAGER%3C%2FP%3E%0A%20%20%3CP%3EConnectionManager%20excelconnection%20%3D%20package.Connections.Add(%22Excel%22)%3B%3C%2FP%3E%0A%20%20%3CP%3Eexcelconnection.ConnectionString%20%3D%20%40%22Provider%3DMicrosoft.ACE.OLEDB.12.0%3BData%20Source%3DF%3A%5C2013%5CSSIS%5CNewContImp.xlsx%3BExtended%20Properties%3D%22%22EXCEL%2012.0%3BHDR%3DYES%22%22%22%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Add%20Excel%20Source%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3EIDTSComponentMetaData100%20ExcelSource%20%3D%20pipeline.ComponentMetaDataCollection.New()%3B%3C%2FP%3E%0A%20%20%3CP%3EExcelSource.ComponentClassID%20%3D%20%22DTSAdapter.EXCELSource%22%3B%3C%2FP%3E%0A%20%20%3CP%3EExcelSource.ValidateExternalMetadata%20%3D%20true%3B%3C%2FP%3E%0A%20%20%3CP%3EExcelSource.Name%20%3D%20%22EXCEL%20Source%22%3B%3C%2FP%3E%0A%20%20%3CP%3EExcelSource.Description%20%3D%20%22Source%20data%20in%20the%20DataFlow%22%3B%3C%2FP%3E%0A%20%20%3CP%3EIDTSDesigntimeComponent100%20instance%20%3D%20ExcelSource.Instantiate()%3B%3C%2FP%3E%0A%20%20%3CP%3Einstance.ProvideComponentProperties()%3B%3C%2FP%3E%0A%20%20%3CP%3Einstance.SetComponentProperty(%22AccessMode%22%2C%200)%3B%3C%2FP%3E%0A%20%20%3CP%3Einstance.SetComponentProperty(%22OpenRowset%22%2C%20%22Sheet1%24%22)%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EExcelSource.RuntimeConnectionCollection%5B0%5D.ConnectionManager%20%3D%20DtsConvert.GetExtendedInterface(excelconnection)%3B%3C%2FP%3E%0A%20%20%3CP%3EExcelSource.RuntimeConnectionCollection%5B0%5D.ConnectionManagerID%20%3D%20excelconnection.ID%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Acquire%20Connections%20and%20reinitialize%20the%20component%3C%2FP%3E%0A%20%20%3CP%3Einstance.AcquireConnections(null)%3B%3C%2FP%3E%0A%20%20%3CP%3Einstance.ReinitializeMetaData()%3B%3C%2FP%3E%0A%20%20%3CP%3Einstance.ReleaseConnections()%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Add%20transform%20data%20conversion%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIDTSComponentMetaData100%20dataConvertComponent%20%3D%20pipeline.ComponentMetaDataCollection.New()%3B%3C%2FP%3E%0A%20%20%3CP%3EdataConvertComponent.ComponentClassID%20%3D%20%22DTSTransform.DataConvert%22%3B%3C%2FP%3E%0A%20%20%3CP%3EdataConvertComponent.Name%20%3D%20%22Data%20Convert%22%3B%3C%2FP%3E%0A%20%20%3CP%3EdataConvertComponent.Description%20%3D%20%22Data%20Conversion%20Component%22%3B%3C%2FP%3E%0A%20%20%3CP%3ECManagedComponentWrapper%20dataConvertWrapper%20%3D%20dataConvertComponent.Instantiate()%3B%3C%2FP%3E%0A%20%20%3CP%3EdataConvertWrapper.ProvideComponentProperties()%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Connect%20the%20source%20and%20the%20transform%3C%2FP%3E%0A%20%20%3CP%3Epipeline.PathCollection.New().AttachPathAndPropagateNotifications(ExcelSource.OutputCollection%5B0%5D%2C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20dataConvertComponent.InputCollection%5B0%5D)%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Configure%20the%20transform%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIDTSVirtualInput100%20dataConvertVirtualInput%20%3D%20dataConvertComponent.InputCollection%5B0%5D.GetVirtualInput()%3B%3C%2FP%3E%0A%20%20%3CP%3EIDTSOutput100%20dataConvertOutput%20%3D%20dataConvertComponent.OutputCollection%5B0%5D%3B%3C%2FP%3E%0A%20%20%3CP%3EIDTSOutputColumnCollection100%20dataConvertOutputColumns%20%3D%20dataConvertOutput.OutputColumnCollection%3B%3C%2FP%3E%0A%20%20%3CP%3Eint%20sourceColumnLineageId%20%3D%20dataConvertVirtualInput.VirtualInputColumnCollection%5B%22CustomerName%22%5D.LineageID%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EdataConvertWrapper.SetUsageType(%3C%2FP%3E%0A%20%20%3CP%3EdataConvertComponent.InputCollection%5B0%5D.ID%2C%3C%2FP%3E%0A%20%20%3CP%3EdataConvertVirtualInput%2C%3C%2FP%3E%0A%20%20%3CP%3EsourceColumnLineageId%2C%3C%2FP%3E%0A%20%20%3CP%3EDTSUsageType.UT_READONLY)%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIDTSOutputColumn100%20newOutputColumn%20%3D%20dataConvertWrapper.InsertOutputColumnAt(dataConvertOutput.ID%2C%200%2C%20%22CustomerName%22%2C%20string.Empty)%3B%3C%2FP%3E%0A%20%20%3CP%3EnewOutputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR%2C%2050%2C%200%2C%200%2C%200)%3B%3C%2FP%3E%0A%20%20%3CP%3EnewOutputColumn.MappedColumnID%20%3D%200%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EdataConvertWrapper.SetOutputColumnProperty(%3C%2FP%3E%0A%20%20%3CP%3EdataConvertOutput.ID%2C%3C%2FP%3E%0A%20%20%3CP%3EnewOutputColumn.ID%2C%3C%2FP%3E%0A%20%20%3CP%3E%22SourceInputColumnLineageID%22%2C%3C%2FP%3E%0A%20%20%3CP%3EsourceColumnLineageId)%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Add%20OLEDB%20Destination%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIDTSComponentMetaData100%20destComponent%20%3D%20pipeline.ComponentMetaDataCollection.New()%3B%3C%2FP%3E%0A%20%20%3CP%3EdestComponent.ComponentClassID%20%3D%20%22DTSAdapter.OleDbDestination%22%3B%3C%2FP%3E%0A%20%20%3CP%3EdestComponent.ValidateExternalMetadata%20%3D%20true%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIDTSDesigntimeComponent100%20destDesignTimeComponent%20%3D%20destComponent.Instantiate()%3B%3C%2FP%3E%0A%20%20%3CP%3EdestDesignTimeComponent.ProvideComponentProperties()%3B%3C%2FP%3E%0A%20%20%3CP%3EdestComponent.Name%20%3D%20%22OleDb%20Destination%22%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EdestDesignTimeComponent.SetComponentProperty(%22AccessMode%22%2C%203)%3B%3C%2FP%3E%0A%20%20%3CP%3EdestDesignTimeComponent.SetComponentProperty(%22OpenRowset%22%2C%20%22%5BOLE%20DB%20Destination%5D%22)%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20set%20connection%3C%2FP%3E%0A%20%20%3CP%3EdestComponent.RuntimeConnectionCollection%5B0%5D.ConnectionManager%20%3D%20DtsConvert.GetExtendedInterface(connection)%3B%3C%2FP%3E%0A%20%20%3CP%3EdestComponent.RuntimeConnectionCollection%5B0%5D.ConnectionManagerID%20%3D%20connection.ID%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20get%20metadata%3C%2FP%3E%0A%20%20%3CP%3EdestDesignTimeComponent.AcquireConnections(null)%3B%3C%2FP%3E%0A%20%20%3CP%3EdestDesignTimeComponent.ReinitializeMetaData()%3B%3C%2FP%3E%0A%20%20%3CP%3EdestDesignTimeComponent.ReleaseConnections()%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Connect%20source%20(data%20conversion)%20and%20destination%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIDTSPath100%20path%20%3D%20pipeline.PathCollection.New()%3B%3C%2FP%3E%0A%20%20%3CP%3Epath.AttachPathAndPropagateNotifications(dataConvertComponent.OutputCollection%5B0%5D%2C%20destComponent.InputCollection%5B0%5D)%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Configure%20the%20destination%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIDTSInput100%20destInput%20%3D%20destComponent.InputCollection%5B0%5D%3B%3C%2FP%3E%0A%20%20%3CP%3EIDTSVirtualInput100%20destVirInput%20%3D%20destInput.GetVirtualInput()%3B%3C%2FP%3E%0A%20%20%3CP%3EIDTSInputColumnCollection100%20destInputCols%20%3D%20destInput.InputColumnCollection%3B%3C%2FP%3E%0A%20%20%3CP%3EIDTSExternalMetadataColumnCollection100%20destExtCols%20%3D%20destInput.ExternalMetadataColumnCollection%3B%3C%2FP%3E%0A%20%20%3CP%3EIDTSOutputColumnCollection100%20sourceColumns%20%3D%20dataConvertComponent.OutputCollection%5B0%5D.OutputColumnCollection%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIDTSOutputColumnCollection100%20excsourceColumns%20%3D%20ExcelSource.OutputCollection%5B0%5D.OutputColumnCollection%3B%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20The%20OLEDB%20destination%20requires%20you%20to%20hook%20up%20the%20external%20data%20conversion%20columns%3C%2FP%3E%0A%20%20%3CP%3Eforeach%20(IDTSOutputColumn100%20outputCol%20in%20sourceColumns)%3C%2FP%3E%0A%20%20%3CP%3E%7B%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Get%20the%20external%20column%20id%3C%2FP%3E%0A%20%20%3CP%3EIDTSExternalMetadataColumn100%20extCol%20%3D%20(IDTSExternalMetadataColumn100)destExtCols%5BoutputCol.Name%5D%3B%3C%2FP%3E%0A%20%20%3CP%3Eif%20(extCol%20!%3D%20null%20)%3C%2FP%3E%0A%20%20%3CP%3E%7B%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Create%20an%20input%20column%20from%20an%20output%20col%20of%20previous%20component.%3C%2FP%3E%0A%20%20%3CP%3EdestVirInput.SetUsageType(outputCol.ID%2C%20DTSUsageType.UT_READONLY)%3B%3C%2FP%3E%0A%20%20%3CP%3EIDTSInputColumn100%20inputCol%20%3D%20destInputCols.GetInputColumnByLineageID(outputCol.ID)%3B%3C%2FP%3E%0A%20%20%3CP%3Eif%20(inputCol%20!%3D%20null)%3C%2FP%3E%0A%20%20%3CP%3E%7B%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20map%20the%20input%20column%20with%20an%20external%20metadata%20column%3C%2FP%3E%0A%20%20%3CP%3EdestDesignTimeComponent.MapInputColumn(destInput.ID%2C%20inputCol.ID%2C%20extCol.ID)%3B%3C%2FP%3E%0A%20%20%3CP%3E%7D%3C%2FP%3E%0A%20%20%3CP%3E%7D%3C%2FP%3E%0A%20%20%3CP%3E%7D%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20The%20OLEDB%20destination%20requires%20you%20to%20hook%20up%20the%20external%20Excel%20source%20columns%3C%2FP%3E%0A%20%20%3CP%3Eforeach%20(IDTSOutputColumn100%20outputCol%20in%20excsourceColumns)%3C%2FP%3E%0A%20%20%3CP%3E%7B%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Get%20the%20external%20column%20id%3C%2FP%3E%0A%20%20%3CP%3EIDTSExternalMetadataColumn100%20extCol%20%3D%20(IDTSExternalMetadataColumn100)destExtCols%5BoutputCol.Name%5D%3B%3C%2FP%3E%0A%20%20%3CP%3Eif%20(extCol%20!%3D%20null)%3C%2FP%3E%0A%20%20%3CP%3E%7B%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20Create%20an%20input%20column%20from%20an%20output%20col%20of%20previous%20component.%3C%2FP%3E%0A%20%20%3CP%3EdestVirInput.SetUsageType(outputCol.ID%2C%20DTSUsageType.UT_READONLY)%3B%3C%2FP%3E%0A%20%20%3CP%3EIDTSInputColumn100%20inputCol%20%3D%20destInputCols.GetInputColumnByLineageID(outputCol.ID)%3B%3C%2FP%3E%0A%20%20%3CP%3Eif%20(inputCol%20!%3D%20null)%3C%2FP%3E%0A%20%20%3CP%3E%7B%3C%2FP%3E%0A%20%20%3CP%3E%2F%2F%20map%20the%20input%20column%20with%20an%20external%20metadata%20column%3C%2FP%3E%0A%20%20%3CP%3EdestDesignTimeComponent.MapInputColumn(destInput.ID%2C%20inputCol.ID%2C%20extCol.ID)%3B%3C%2FP%3E%0A%20%20%3CP%3E%7D%3C%2FP%3E%0A%20%20%3CP%3E%7D%3C%2FP%3E%0A%20%20%3CP%3E%7D%3C%2FP%3E%0A%20%20%3CP%3EApp.SaveToXml(%40%22D%3A%5CTest3.dtsx%22%2C%20package%2C%20null)%3B%3C%2FP%3E%0A%20%20%3CP%3E%7D%3C%2FP%3E%0A%20%20%3CP%3E%7D%3C%2FP%3E%0A%20%20%3CP%3E%7D%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EThis%20code%20will%20save%20the%20packahe%20test3.dtsx%20in%20D%20drive%2C%20which%20can%20we%20executed%20directly%20or%20in%20SQL%20job.%3C%2FP%3E%0A%20%20%3CP%3EReference%20links%3C%2FP%3E%0A%20%20%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms135946.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms135946.aspx%20%3C%2FA%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms136093.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms136093.aspx%20%3C%2FA%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms136086.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms136086.aspx%20%3C%2FA%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EHapping%20coding!!!!!%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20Author%20%3A%20Archana(MSFT)%20SQL%20Developer%20Engineer%2C%20Microsoft%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20Reviewed%20by%20%3A%20Debarchan(MSFT)%2C%20SQL%20Developer%20Engineer%2C%20Microsoft%20%3C%2FB%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-317956%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Nov%2026%2C%202013%20It%E2%80%99s%20Simple%20to%20develop%20SSIS%20package%20using%20BIDS%2C%20but%20in%20this%20blog%20I%20am%20concentrating%20on%20how%20to%20develop%20SSIS%20package%20programmatically.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-317956%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EIntegration%20Services(SSIS)%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 03:25 PM
Updated by: