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