Programmatically Insert Character Map Transformation in Data Flow Task of SSIS Package Using C#

By | July 14, 2011

Abstract

This article explains how to create an SSIS package with both Data Flow Task and Character Map Transformation connected together using C# programming language.

Requirements

Article

If the above requirements are all met, we will begin by launching Microsoft Visual Studio edition.

Create a new project Integration Services Project which is located under Business Intelligence Projects.

After you have named the new project, proceed to click and drag the script task in Control Flow pane of the new package.

Right click the script task and click on “Edit”

Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2008”.

In Project Explorer import relevant references and ensure that you have declared namespaces as below:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;

After declarations, create an instance of application and package:

Application SIFISO_app = new Application();
Package dyna_pkg = new Package();

Insert source and destination connections:

ConnectionManager cm = p.Connections.Add(“OLEDB”);
cm.Name = “OLEDB SOURCE”;
cm.ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “your_Source_SQL_Server”, “your_Source_SQL_Database”);

ConnectionManager cm_DES = p.Connections.Add(“OLEDB”);
cm_DES.Name = “OLEDB DESTINATION”;
cm_DES.ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “your_Destination_SQL_Server”, “your_Destination_SQL_Database”);

Insert a data flow task onto a package:

Executable e = p.Executables.Add(“STOCK:PipelineTask”);
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
thMainPipe.Name = “selectSIFISO data Flow”;

Declare source components of Data Flow Task:

IDTSComponentMetaData100 component =
dataFlowTask.ComponentMetaDataCollection.New();
component.Name = “OLEDBSource”;
component.ComponentClassID = “DTSAdapter.OleDbSource.2”;

Initialise the source component:

CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();

Assign connection manager to data flow task source component:

component.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(cm);

The rest of data flow task source component properties should be as follows:

instance.SetComponentProperty(“AccessMode”, 2);
instance.SetComponentProperty(“SqlCommand”, “SELECT * FROM [dbo].[selectSIFISO_CharacterMap]”);
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

Insert the Character Map Transformation component onto data flow task:

IDTSComponentMetaData100 dataConvertComponent = dataFlowTask.ComponentMetaDataCollection.New();
dataConvertComponent.ComponentClassID = “DTSTransform.CharacterMap”;
dataConvertComponent.Name = “capitalise all initials”;
dataConvertComponent.Description = “capitalise all initials”;

CManagedComponentWrapper dataConvertWrapper = dataConvertComponent.Instantiate();
dataConvertWrapper.ProvideComponentProperties();

Connect the OLE DB source component to the Character Map Transformation component:

dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(component.OutputCollection[0],
dataConvertComponent.InputCollection[0]);

Acquire input column collections:

IDTSInput100 input = dataConvertComponent.InputCollection[0];
IDTSVirtualInput100 dataConvertVirtualInput = dataConvertComponent.InputCollection[0].GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumns =
dataConvertVirtualInput.VirtualInputColumnCollection;
IDTSOutput100 dataConvertOutput = dataConvertComponent.OutputCollection[0];
IDTSOutputColumnCollection100 dataConvertOutputColumns = dataConvertOutput.OutputColumnCollection;

Declare local variables:

int sourceColumnLineageId;
IDTSOutputColumn100 newOutputColumn;
string get_name = “”;

Map source component columns into the Character Map Transformation component:

foreach (IDTSVirtualInputColumn100 virtualInputColumn in destinationVirtualInputColumns)
{
get_name = virtualInputColumn.Name.ToString();
sourceColumnLineageId = dataConvertVirtualInput.VirtualInputColumnCollection[get_name].LineageID;

dataConvertWrapper.SetUsageType(dataConvertComponent.InputCollection[0].ID, dataConvertVirtualInput, sourceColumnLineageId, DTSUsageType.UT_READONLY);

newOutputColumn = dataConvertWrapper.InsertOutputColumnAt(dataConvertOutput.ID, 0, get_name + “_NEW_CHAR_MAP”, string.Empty);
newOutputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR, 50, 0, 0, 1252);
newOutputColumn.MappedColumnID = 0;

dataConvertWrapper.SetOutputColumnProperty(dataConvertOutput.ID, newOutputColumn.ID, “InputColumnLineageID”, sourceColumnLineageId);
}

Insert OLE DB Destination component:

IDTSComponentMetaData100 destination =
dataFlowTask.ComponentMetaDataCollection.New();
destination.ComponentClassID = “DTSAdapter.OleDbDestination”;
destination.Name = “OLEDBDestination”;

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

Assign connection manager to data flow task destination component:

destination.RuntimeConnectionCollection[0].ConnectionManagerID = cm_DES.ID;
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(cm_DES);

The rest of data flow task destination component properties should be as follows:

destDesignTime.SetComponentProperty(“OpenRowset”, “[dbo].[selectSIFISO_CharacterMap]”);
destDesignTime.SetComponentProperty(“AccessMode”, 3);
destDesignTime.SetComponentProperty(“FastLoadOptions”, “TABLOCK,CHECK_CONSTRAINTS”);

Connect the Character Map Transformation component onto destination component:

IDTSPath100 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(dataConvertComponent.OutputCollection[0], destination.InputCollection[0]);

Acquire input column collections of destination component:

IDTSInput100 destinationInputerr = destination.InputCollection[0];
IDTSVirtualInput100 destinationVirtualInputerr = destinationInputerr.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumnserr =
destinationVirtualInputerr.VirtualInputColumnCollection;

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

string inputa = “”;
string remove_conv = “”;

Map Character Map Transformation component columns to destination component columns:

foreach (IDTSVirtualInputColumn100 virtualInputColumn in destinationVirtualInputColumnserr)
{
if (virtualInputColumn.Name.EndsWith(“_NEW_CHAR_MAP”))
{

// Select column, and retain new input column
IDTSInputColumn100 inputColumn = destDesignTime.SetUsageType(destinationInputerr.ID,
destinationVirtualInputerr, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
// Find external column by name
inputa = inputColumn.Name;
remove_conv = inputa.Replace(“_NEW_CHAR_MAP”, “”);
IDTSExternalMetadataColumn100 externalColumn =
destinationInputerr.ExternalMetadataColumnCollection[remove_conv];
// Map input column to external column
destDesignTime.MapInputColumn(destinationInputerr.ID, inputColumn.ID, externalColumn.ID);

}
}

We then save the package into a file system.

Dts.TaskResult = (int)ScriptResults.Success;
SIFISO_app.SaveToXml(“C:\\TEMP\\pkg_Execute_Sql_Tasks_Character_Map.dtsx”, dyna_pkg, null);

Conclusion

It’s that simple!

You can now execute your script task and the package will be created in location you specified.

8 thoughts on “Programmatically Insert Character Map Transformation in Data Flow Task of SSIS Package Using C#

  1. Suganya

    Hi,

    Your site has helped me understand the usage of Script task better. I have created a character map transformation programmatically using your code above. But I’m wondering how to set the MapFlags property for other types of transformations.

    Could you please help

    Reply
  2. Data Recovery

    My brother suggested I may like this web site. He was once totally right. This submit actually made my day. You cann’t consider simply how a lot time I had spent for this information! Thank you!

    Reply
  3. Data Recovery

    Heya i am for the first time here. I came across this board and I to find It really useful & it helped me out much. I hope to give one thing again and help others such as you helped me.

    Reply

Leave a Reply