SSIS Datatypes

Data type Description

DT_BOOL A Boolean value.
DT_BYTES A binary data value.
The length is variable and the maximum length is 8000 bytes.
DT_CY A currency value.
This data type is an eight-byte signed integer with a scale of 4 and a maximum precision of 19.
DT_DATE A date structure that consists of year, month, day, and hour.
DT_DBDATE A date structure that consists of year, month, and day.
DT_DBTIME A time structure that consists of hour, minute, and second.
DT_DBTIMESTAMP A timestamp structure that consists of year, month, hour, minute, second, and fraction.
DT_DECIMAL An exact numeric value with a fixed precision and a fixed scale. This data type is a 12-byte unsigned integer with a separate sign, a scale of 0 to 28, and a maximum precision of 29.
DT_FILETIME A 64-bit value that represents the number of 100-nanosecond intervals since January 1, 1601.
DT_GUID A globally unique identifier (GUID).
DT_I1 A one-byte, signed integer.
DT_I2 A two-byte, signed integer.
DT_I4 A four-byte, signed integer.
DT_I8 An eight-byte, signed integer.
DT_NUMERIC An exact numeric value with a fixed precision and scale. This data type is a 16-byte unsigned integer with a separate sign, a scale of 0 - 38, and a maximum precision of 38.
DT_R4 A single-precision floating-point value.
DT_R8 A double-precision floating-point value.
DT_STR A null-terminated ANSI/MBCS character string with a maximum length of 8000 characters.
DT_UI1 A one-byte, unsigned integer.
DT_UI2 A two-byte, unsigned integer.
DT_UI4 A four-byte, unsigned integer.
DT_UI8 An eight-byte, unsigned integer.
DT_WSTR A null-terminated Unicode character string with a maximum length of 4000 characters.
DT_IMAGE A binary value with a maximum size of 231-1 (2,147,483,647) bytes. DT_NTEXT A Unicode character string with a maximum length of 230 - 1 (1,073,741,823) characters.
DT_TEXT An ANSI/MBCS character string with a maximum length of 231-1 147,483,647) characters.

HOW TO: Programmatically create SSIS packages: Data Flow Task

Hi Friends,

In this article, let us now check how to programmatically create a package containing a Data Flow Task.

Let us start with creating a simple Console Application for it.



First of all, we need to add the following references to your project:

1. Microsoft.SqlServer.DTSPipelineWrap.dll
2. Microsoft.SQLServer.DTSRuntimeWrap.dll
3. Microsoft.SQLServer.ManagedDTS.dll

The reference dlls can be found in the following location:
C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

You must then add the namespace references in the Program.cs file as follows:

#region

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Xml;

#endregion

The following function will explain you How to create data flow task programmatically with

• MS SQL as Source Component
• MS SQL as Destination Component

public bool MSSQLSource_to_MSSQLDestination()
{
try
{
Package package = new Package();

ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB Connection Manager";
cm.ConnectionString = "Data Source=local;Initial Catalog=MyDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

IDTSComponentMetaData90 source = dataFlow.ComponentMetaDataCollection.New();
source.Name = "OLEDBSource";
source.ComponentClassID = "DTSAdapter.OleDbSource.1";

CManagedComponentWrapper sourceinstance = source.Instantiate();
sourceinstance.ProvideComponentProperties();

//Insert Output Column in Source OutputCollection's OutputColumnCollection

sourceinstance.InsertOutputColumnAt(source.OutputCollection[0].ID, 1, "ColumnName", ""); //(type varchar)

source.OutputCollection[0].OutputColumnCollection[1].SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR, 50, 0, 0, 1252);
sourceinstance.InsertExternalMetadataColumnAt(source.OutputCollection[0].ID, 1, "ColumnName ", ""); //(type varchar)

source.OutputCollection[0].ExternalMetadataColumnCollection[0].DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
source.OutputCollection[0].ExternalMetadataColumnCollection[0].Length = 50;
source.OutputCollection[0].ExternalMetadataColumnCollection[0].CodePage = 1252;
source.OutputCollection[0].OutputColumnCollection[0].ExternalMetadataColumnID =
source.OutputCollection[0].ExternalMetadataColumnCollection[0].ID;

source.OutputCollection[0].OutputColumnCollection[0].ExternalMetadataColumnID =
source.OutputCollection[0].ExternalMetadataColumnCollection[0].ID;

IDTSOutputColumn90 column = source.OutputCollection[1].OutputColumnCollection.New();
column.Name = "ColumnName";

if (source.RuntimeConnectionCollection.Count > 0)
{
source.RuntimeConnectionCollection[0].ConnectionManagerID = "OLEDB Connection Manager";
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}

sourceinstance.SetComponentProperty("AccessMode", 2);
sourceinstance.SetComponentProperty("SqlCommand", "Select * from MyDataSrcTable");

sourceinstance.AcquireConnections(null);
sourceinstance.ReinitializeMetaData();
sourceinstance.ReleaseConnections();

IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();
destination.Name = "OLEDBDestination";
destination.ComponentClassID = "DTSAdapter.OleDbDestination.1";

CManagedComponentWrapper destinationinstance = destination.Instantiate();
destinationinstance.ProvideComponentProperties();

if (destination.RuntimeConnectionCollection.Count > 0)
{

destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);

oledbDestination.RuntimeConnectionCollection[0].ConnectionManagerID = "OLEDB Connection Manager";

}

destinationinstance.SetComponentProperty("OpenRowset", "MyDataDestTable");
destinationinstance.SetComponentProperty("AccessMode", 0);

IDTSPath90 path_From_Source_To_Destination = dataFlow.PathCollection.New();
path_From_Source_To_Destination.AttachPathAndPropagateNotifications(source.OutputCollection[0], destination.InputCollection[0]);

destination.InputCollection[0].InputColumnCollection[0].Name = "";
destination.InputCollection[0].InputColumnCollection[1].Name = "";
destination.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = true;

IDTSExternalMetadataColumn90 exCol = destination.InputCollection[0].ExternalMetadataColumnCollection.New();
exCol.Name = "ColumnName";
exCol.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
exCol.Length = 10;
destination.InputCollection[0].InputColumnCollection[1].ExternalMetadataColumnID = exCol.ID;

IDTSOutputColumn90 column1 = op.OutputColumnCollection.New();
column1.Name = "ColumnName";
column1.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR, 50, 0, 0, 1252);

IDTSInput90 input = destination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
IDTSInputColumn90 vCol = destinationinstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
destinationinstance.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[0].ID);
}

destination.InputCollection[0].InputColumnCollection[0].ExternalMetadataColumnID = input.ExternalMetadataColumnCollection[0].ID;

destinationinstance.AcquireConnections(null);
destinationinstance.ReinitializeMetaData();
destination instance.ReleaseConnections();

Application app1 = new Application();
app.SaveToXml(@"E:\MyPackage.dtsx", package, null);

Application app2 = new Application();
Package newPkg = app2.LoadPackage(@"E:\MyPackage.dtsx", null);

DTSExecResult resultPkgExec = newPkg.Execute();

if (resultPkgExec == DTSExecResult.Success)
return true;
else
return false;
}

catch(Exception ex)
{
throw ex;
}

}





Click on the graphic to vote for this

page as a Starting Point Hot Site.