Programming SSIS - Exception 0xC0204006

Hello. I am attempting to use SSIS to import a table from MS Access in to SQL Server. However, when i set the destination component properties I get the following error:

Exception from HRESULT: 0xC0204006

The table exists within the destination database and if I comment the line out, it is able to acquire the connection.Anyone have any ideas?

Thanks

ConnectionManager connMgr;
ConnectionManager connMgr1;

//Create the package
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();

//Create to connections to the package
Connections packageConns = package.Connections;

connMgr1 = package.Connections.Add("OLEDB");
connMgr1.ConnectionString = accessModelConnectString;
connMgr1.Name = "OLEDB ConnectionManager";

connMgr = package.Connections.Add("OLEDB");
connMgr.ConnectionString = sqlConnectionString;
connMgr.Name = "OLEDB ConnectionManager1";

//Add a dataflow task to the package.
MainPipe dataFlowTask = ((Microsoft.SqlServer.Dts.Runtime.TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;

IDTSComponentMetaData90 sourceComponent = dataFlowTask.ComponentMetaDataCollection.New();
sourceComponent.ComponentClassID = "DTSAdapter.OleDbSource.1";
sourceComponent.Name = "yyy";

// Get the design time instance of the component.
CManagedComponentWrapper instance = sourceComponent.Instantiate();

// Initialize the component
instance.ProvideComponentProperties();

// Specify the connection manager.
if (sourceComponent.RuntimeConnectionCollection.Count > 0)
{
sourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
sourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;
}

// Set the custom properties.
instance.SetComponentProperty("AccessMode", 2);
instance.SetComponentProperty("SqlCommand", "SELECT Test FROM Test");

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

IDTSComponentMetaData90 destinationComponent = dataFlowTask.ComponentMetaDataCollection.New();
destinationComponent.ComponentClassID = "DTSAdapter.OleDBDestination.1";
destinationComponent.Name = "xxx";

// Get the design time instance of the component.
CManagedComponentWrapper instance1 = destinationComponent.Instantiate();

// Initialize the component
instance1.ProvideComponentProperties();

// Specify the connection manager.
if (destinationComponent.RuntimeConnectionCollection.Count > 0)
{
destinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
destinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[1].ID;
}

instance1.SetComponentProperty("AccessMode", 3);
instance1.SetComponentProperty("OpenRowSet", "[PedestrianFlow].[dbo].[OLE DB Destination]");

// Reinitialize the metadata.
instance1.AcquireConnections(null);
instance1.ReinitializeMetaData();
instance1.ReleaseConnections();

// Create the path.
IDTSPath90 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0],destinationComponent.InputCollection[0]);

IDTSInput90 input = destinationComponent.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
string y = vColumn.Name;
// Call the SetUsageType method of the design time instance of the component.
instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}

Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute();

foreach (DtsError pkgerror in package.Errors)
{
string err = pkgerror.Description;
Console.WriteLine(err);
}

Can anybody help? I am getting nowhere fast.

Thanks.|||

This explains it quite well I think, but feel free to update-

0xC0204006
(http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0204006.html)

Check the property you are using, it does not exist.

|||... case sensitive name is what I meant to add.|||

Thankyou, you are a life saver. I can't believe I missed that, I have been staring at that code for hours.

Thanks again.|||

cjturner wrote:

Thankyou, you are a life saver. I can't believe I missed that, I have been staring at that code for hours.

Thanks again.

Please be sure to mark the appropriate post as the answer to your question.

Programmatically Data Conversion component creation

Hi there,

I have created a package which simply imports data from a flat file to a SQL Server table. But I need to incorporate a data conversion component by which I may change the source-destination column mapping programmatically. So what I thought that I need to add a data conversion component into the dataflow task. After adding this component (I found its component id as {C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}) I have created a path which establishes the mapping between output columns of source component and the input columns of data conversion component. Now I am not sure how to establish the mapping between the data conversion component’s input column collection and output column collection.

I am giving my code snippet here,

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

… … …. // Code for configuring the source data flow component

IDTSComponentMetaData90 conversionDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();// creating data conversion

conversionDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";// This is the GUID for data conversion component

CManagedComponentWrapper conversionInstance = conversionDataFlowComponent.Instantiate();//Instantiate

conversionInstance.ProvideComponentProperties();

// Now creating a path to connet the source and conversion

IDTSPath90 fPath = dataFlowTask.PathCollection.New();fPath.AttachPathAndPropagateNotifications(

sourceDataFlowComponent.OutputCollection[0],

conversionDataFlowComponent.InputCollection[0]);

// Sould I need to accuire connect for data conversion? Im not sure

conversionInstance.AcquireConnections(null);

conversionInstance.ReinitializeMetaData();

// Get the input collection

IDTSInput90 input = conversionDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection){

conversionInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

.. . // Well here I am stucked. What I need to do here to establish a map

// between conversionDataFlowComponent.InputCollection[0] and

// conversionDataFlowComponent.OutputCollection[0]?

As you can see I am just away from creating the mapping between input and output collection. Can anybody give me an idea how can I achieve this?

I will appreciate all kind of suggestions and comments.

Regards

Moim

Have a look at this code snippet where I use this transform, selecting all columns, and converting types where required based on the result of a helper function-

#region Select Conversion Columns

IDTSOutput90 output = dataConversion.OutputCollection[0];

IDTSVirtualInput90 virtualInput = dataConversion.InputCollection[0].GetVirtualInput();

int inputId = dataConversion.InputCollection[0].ID;

foreach (IDTSVirtualInputColumn90 column in virtualInput.VirtualInputColumnCollection)

{

int length = column.Length;

int precision = column.Precision;

int scale = column.Scale;

DataType dataType = ComponentHelper.ConvertBufferDataTypeToFitExcel(column.DataType, ref length, ref precision, ref scale);

if (dataType != column.DataType)

{

IDTSInputColumn90 inputColumn = dataConversionInstance.SetUsageType(inputId, virtualInput, column.LineageID, DTSUsageType.UT_READONLY);

IDTSOutputColumn90 outputColumn = dataConversionInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, column.Name, "Excel compatible conversion column");

dataConversionInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn.ID, dataType, length, precision, scale, 0);

outputColumn.CustomPropertyCollection[0].Value = inputColumn.LineageID;

inputColumn.Name = DataConversionInputRenamePrefix + inputColumn.Name;

}

}

#endregion

Btw, how did you get your code to paste so nicely into this editor Window?

|||

DarrenSQLIS wrote:

Btw, how did you get your code to paste so nicely into this editor Window?

Try this:

http://www.codeproject.com/jscript/CopyasHTML.asp

Or just go here: http://www.google.co.uk/search?hl=en&q=copyashtml&meta=

I have add-in that enables me to do it but I can't for the life of me find it again.

-Jamie

|||

Jamie Thomson wrote:

DarrenSQLIS wrote:

Btw, how did you get your code to paste so nicely into this editor Window?

I have add-in that enables me to do it but I can't for the life of me find it again.

-Jamie

Here it is: http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/|||Nice, thanks Gents.|||

Hello Darran,

Thanks a lot. After implementing your suggestion I believe I am very near to make it work. I think I am missing/doing some silly things/mistakes so that the package is not working as expected. I observed the log traces and found that it is failing to validate the destination data flow component. And debugging it I found that after creating a path between the data conversion component and the destination component, when I populate the input columns for the destination component it is actually getting the source components output columns lineageID instead of data conversion component’s output columns lineageID. Can you help me to find out the problem please?

Anyway, thank you very much for your previous help.

Here is the code snippet:

private void ConfigureSourceComponent()

{

sourceDataFlowComponent.Name = "Source Data from Flat file";

// Here is the component class id for flat file source data

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

CManagedComponentWrapper managedFlatFileInstance =

sourceDataFlowComponent.Instantiate();

managedFlatFileInstance.ProvideComponentProperties();sourceDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManagerID =flatfileConnectionManager.ID;sourceDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManager =DtsConvert.ToConnectionManager90(flatfileConnectionManager);

managedFlatFileInstance.AcquireConnections(null);

managedFlatFileInstance.ReinitializeMetaData();

IDTSExternalMetadataColumn90 exOutColumn;

foreach (IDTSOutputColumn90 outColumn in sourceDataFlowComponent.OutputCollection[0].OutputColumnCollection)

{

exOutColumn = sourceDataFlowComponent.OutputCollection[0].

ExternalMetadataColumnCollection[outColumn.Name];managedFlatFileInstance.MapOutputColumn(

sourceDataFlowComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);

}

managedFlatFileInstance.ReleaseConnections()

}

private void ConfigureConversionComponent()

{

conversionDataFlowComponent.Name = "Conversion compoenent";

// component class id for Data conversion component

conversionDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";

CManagedComponentWrapper conversionInstance =

conversionDataFlowComponent.Instantiate();

conversionInstance.ProvideComponentProperties();

// Create the path.

IDTSPath90 fPath = dataFlowTask.PathCollection.New();fPath.AttachPathAndPropagateNotifications(

sourceDataFlowComponent.OutputCollection[0],

conversionDataFlowComponent.InputCollection[0]);

conversionInstance.AcquireConnections(null);

conversionInstance.ReinitializeMetaData();

// Get the output collection

IDTSOutput90 output = conversionDataFlowComponent.OutputCollection[0];

// Get the destination's default input and virtual input.

input = conversionDataFlowComponent.InputCollection[0];

vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

IDTSInputColumn90 inputColumn =

conversionInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

IDTSOutputColumn90 outputColumn =

conversionInstance.InsertOutputColumnAt

(output.ID, output.OutputColumnCollection.Count,

vColumn.Name, "Sql server compatible conversion column");

conversionInstance.SetOutputColumnDataTypeProperties(

output.ID, outputColumn.ID, vColumn.DataType,

vColumn.Length, vColumn.Precision, vColumn.Scale, 0);

outputColumn.CustomPropertyCollection[0].Value =

inputColumn.LineageID;

}

conversionInstance.ReleaseConnections();

}

private void ConfigureDestinationComponent()

{

destinationDataFlowComponent.Name = "Destination Oledb compoenent";

// component class id for Oledb

destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";

CManagedComponentWrapper managedOleInstance =

destinationDataFlowComponent.Instantiate();

managedOleInstance.ProvideComponentProperties();

destinationDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManagerID = oledbConnectionManager.ID;

destinationDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManager =DtsConvert.ToConnectionManager90(oledbConnectionManager);

managedOleInstance.SetComponentProperty("AccessMode", 0);

// .. .. .. setting other component properties here..

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New();

path.AttachPathAndPropagateNotifications(

conversionDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]

);

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData();

// Get the destination's default input and virtual input.

IDTSInput90 input;

IDTSVirtualInput90 vInput;

// Get the destination's default input and virtual input.

input = destinationDataFlowComponent.InputCollection[0];

vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in

vInput.VirtualInputColumnCollection)

{

managedOleInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

// culprit Line. I can found vColumn.LineageID = source lineage ids

// while debugging.. can you please help me out here?

}

IDTSExternalMetadataColumn90 exColumn;

foreach (IDTSInputColumn90 inColumn in

destinationDataFlowComponent.InputCollection[0].InputColumnCollection)

{

exColumn = destinationDataFlowComponent.InputCollection[0].

ExternalMetadataColumnCollection[inColumn.Name];managedOleInstance.MapInputColumn(destinationDataFlowComponent.

InputCollection[0].ID, inColumn.ID, exColumn.ID);

}

managedOleInstance.ReleaseConnections();

}

Here you can see the red marked lines. Where I can found that the destination component is watching the source output columns lineage althoug its mapped with data conversion component.

Regards

Moim

|||

The input buffer of the destination will include all upstream columns, so that means both the ones that came from the source and those from the Data Conversion Tx. You will need to be selective in choosing the input columns.

Think of the buffer as it passes down the pipeline, or rather what columns are available -

Source Output - SourceCol1

Data Conversion Output - SourceCol1, SourceCol1(converted)

You don't seem to be distiguishing between the input columns and output columns in the data conversion setup, that seems strange. You would only want to select the converted columns I assume. You could use a name, description or the UpstreamComponentName property of the column perhaps.

|||

Hi,

I have similar requirement.I need to import data from csv files to sql server tables using ssis programatically.iam not able to create a data conversion task programatically and map the columns from the dataconversion task to the oledb destination.tried my level best and got struck.Is there a code demo availble?appreciating your help

|||

adarsha wrote:

Hi,

I have similar requirement.I need to import data from csv files to sql server tables using ssis programatically.iam not able to create a data conversion task programatically and map the columns from the dataconversion task to the oledb destination.tried my level best and got struck.Is there a code demo availble?appreciating your help

It would really help if you explain what you are getting stuck on and how far you have come so far. Posting your code would be even better.

-Jamie

|||

Hi,

//In the below example i have pumped data from sql table to csvs which works fine for me .But now i need to do the exact opposite task.(Pump data from csv files to sql table)

Problems i have :

1:

After adding a flatfile source iam not very sure how to exactly add input columns in FlatFile source as they dont get populated by themselves.I tried using datatable reading the columns from the file and add it and somewhat succeeded.But need a clear implementation help.

2:

To some extent when i succeeded in adding input columns to flat file source, the package failed due to data conversion problems(unicode to non unicode conversion error).I tried programatically building dataconversion task and had no clue how exactly to do it correctly and make it to work.

So i need help in adding input columns to flat file source and adding data conversion task and selecting only the converted columns to the oledb destination.

using System;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Reflection;

using System.Threading;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace CCE_ETL_Engine_Flatfiles

{

class Flatfile_Pump

{

Boolean mblnDebug = true;

string mstrAppLogFile;

string mstrDTSXFile;

string DataFileName;

string DestinationDataDirectory;

#region intialize

public void InitializeVariables(string strDTSPackageName)

{

DestinationDataDirectory = System.Configuration.ConfigurationManager.AppSettings.Get("DataFilePath");

DataFileName = System.Configuration.ConfigurationManager.AppSettings.Get("DataFileName");

Object objLock = new Object();

Monitor.Enter(objLock);

string strTimeStamp = DateTime.Now.Hour.ToString().PadLeft(2, '0') + DateTime.Now.Minute.ToString().PadLeft(2, '0') + DateTime.Now.Second.ToString().PadLeft(2, '0') + DateTime.Now.Millisecond.ToString();

Monitor.Exit(objLock);

mstrAppLogFile = System.Configuration.ConfigurationManager.AppSettings.Get("ApplicationLogFilePath") + @."\" + strDTSPackageName + "_" + strTimeStamp + ".log";

mstrDTSXFile = System.Configuration.ConfigurationManager.AppSettings.Get("DTSXFilePath") + @."\" + strDTSPackageName + "_" + strTimeStamp + ".dtsx";

if (System.Configuration.ConfigurationManager.AppSettings.Get("Debug").ToUpper() == "FALSE")

mblnDebug = false;

else

mblnDebug = true;

}

#endregion

#region constructor

public Flatfile_Pump(string strDTSPackageName)

{

InitializeVariables(strDTSPackageName);

}

#endregion

#region CreatePackage

public Package CreatePackage(string Name, string Description)

{

Package DataPump = new Package();

DataPump.PackageType = DTSPackageType.DTSDesigner90;

DataPump.Name = Name;

DataPump.Description = Description;

DataPump.CreatorComputerName = System.Environment.MachineName;

DataPump.CreatorName = System.Environment.UserName;

return DataPump;

}

#endregion

#region AddConnectionManagers

/// <summary>

/// Adds the OLEDB and FlatFile connection managers to the package.

/// </summary>

public void AddConnectionManagers(Package DataPump, string SqlConnection) Here iam adding 2 connection managers 1 for flatfile and 1 for OLEDB

{

// Add the OLEDB connection manager.

ConnectionManager ConnectionName;

ConnectionManager cmflatFile;

ConnectionName = DataPump.Connections.Add("OLEDB");

// Set stock properties.

ConnectionName.Name = "Remote Source";

ConnectionName.ConnectionString = SqlConnection;

//@."Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cce_control;Data Source=(wxp-9plyf1s);Auto Translate=False;";

// Add the Destination connection manager.

cmflatFile = DataPump.Connections.Add("FLATFILE");

// Set the stock properties.

cmflatFile.Properties["ConnectionString"].SetValue(cmflatFile, DestinationDataDirectory + DataFileName);

cmflatFile.Properties["Format"].SetValue(cmflatFile, "Delimited");

cmflatFile.Properties["DataRowsToSkip"].SetValue(cmflatFile, 0);

cmflatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmflatFile, true);

cmflatFile.Properties["Name"].SetValue(cmflatFile, "FlatFileConnection");

cmflatFile.Properties["RowDelimiter"].SetValue(cmflatFile, "\r\n");

cmflatFile.Properties["TextQualifier"].SetValue(cmflatFile, "\"");

}

#endregion

#region AddDataFlowTask

/// <summary>

/// Adds a DataFlow task to the Executables collection of the package.

/// Retrieves the MainPipe object from the TaskHost and stores it in

/// the dataFlow member variable

/// </summary>

public MainPipe AddDataFlowTask(Package DataPump)

{

TaskHost th = DataPump.Executables.Add("DTS.Pipeline") as TaskHost;

th.Name = "DataFlow";

th.Description = "The DataFlow task in the package.";

MainPipe dataFlow = th.InnerObject as MainPipe;

ComponentEvents componentEvents = new ComponentEvents();

dataFlow.Events = componentEvents as wrap.IDTSComponentEvents90;

return dataFlow;

}

#endregion

#region AddOLEDBSource

public IDTSComponentMetaData90 AddOLEDBSource(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe dataFlow, Package DataPump, string strSourceQuery)

{

// Declare and Add the component to the dataFlow metadata collection

IDTSComponentMetaData90 OLEDBsource;

OLEDBsource = dataFlow.ComponentMetaDataCollection.New();

// Set the common properties

OLEDBsource.ComponentClassID = "DTSAdapter.OLEDBSource";

OLEDBsource.Name = "cce_control";

OLEDBsource.Description = "Remote Source Server";

// Create an instance of the component

CManagedComponentWrapper instance = OLEDBsource.Instantiate();

instance.ProvideComponentProperties();

// Associate the runtime ConnectionManager with the component

OLEDBsource.RuntimeConnectionCollection[0].ConnectionManagerID = DataPump.Connections["Remote Source"].ID;

OLEDBsource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DataPump.Connections["Remote Source"]);

instance.SetComponentProperty("SqlCommand", strSourceQuery);

instance.SetComponentProperty("AccessMode", 2);

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

instance.ReleaseConnections();

return OLEDBsource;

}

#endregion

#region AddFlatFileDestination

public IDTSComponentMetaData90 AddFlatFileDestination(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe dataFlow, Package DataPump, IDTSComponentMetaData90 OLEDBsource)

{

// Declare and Add the component to the dataFlow metadata collection

IDTSComponentMetaData90 flatfileDestination;

flatfileDestination = dataFlow.ComponentMetaDataCollection.New();

// Set the common properties

flatfileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination";

flatfileDestination.Name = "FlatFileDestination";

flatfileDestination.Description = "Flat file destination";

// Create an instance of the component

CManagedComponentWrapper instance = flatfileDestination.Instantiate();

instance.ProvideComponentProperties();

// Associate the runtime ConnectionManager with the component

flatfileDestination.RuntimeConnectionCollection[0].ConnectionManagerID

= DataPump.Connections["FlatFileConnection"].ID;

flatfileDestination.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(DataPump.Connections["FlatFileConnection"]);

// Map a path between the Sort transformation component to the FlatFileDestination

dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(OLEDBsource.OutputCollection[0], flatfileDestination.InputCollection[0]);

// Add columns to the FlatFileConnectionManager

AddColumnsToFlatFileConnection(DataPump, flatfileDestination);

// Acquire the connection, reinitialize the metadata,

// map the columns, then release the connection.

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

MapFlatFileDestinationColumns(flatfileDestination);

instance.ReleaseConnections();

return flatfileDestination;

}

#endregion

#region AddColumnsToFlatFileConnection

public void AddColumnsToFlatFileConnection(Package DataPump, IDTSComponentMetaData90 flatfileDestination)

{

wrap.IDTSConnectionManagerFlatFile90 ff = null;

foreach (ConnectionManager cm in DataPump.Connections)

{

if (cm.Name == "FlatFileConnection")

{

ff = cm.InnerObject as wrap.IDTSConnectionManagerFlatFile90;

DtsConvert.ToConnectionManager90(cm);

}

}

int count = ff.Columns.Count;

// if the connection manager is null here, then we have a problem

if (ff != null)

{

// Get the upstream columns

IDTSVirtualInputColumnCollection90 vColumns = flatfileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;

for (int cols = 0; cols < vColumns.Count; cols++)

{

wrap.IDTSConnectionManagerFlatFileColumn90 col = ff.Columns.Add();

// If this is the last column, set the delimiter to CRLF.

// Otherwise keep the delimiter as ",".

if (cols == vColumns.Count - 1)

{

col.ColumnDelimiter = "\r\n";

}

else

{

col.ColumnDelimiter = @.",";

}

col.ColumnType = "Delimited";

col.DataType = vColumns[cols].DataType;

col.DataPrecision = vColumns[cols].Precision;

col.DataScale = vColumns[cols].Scale;

wrap.IDTSName90 name = col as wrap.IDTSName90;

name.Name = vColumns[cols].Name;

}

}

}

#endregion

#region MapFlatFileDestination Columns

public void MapFlatFileDestinationColumns(IDTSComponentMetaData90 flatfileDestination)

{

CManagedComponentWrapper wrp = flatfileDestination.Instantiate();

IDTSVirtualInput90 vInput = flatfileDestination.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

wrp.SetUsageType(flatfileDestination.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

// For each column in the input collection

// find the corresponding external metadata column.

foreach (IDTSInputColumn90 col in flatfileDestination.InputCollection[0].InputColumnCollection)

{

IDTSExternalMetadataColumn90 exCol = flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];

wrp.MapInputColumn(flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);

}

}

#endregion

#region Exec_DataPump--This is just the execution where i get values from like server name etc and create the package and execute it.

public Boolean DataPump(string SqlConnection, string strSourceQuery)

{

Package DataPump;

PackageEvents packageEvents;

MainPipe dataFlow;

IDTSComponentMetaData90 OLEDBsource, flatfileDestination;

packageEvents = new PackageEvents();

DataPump = CreatePackage("SSISPackage", "The package for the Flatfile pump");

AddConnectionManagers(DataPump, SqlConnection);

dataFlow = AddDataFlowTask(DataPump);

OLEDBsource = AddOLEDBSource(dataFlow, DataPump, strSourceQuery);

flatfileDestination = AddFlatFileDestination(dataFlow, DataPump, OLEDBsource);

DTSExecResult status = DataPump.Validate(null, null, packageEvents, null);

if (mblnDebug)

{

Application dts = new Application();

dts.SaveToXml(mstrDTSXFile, DataPump, packageEvents);

}

foreach (Variable v in DataPump.Variables)

{

if (v.Name.ToUpper() == "EXECUTIONINSTANCEGUID")

{

break;

}

}

if (status == DTSExecResult.Success)

{

DTSExecResult result = DataPump.Execute(null, null, packageEvents, null, null);

foreach (Variable v in DataPump.Variables)

{

Console.WriteLine(v.Name.ToUpper());

}

}

return true;

}

#endregion

}

}

Programmatically Data Conversion component creation

Hi there,

I have created a package which simply imports data from a flat file to a SQL Server table. But I need to incorporate a data conversion component by which I may change the source-destination column mapping programmatically. So what I thought that I need to add a data conversion component into the dataflow task. After adding this component (I found its component id as {C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}) I have created a path which establishes the mapping between output columns of source component and the input columns of data conversion component. Now I am not sure how to establish the mapping between the data conversion component’s input column collection and output column collection.

I am giving my code snippet here,

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

… … …. // Code for configuring the source data flow component

IDTSComponentMetaData90 conversionDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();// creating data conversion

conversionDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";// This is the GUID for data conversion component

CManagedComponentWrapper conversionInstance = conversionDataFlowComponent.Instantiate();//Instantiate

conversionInstance.ProvideComponentProperties();

// Now creating a path to connet the source and conversion

IDTSPath90 fPath = dataFlowTask.PathCollection.New();fPath.AttachPathAndPropagateNotifications(

sourceDataFlowComponent.OutputCollection[0],

conversionDataFlowComponent.InputCollection[0]);

// Sould I need to accuire connect for data conversion? Im not sure

conversionInstance.AcquireConnections(null);

conversionInstance.ReinitializeMetaData();

// Get the input collection

IDTSInput90 input = conversionDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection){

conversionInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

.. . // Well here I am stucked. What I need to do here to establish a map

// between conversionDataFlowComponent.InputCollection[0] and

// conversionDataFlowComponent.OutputCollection[0]?

As you can see I am just away from creating the mapping between input and output collection. Can anybody give me an idea how can I achieve this?

I will appreciate all kind of suggestions and comments.

Regards

Moim

Have a look at this code snippet where I use this transform, selecting all columns, and converting types where required based on the result of a helper function-

#region Select Conversion Columns

IDTSOutput90 output = dataConversion.OutputCollection[0];

IDTSVirtualInput90 virtualInput = dataConversion.InputCollection[0].GetVirtualInput();

int inputId = dataConversion.InputCollection[0].ID;

foreach (IDTSVirtualInputColumn90 column in virtualInput.VirtualInputColumnCollection)

{

int length = column.Length;

int precision = column.Precision;

int scale = column.Scale;

DataType dataType = ComponentHelper.ConvertBufferDataTypeToFitExcel(column.DataType, ref length, ref precision, ref scale);

if (dataType != column.DataType)

{

IDTSInputColumn90 inputColumn = dataConversionInstance.SetUsageType(inputId, virtualInput, column.LineageID, DTSUsageType.UT_READONLY);

IDTSOutputColumn90 outputColumn = dataConversionInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, column.Name, "Excel compatible conversion column");

dataConversionInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn.ID, dataType, length, precision, scale, 0);

outputColumn.CustomPropertyCollection[0].Value = inputColumn.LineageID;

inputColumn.Name = DataConversionInputRenamePrefix + inputColumn.Name;

}

}

#endregion

Btw, how did you get your code to paste so nicely into this editor Window?

|||

DarrenSQLIS wrote:

Btw, how did you get your code to paste so nicely into this editor Window?

Try this:

http://www.codeproject.com/jscript/CopyasHTML.asp

Or just go here: http://www.google.co.uk/search?hl=en&q=copyashtml&meta=

I have add-in that enables me to do it but I can't for the life of me find it again.

-Jamie

|||

Jamie Thomson wrote:

DarrenSQLIS wrote:

Btw, how did you get your code to paste so nicely into this editor Window?

I have add-in that enables me to do it but I can't for the life of me find it again.

-Jamie

Here it is: http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/|||Nice, thanks Gents.|||

Hello Darran,

Thanks a lot. After implementing your suggestion I believe I am very near to make it work. I think I am missing/doing some silly things/mistakes so that the package is not working as expected. I observed the log traces and found that it is failing to validate the destination data flow component. And debugging it I found that after creating a path between the data conversion component and the destination component, when I populate the input columns for the destination component it is actually getting the source components output columns lineageID instead of data conversion component’s output columns lineageID. Can you help me to find out the problem please?

Anyway, thank you very much for your previous help.

Here is the code snippet:

private void ConfigureSourceComponent()

{

sourceDataFlowComponent.Name = "Source Data from Flat file";

// Here is the component class id for flat file source data

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

CManagedComponentWrapper managedFlatFileInstance =

sourceDataFlowComponent.Instantiate();

managedFlatFileInstance.ProvideComponentProperties();sourceDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManagerID =flatfileConnectionManager.ID;sourceDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManager =DtsConvert.ToConnectionManager90(flatfileConnectionManager);

managedFlatFileInstance.AcquireConnections(null);

managedFlatFileInstance.ReinitializeMetaData();

IDTSExternalMetadataColumn90 exOutColumn;

foreach (IDTSOutputColumn90 outColumn in sourceDataFlowComponent.OutputCollection[0].OutputColumnCollection)

{

exOutColumn = sourceDataFlowComponent.OutputCollection[0].

ExternalMetadataColumnCollection[outColumn.Name];managedFlatFileInstance.MapOutputColumn(

sourceDataFlowComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);

}

managedFlatFileInstance.ReleaseConnections()

}

private void ConfigureConversionComponent()

{

conversionDataFlowComponent.Name = "Conversion compoenent";

// component class id for Data conversion component

conversionDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";

CManagedComponentWrapper conversionInstance =

conversionDataFlowComponent.Instantiate();

conversionInstance.ProvideComponentProperties();

// Create the path.

IDTSPath90 fPath = dataFlowTask.PathCollection.New();fPath.AttachPathAndPropagateNotifications(

sourceDataFlowComponent.OutputCollection[0],

conversionDataFlowComponent.InputCollection[0]);

conversionInstance.AcquireConnections(null);

conversionInstance.ReinitializeMetaData();

// Get the output collection

IDTSOutput90 output = conversionDataFlowComponent.OutputCollection[0];

// Get the destination's default input and virtual input.

input = conversionDataFlowComponent.InputCollection[0];

vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

IDTSInputColumn90 inputColumn =

conversionInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

IDTSOutputColumn90 outputColumn =

conversionInstance.InsertOutputColumnAt

(output.ID, output.OutputColumnCollection.Count,

vColumn.Name, "Sql server compatible conversion column");

conversionInstance.SetOutputColumnDataTypeProperties(

output.ID, outputColumn.ID, vColumn.DataType,

vColumn.Length, vColumn.Precision, vColumn.Scale, 0);

outputColumn.CustomPropertyCollection[0].Value =

inputColumn.LineageID;

}

conversionInstance.ReleaseConnections();

}

private void ConfigureDestinationComponent()

{

destinationDataFlowComponent.Name = "Destination Oledb compoenent";

// component class id for Oledb

destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";

CManagedComponentWrapper managedOleInstance =

destinationDataFlowComponent.Instantiate();

managedOleInstance.ProvideComponentProperties();

destinationDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManagerID = oledbConnectionManager.ID;

destinationDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManager =DtsConvert.ToConnectionManager90(oledbConnectionManager);

managedOleInstance.SetComponentProperty("AccessMode", 0);

// .. .. .. setting other component properties here..

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New();

path.AttachPathAndPropagateNotifications(

conversionDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]

);

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData();

// Get the destination's default input and virtual input.

IDTSInput90 input;

IDTSVirtualInput90 vInput;

// Get the destination's default input and virtual input.

input = destinationDataFlowComponent.InputCollection[0];

vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in

vInput.VirtualInputColumnCollection)

{

managedOleInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

// culprit Line. I can found vColumn.LineageID = source lineage ids

// while debugging.. can you please help me out here?

}

IDTSExternalMetadataColumn90 exColumn;

foreach (IDTSInputColumn90 inColumn in

destinationDataFlowComponent.InputCollection[0].InputColumnCollection)

{

exColumn = destinationDataFlowComponent.InputCollection[0].

ExternalMetadataColumnCollection[inColumn.Name];managedOleInstance.MapInputColumn(destinationDataFlowComponent.

InputCollection[0].ID, inColumn.ID, exColumn.ID);

}

managedOleInstance.ReleaseConnections();

}

Here you can see the red marked lines. Where I can found that the destination component is watching the source output columns lineage althoug its mapped with data conversion component.

Regards

Moim

|||

The input buffer of the destination will include all upstream columns, so that means both the ones that came from the source and those from the Data Conversion Tx. You will need to be selective in choosing the input columns.

Think of the buffer as it passes down the pipeline, or rather what columns are available -

Source Output - SourceCol1

Data Conversion Output - SourceCol1, SourceCol1(converted)

You don't seem to be distiguishing between the input columns and output columns in the data conversion setup, that seems strange. You would only want to select the converted columns I assume. You could use a name, description or the UpstreamComponentName property of the column perhaps.

|||

Hi,

I have similar requirement.I need to import data from csv files to sql server tables using ssis programatically.iam not able to create a data conversion task programatically and map the columns from the dataconversion task to the oledb destination.tried my level best and got struck.Is there a code demo availble?appreciating your help

|||

adarsha wrote:

Hi,

I have similar requirement.I need to import data from csv files to sql server tables using ssis programatically.iam not able to create a data conversion task programatically and map the columns from the dataconversion task to the oledb destination.tried my level best and got struck.Is there a code demo availble?appreciating your help

It would really help if you explain what you are getting stuck on and how far you have come so far. Posting your code would be even better.

-Jamie

|||

Hi,

//In the below example i have pumped data from sql table to csvs which works fine for me .But now i need to do the exact opposite task.(Pump data from csv files to sql table)

Problems i have :

1:

After adding a flatfile source iam not very sure how to exactly add input columns in FlatFile source as they dont get populated by themselves.I tried using datatable reading the columns from the file and add it and somewhat succeeded.But need a clear implementation help.

2:

To some extent when i succeeded in adding input columns to flat file source, the package failed due to data conversion problems(unicode to non unicode conversion error).I tried programatically building dataconversion task and had no clue how exactly to do it correctly and make it to work.

So i need help in adding input columns to flat file source and adding data conversion task and selecting only the converted columns to the oledb destination.

using System;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Reflection;

using System.Threading;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace CCE_ETL_Engine_Flatfiles

{

class Flatfile_Pump

{

Boolean mblnDebug = true;

string mstrAppLogFile;

string mstrDTSXFile;

string DataFileName;

string DestinationDataDirectory;

#region intialize

public void InitializeVariables(string strDTSPackageName)

{

DestinationDataDirectory = System.Configuration.ConfigurationManager.AppSettings.Get("DataFilePath");

DataFileName = System.Configuration.ConfigurationManager.AppSettings.Get("DataFileName");

Object objLock = new Object();

Monitor.Enter(objLock);

string strTimeStamp = DateTime.Now.Hour.ToString().PadLeft(2, '0') + DateTime.Now.Minute.ToString().PadLeft(2, '0') + DateTime.Now.Second.ToString().PadLeft(2, '0') + DateTime.Now.Millisecond.ToString();

Monitor.Exit(objLock);

mstrAppLogFile = System.Configuration.ConfigurationManager.AppSettings.Get("ApplicationLogFilePath") + @."\" + strDTSPackageName + "_" + strTimeStamp + ".log";

mstrDTSXFile = System.Configuration.ConfigurationManager.AppSettings.Get("DTSXFilePath") + @."\" + strDTSPackageName + "_" + strTimeStamp + ".dtsx";

if (System.Configuration.ConfigurationManager.AppSettings.Get("Debug").ToUpper() == "FALSE")

mblnDebug = false;

else

mblnDebug = true;

}

#endregion

#region constructor

public Flatfile_Pump(string strDTSPackageName)

{

InitializeVariables(strDTSPackageName);

}

#endregion

#region CreatePackage

public Package CreatePackage(string Name, string Description)

{

Package DataPump = new Package();

DataPump.PackageType = DTSPackageType.DTSDesigner90;

DataPump.Name = Name;

DataPump.Description = Description;

DataPump.CreatorComputerName = System.Environment.MachineName;

DataPump.CreatorName = System.Environment.UserName;

return DataPump;

}

#endregion

#region AddConnectionManagers

/// <summary>

/// Adds the OLEDB and FlatFile connection managers to the package.

/// </summary>

public void AddConnectionManagers(Package DataPump, string SqlConnection) Here iam adding 2 connection managers 1 for flatfile and 1 for OLEDB

{

// Add the OLEDB connection manager.

ConnectionManager ConnectionName;

ConnectionManager cmflatFile;

ConnectionName = DataPump.Connections.Add("OLEDB");

// Set stock properties.

ConnectionName.Name = "Remote Source";

ConnectionName.ConnectionString = SqlConnection;

//@."Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cce_control;Data Source=(wxp-9plyf1s);Auto Translate=False;";

// Add the Destination connection manager.

cmflatFile = DataPump.Connections.Add("FLATFILE");

// Set the stock properties.

cmflatFile.Properties["ConnectionString"].SetValue(cmflatFile, DestinationDataDirectory + DataFileName);

cmflatFile.Properties["Format"].SetValue(cmflatFile, "Delimited");

cmflatFile.Properties["DataRowsToSkip"].SetValue(cmflatFile, 0);

cmflatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmflatFile, true);

cmflatFile.Properties["Name"].SetValue(cmflatFile, "FlatFileConnection");

cmflatFile.Properties["RowDelimiter"].SetValue(cmflatFile, "\r\n");

cmflatFile.Properties["TextQualifier"].SetValue(cmflatFile, "\"");

}

#endregion

#region AddDataFlowTask

/// <summary>

/// Adds a DataFlow task to the Executables collection of the package.

/// Retrieves the MainPipe object from the TaskHost and stores it in

/// the dataFlow member variable

/// </summary>

public MainPipe AddDataFlowTask(Package DataPump)

{

TaskHost th = DataPump.Executables.Add("DTS.Pipeline") as TaskHost;

th.Name = "DataFlow";

th.Description = "The DataFlow task in the package.";

MainPipe dataFlow = th.InnerObject as MainPipe;

ComponentEvents componentEvents = new ComponentEvents();

dataFlow.Events = componentEvents as wrap.IDTSComponentEvents90;

return dataFlow;

}

#endregion

#region AddOLEDBSource

public IDTSComponentMetaData90 AddOLEDBSource(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe dataFlow, Package DataPump, string strSourceQuery)

{

// Declare and Add the component to the dataFlow metadata collection

IDTSComponentMetaData90 OLEDBsource;

OLEDBsource = dataFlow.ComponentMetaDataCollection.New();

// Set the common properties

OLEDBsource.ComponentClassID = "DTSAdapter.OLEDBSource";

OLEDBsource.Name = "cce_control";

OLEDBsource.Description = "Remote Source Server";

// Create an instance of the component

CManagedComponentWrapper instance = OLEDBsource.Instantiate();

instance.ProvideComponentProperties();

// Associate the runtime ConnectionManager with the component

OLEDBsource.RuntimeConnectionCollection[0].ConnectionManagerID = DataPump.Connections["Remote Source"].ID;

OLEDBsource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DataPump.Connections["Remote Source"]);

instance.SetComponentProperty("SqlCommand", strSourceQuery);

instance.SetComponentProperty("AccessMode", 2);

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

instance.ReleaseConnections();

return OLEDBsource;

}

#endregion

#region AddFlatFileDestination

public IDTSComponentMetaData90 AddFlatFileDestination(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe dataFlow, Package DataPump, IDTSComponentMetaData90 OLEDBsource)

{

// Declare and Add the component to the dataFlow metadata collection

IDTSComponentMetaData90 flatfileDestination;

flatfileDestination = dataFlow.ComponentMetaDataCollection.New();

// Set the common properties

flatfileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination";

flatfileDestination.Name = "FlatFileDestination";

flatfileDestination.Description = "Flat file destination";

// Create an instance of the component

CManagedComponentWrapper instance = flatfileDestination.Instantiate();

instance.ProvideComponentProperties();

// Associate the runtime ConnectionManager with the component

flatfileDestination.RuntimeConnectionCollection[0].ConnectionManagerID

= DataPump.Connections["FlatFileConnection"].ID;

flatfileDestination.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(DataPump.Connections["FlatFileConnection"]);

// Map a path between the Sort transformation component to the FlatFileDestination

dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(OLEDBsource.OutputCollection[0], flatfileDestination.InputCollection[0]);

// Add columns to the FlatFileConnectionManager

AddColumnsToFlatFileConnection(DataPump, flatfileDestination);

// Acquire the connection, reinitialize the metadata,

// map the columns, then release the connection.

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

MapFlatFileDestinationColumns(flatfileDestination);

instance.ReleaseConnections();

return flatfileDestination;

}

#endregion

#region AddColumnsToFlatFileConnection

public void AddColumnsToFlatFileConnection(Package DataPump, IDTSComponentMetaData90 flatfileDestination)

{

wrap.IDTSConnectionManagerFlatFile90 ff = null;

foreach (ConnectionManager cm in DataPump.Connections)

{

if (cm.Name == "FlatFileConnection")

{

ff = cm.InnerObject as wrap.IDTSConnectionManagerFlatFile90;

DtsConvert.ToConnectionManager90(cm);

}

}

int count = ff.Columns.Count;

// if the connection manager is null here, then we have a problem

if (ff != null)

{

// Get the upstream columns

IDTSVirtualInputColumnCollection90 vColumns = flatfileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;

for (int cols = 0; cols < vColumns.Count; cols++)

{

wrap.IDTSConnectionManagerFlatFileColumn90 col = ff.Columns.Add();

// If this is the last column, set the delimiter to CRLF.

// Otherwise keep the delimiter as ",".

if (cols == vColumns.Count - 1)

{

col.ColumnDelimiter = "\r\n";

}

else

{

col.ColumnDelimiter = @.",";

}

col.ColumnType = "Delimited";

col.DataType = vColumns[cols].DataType;

col.DataPrecision = vColumns[cols].Precision;

col.DataScale = vColumns[cols].Scale;

wrap.IDTSName90 name = col as wrap.IDTSName90;

name.Name = vColumns[cols].Name;

}

}

}

#endregion

#region MapFlatFileDestination Columns

public void MapFlatFileDestinationColumns(IDTSComponentMetaData90 flatfileDestination)

{

CManagedComponentWrapper wrp = flatfileDestination.Instantiate();

IDTSVirtualInput90 vInput = flatfileDestination.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

wrp.SetUsageType(flatfileDestination.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

// For each column in the input collection

// find the corresponding external metadata column.

foreach (IDTSInputColumn90 col in flatfileDestination.InputCollection[0].InputColumnCollection)

{

IDTSExternalMetadataColumn90 exCol = flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];

wrp.MapInputColumn(flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);

}

}

#endregion

#region Exec_DataPump--This is just the execution where i get values from like server name etc and create the package and execute it.

public Boolean DataPump(string SqlConnection, string strSourceQuery)

{

Package DataPump;

PackageEvents packageEvents;

MainPipe dataFlow;

IDTSComponentMetaData90 OLEDBsource, flatfileDestination;

packageEvents = new PackageEvents();

DataPump = CreatePackage("SSISPackage", "The package for the Flatfile pump");

AddConnectionManagers(DataPump, SqlConnection);

dataFlow = AddDataFlowTask(DataPump);

OLEDBsource = AddOLEDBSource(dataFlow, DataPump, strSourceQuery);

flatfileDestination = AddFlatFileDestination(dataFlow, DataPump, OLEDBsource);

DTSExecResult status = DataPump.Validate(null, null, packageEvents, null);

if (mblnDebug)

{

Application dts = new Application();

dts.SaveToXml(mstrDTSXFile, DataPump, packageEvents);

}

foreach (Variable v in DataPump.Variables)

{

if (v.Name.ToUpper() == "EXECUTIONINSTANCEGUID")

{

break;

}

}

if (status == DTSExecResult.Success)

{

DTSExecResult result = DataPump.Execute(null, null, packageEvents, null, null);

foreach (Variable v in DataPump.Variables)

{

Console.WriteLine(v.Name.ToUpper());

}

}

return true;

}

#endregion

}

}

Programmatically creating Transformation Script Component

Does anyone have any examples of programmatically creating a Transformation Script Component (or Source/Destination) in the dataflow? I have been able to create other Transforms for the dataflow like Derived Column, Sort, etc. but for some reason the Script Component doesn't seem to work the same way.

I have done it as below trying many ways to get the componentClassId including the AssemblyQualifiedname & the GUID as well. No matter, what I do, when it hits the ProvideComponentProperties, it get Exception from HRESULT: 0xC0048021

IDTSComponentMetaData90 scriptPropType = dataFlow.ComponentMetaDataCollection.New();

scriptPropType.Name = "Transform Property Type";

scriptPropType.ComponentClassID = "DTSTransform.ScriptComponent";

// have also tried scriptPropType.ComponentClassID =typeof(Microsoft.SqlServer.Dts.Pipeline.ScriptComponent).AssemblyQualifiedName;

scriptPropType.Description = "Transform Property Type";

CManagedComponentWrapper instance2 = scriptPropType.Instantiate();

instance2.ProvideComponentProperties();

Any help or examples would be greatly appreciated! Thanks!

If you have not deduced, the error 0xC0048021 means that the component is not installed basically, so I'd say whatever you are using for the ComponentClassID is not right as you suspect. (http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0048021.html)

As a start point, what values have you tried., and did they include this -

Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Is that assembly in the GAC?

|||

thanks! worked like a charm.

Now...I have the inputs selected and the outputs added but can't figure out how to add the actual script code. I don't see any custom properties that look like a script. I know I have to override the ScriptMain routines, just not sure how.

Again any help or examples would be great. thanks

|||

Can you find a SourceCode property? Looking in Books Online, it seems that MS have pretty much neglected to document the component object model, and even in the limited component property documentation (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/56f5df6a-56f6-43df-bca9-08476a3bd931.htm#script) this property is not listed. I’d say they just have not documented this at all, so is it even supported? A think it is rather pants if you cannot build packages entirely in code. Still, have a look at the SourceCode property, and perhaps have a look at what it is if you load an existing package via the object model. Another place to look is the raw XML of an existing DTSX file, but bare in mind this has been XML encoded in various unknown ways and may not be the directly assignable as you see the Xml, hence I suggested looking at an existing component through the object model.

|||

Yes, found the SourceCode custom property after last post, but am having lots of trouble figuring out what to put in there.

I am comparing the XML of the a package where the script was created through bids vs the script I'm trying to create programmatically. In Bids, It goes from an arrayElementCount of 0 with the default script to an arrayElementCount of 4 once you make change to the script. It adds an element for a .vsaproj, an element for the references, an element for .vsaitem. No clue how to add those items. I can see that the Script Task on the Control Flow does something similiar but has built in routines SetUniqueVsaProjectName & CodeProvider.PutSourceCode. So far, I'm not finding the corresponding routines for the Script Component. Any clues on that? thanks

|||

I've been doing some playing with this. The value of SourceCode is just a string array, so you can easily create a this array to set it. There are four items in the array as you note. They seem to be in pairs, a moniker and some detail. You can examine these in detail for yourself, but the monikers seem to be a standard format and, and even the project xml seems fairly sensible, then you just have the VB.Net code. So far there is nothing that could not be easily derived, even the moniker and project format just uses a Guid, albeit formatted ( Guid.ToString("n") ).

The major issue is that you also need to supply the wrapper code. Look in the project Xml (array element 1, or the second item) and you will see if references 3 files, only one of which is ScriptMain, the code we normally write. The other two are the wrappers. You can see these in the VSA designer if you look, but they are auto-generated at design-time for you.

So without these wrappers our basic code will never compile. This of course raises the issue of compilation. Normally the PreCompile property is true, so the designer compiles the code. This ends up as base 64 encoded string in another property, BinaryCode (?).

We can access the VSA compilation engine, and may even be able to get the compiled code back as binary, so we can base64 encode and set the property, but we still lack the wrappers. These seem like a lot of hard work, it seems MS generates them, but in sealed/internal design-time modules. So maybe we just write our own wrappers? Possible, but this means the package will not be maintainable via the UI. Is that an issue?

|||

Thanks for looking into it. We were on the same track. We did get the script component to work but had to set the Precompile to false. Since we are going to be running it on 64bit, that won't work for us. We need to set the property "BinaryCode" to the compiled code. Looking into ways to get the binary code. But we are also pursuing building custom components instead of script components.

String[] scriptValue = newString[4];

scriptValue[0] = @."dts://Scripts/" + scriptPropType.CustomPropertyCollection["VsaProjectName"].Value + @."/" + scriptPropType.CustomPropertyCollection["VsaProjectName"].Value + @.".vsaproj";

scriptValue[1] = ConsoleApplication1.Properties.Resources.ProjectFile;

scriptValue[2] = @."dts://Scripts/" + scriptPropType.CustomPropertyCollection["VsaProjectName"].Value + @."/ScriptMain.vsaitem";

scriptValue[3] = "' Microsoft SQL Server Integration Services user script component\r\n"

+ "' This is your new script component in Microsoft Visual Basic .NET \r\n"

+ "' ScriptMain is the entrypoint class for script components\r\n"

+ "\r\n"

+ "Imports System \r\n"

+ "Imports System.Data \r\n"

+ "Imports System.Math \r\n"

+ "Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper \r\n"

+ "Imports Microsoft.SqlServer.Dts.Runtime.Wrapper \r\n"

+ " \r\n"

+ "Public Class ScriptMain \r\n"

+ " Inherits UserComponent \r\n"

+ " \r\n"

+ " Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) \r\n"

+ " ' \r\n"

+ " 'Code Here \r\n"

+ " ' \r\n"

+ " End Sub \r\n"

+ " \r\n"

+ "End Class \r\n";

IDTSDesigntimeComponent90 anIDTSDesigntimeComponent90 = instance2 asIDTSDesigntimeComponent90;

anIDTSDesigntimeComponent90.SetComponentProperty("SourceCode", scriptValue);

anIDTSDesigntimeComponent90.SetComponentProperty("PreCompile", false);

|||Yep the 64bit will be a killer for you. Whilst it is possible to genarete your own wrappers, custom components may well be easier. To be honest you could probably write your own component that accepted .Net code, easier than you can simulate the stock component. If the code is reasonably static custom components would be easier, I find them easier anyway.|||

You have done very good investigation. Could you please post full example of how youprogrammatically created a Transformation Script Component. I have a similar task and would appreciate your help.

|||

I'm also trying to generate a script component programmatically and this post has been very useful, just about the only info I could find on it.

I've followed through what's above and incorporated this into my own project, but I'm stuck with this bit (from the above):

scriptValue[1] = ConsoleApplication1.Properties.Resources.ProjectFile;

I'm guessing this somehow adds the references in - but I can't find the "Properties" of (I'm assuming) the namespace of the package generation class. Can anyone help here? Sounds like ProjectFile is a member ofCreateTemporaryVCProject - buried deep in the framework and a little short on documentation.

I'm also concerned about the need to supply the wrapper files as discussed. Did you manage to get around this or would it still be necessary to supply these; even once you've added the XML resources I refer to above? If so I reckon I may have to take the plunge with custom components.

|||

ConsoleApplication1.Properties.Resources.ProjectFile refers to a property called ProjectFile in the application CarlaC wrote. That is basically a string resource. Look at the Resources tab in your Visual Studio Project, or have a look in the MSDN docs on this.

It is not really important, what is important is the XML that that property contained. As per the name and my description above, it is the "project file", the XML stuff a bit like what you see if you open a csproj in notepad now. The project file will hold the references, and other project level infromation. The best thing to do would be to reverse engineer a package you built in the designer. That is what I did, and to be honest I just don't think this is feasible. So I only spent a few hours on the topic, but there was an awfull lot of code generation logic embeded in the UI that you cannot access. Damn internal methods, seaaled classes etc, and decompiling code to that degree, yuk.

Personally I think this is just too much work. I would find it easier to write my own components. Forget the dynamic stuff, it is too much work, and I cannot see anyone getting eneough reuse from such effort.

|||

CarlaC,

Not sure if you got the answers you were looking for, it's been a while since you posted this message. However, I'm working on a similar scenario and have found the exact solution for creating a script task and embedding the code in it.

To make this all work...

1. Create a new package in the designer and add the script object (and associated code).

2. Right-click on the package and select "View Code"

3. Within the XML will be two "CDATA" tags, one starting with "<VisualStudioProject>" and the other starting with "' Microsoft SQL Server Integration Services Script Task".

4. Go to the project you are creating to build your script task, I have chosen to add two string variables to the resource file which are used to hold the script in both tags mentioned above (i.e. "ScriptTaskCode" and "ScriptTaskProjFile").

5. Once you have set the two properties in your resource file (or in the local code page) you can then use the following code to load the scripts into the script component:

'NOTE: You will need a reference to the following in your class:

'C:\Program Files\Microsoft SQL Server\90\DTS\Binn\Microsoft.SqlServer.VSAHosting.dll

'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ScriptTask.dll

'add a scripting task to the package

scriptTaskHost = TryCast(Me.m_pkg.Executables.Add("STOCKCRIPTTASK"), TaskHost)

scriptTaskHost.Properties("Name").SetValue(scriptTaskHost, "PkgUpdate")

scriptTaskHost.Properties("Description").SetValue(scriptTaskHost, "PkgUpdate")

'load the script task code from the resource file

scriptTask = TryCast(scriptTaskHost.InnerObject, ScriptTask)

scriptTask.SetUniqueVsaProjectName()

scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/ScriptMain.vsaitem", My.Resources.ScriptTaskCode)

scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/" & scriptTask.VsaProjectName & ".vsaproj", My.Resources.ScriptTaskProjFile)

scriptTask.PreCompile = False

'save everything

ssisApp = New Application()

ssisApp.SaveToDtsServer(Me.m_pkg, Nothing, "MSDB\" & Me.m_pkg.Name, serverName)

|||Have any of you managed to create a Source Script Component programmatically. I seem to only be able to create Transformation Script Components. Thanks.

Programmatically creating Transformation Script Component

Does anyone have any examples of programmatically creating a Transformation Script Component (or Source/Destination) in the dataflow? I have been able to create other Transforms for the dataflow like Derived Column, Sort, etc. but for some reason the Script Component doesn't seem to work the same way.

I have done it as below trying many ways to get the componentClassId including the AssemblyQualifiedname & the GUID as well. No matter, what I do, when it hits the ProvideComponentProperties, it get Exception from HRESULT: 0xC0048021

IDTSComponentMetaData90 scriptPropType = dataFlow.ComponentMetaDataCollection.New();

scriptPropType.Name = "Transform Property Type";

scriptPropType.ComponentClassID = "DTSTransform.ScriptComponent";

// have also tried scriptPropType.ComponentClassID =typeof(Microsoft.SqlServer.Dts.Pipeline.ScriptComponent).AssemblyQualifiedName;

scriptPropType.Description = "Transform Property Type";

CManagedComponentWrapper instance2 = scriptPropType.Instantiate();

instance2.ProvideComponentProperties();

Any help or examples would be greatly appreciated! Thanks!

If you have not deduced, the error 0xC0048021 means that the component is not installed basically, so I'd say whatever you are using for the ComponentClassID is not right as you suspect. (http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0048021.html)

As a start point, what values have you tried., and did they include this -

Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Is that assembly in the GAC?

|||

thanks! worked like a charm.

Now...I have the inputs selected and the outputs added but can't figure out how to add the actual script code. I don't see any custom properties that look like a script. I know I have to override the ScriptMain routines, just not sure how.

Again any help or examples would be great. thanks

|||

Can you find a SourceCode property? Looking in Books Online, it seems that MS have pretty much neglected to document the component object model, and even in the limited component property documentation (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/56f5df6a-56f6-43df-bca9-08476a3bd931.htm#script) this property is not listed. I’d say they just have not documented this at all, so is it even supported? A think it is rather pants if you cannot build packages entirely in code. Still, have a look at the SourceCode property, and perhaps have a look at what it is if you load an existing package via the object model. Another place to look is the raw XML of an existing DTSX file, but bare in mind this has been XML encoded in various unknown ways and may not be the directly assignable as you see the Xml, hence I suggested looking at an existing component through the object model.

|||

Yes, found the SourceCode custom property after last post, but am having lots of trouble figuring out what to put in there.

I am comparing the XML of the a package where the script was created through bids vs the script I'm trying to create programmatically. In Bids, It goes from an arrayElementCount of 0 with the default script to an arrayElementCount of 4 once you make change to the script. It adds an element for a .vsaproj, an element for the references, an element for .vsaitem. No clue how to add those items. I can see that the Script Task on the Control Flow does something similiar but has built in routines SetUniqueVsaProjectName & CodeProvider.PutSourceCode. So far, I'm not finding the corresponding routines for the Script Component. Any clues on that? thanks

|||

I've been doing some playing with this. The value of SourceCode is just a string array, so you can easily create a this array to set it. There are four items in the array as you note. They seem to be in pairs, a moniker and some detail. You can examine these in detail for yourself, but the monikers seem to be a standard format and, and even the project xml seems fairly sensible, then you just have the VB.Net code. So far there is nothing that could not be easily derived, even the moniker and project format just uses a Guid, albeit formatted ( Guid.ToString("n") ).

The major issue is that you also need to supply the wrapper code. Look in the project Xml (array element 1, or the second item) and you will see if references 3 files, only one of which is ScriptMain, the code we normally write. The other two are the wrappers. You can see these in the VSA designer if you look, but they are auto-generated at design-time for you.

So without these wrappers our basic code will never compile. This of course raises the issue of compilation. Normally the PreCompile property is true, so the designer compiles the code. This ends up as base 64 encoded string in another property, BinaryCode (?).

We can access the VSA compilation engine, and may even be able to get the compiled code back as binary, so we can base64 encode and set the property, but we still lack the wrappers. These seem like a lot of hard work, it seems MS generates them, but in sealed/internal design-time modules. So maybe we just write our own wrappers? Possible, but this means the package will not be maintainable via the UI. Is that an issue?

|||

Thanks for looking into it. We were on the same track. We did get the script component to work but had to set the Precompile to false. Since we are going to be running it on 64bit, that won't work for us. We need to set the property "BinaryCode" to the compiled code. Looking into ways to get the binary code. But we are also pursuing building custom components instead of script components.

String[] scriptValue = new String[4];

scriptValue[0] = @."dts://Scripts/" + scriptPropType.CustomPropertyCollection["VsaProjectName"].Value + @."/" + scriptPropType.CustomPropertyCollection["VsaProjectName"].Value + @.".vsaproj";

scriptValue[1] = ConsoleApplication1.Properties.Resources.ProjectFile;

scriptValue[2] = @."dts://Scripts/" + scriptPropType.CustomPropertyCollection["VsaProjectName"].Value + @."/ScriptMain.vsaitem";

scriptValue[3] = "' Microsoft SQL Server Integration Services user script component\r\n"

+ "' This is your new script component in Microsoft Visual Basic .NET \r\n"

+ "' ScriptMain is the entrypoint class for script components\r\n"

+ "\r\n"

+ "Imports System \r\n"

+ "Imports System.Data \r\n"

+ "Imports System.Math \r\n"

+ "Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper \r\n"

+ "Imports Microsoft.SqlServer.Dts.Runtime.Wrapper \r\n"

+ " \r\n"

+ "Public Class ScriptMain \r\n"

+ " Inherits UserComponent \r\n"

+ " \r\n"

+ " Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) \r\n"

+ " ' \r\n"

+ " 'Code Here \r\n"

+ " ' \r\n"

+ " End Sub \r\n"

+ " \r\n"

+ "End Class \r\n";

IDTSDesigntimeComponent90 anIDTSDesigntimeComponent90 = instance2 as IDTSDesigntimeComponent90;

anIDTSDesigntimeComponent90.SetComponentProperty("SourceCode", scriptValue);

anIDTSDesigntimeComponent90.SetComponentProperty("PreCompile", false);

|||Yep the 64bit will be a killer for you. Whilst it is possible to genarete your own wrappers, custom components may well be easier. To be honest you could probably write your own component that accepted .Net code, easier than you can simulate the stock component. If the code is reasonably static custom components would be easier, I find them easier anyway.|||

You have done very good investigation. Could you please post full example of how you programmatically created a Transformation Script Component. I have a similar task and would appreciate your help.

|||

I'm also trying to generate a script component programmatically and this post has been very useful, just about the only info I could find on it.

I've followed through what's above and incorporated this into my own project, but I'm stuck with this bit (from the above):

scriptValue[1] = ConsoleApplication1.Properties.Resources.ProjectFile;

I'm guessing this somehow adds the references in - but I can't find the "Properties" of (I'm assuming) the namespace of the package generation class. Can anyone help here? Sounds like ProjectFile is a member of CreateTemporaryVCProject - buried deep in the framework and a little short on documentation.

I'm also concerned about the need to supply the wrapper files as discussed. Did you manage to get around this or would it still be necessary to supply these; even once you've added the XML resources I refer to above? If so I reckon I may have to take the plunge with custom components.

|||

ConsoleApplication1.Properties.Resources.ProjectFile refers to a property called ProjectFile in the application CarlaC wrote. That is basically a string resource. Look at the Resources tab in your Visual Studio Project, or have a look in the MSDN docs on this.

It is not really important, what is important is the XML that that property contained. As per the name and my description above, it is the "project file", the XML stuff a bit like what you see if you open a csproj in notepad now. The project file will hold the references, and other project level infromation. The best thing to do would be to reverse engineer a package you built in the designer. That is what I did, and to be honest I just don't think this is feasible. So I only spent a few hours on the topic, but there was an awfull lot of code generation logic embeded in the UI that you cannot access. Damn internal methods, seaaled classes etc, and decompiling code to that degree, yuk.

Personally I think this is just too much work. I would find it easier to write my own components. Forget the dynamic stuff, it is too much work, and I cannot see anyone getting eneough reuse from such effort.

|||

CarlaC,

Not sure if you got the answers you were looking for, it's been a while since you posted this message. However, I'm working on a similar scenario and have found the exact solution for creating a script task and embedding the code in it.

To make this all work...

1. Create a new package in the designer and add the script object (and associated code).

2. Right-click on the package and select "View Code"

3. Within the XML will be two "CDATA" tags, one starting with "<VisualStudioProject>" and the other starting with "' Microsoft SQL Server Integration Services Script Task".

4. Go to the project you are creating to build your script task, I have chosen to add two string variables to the resource file which are used to hold the script in both tags mentioned above (i.e. "ScriptTaskCode" and "ScriptTaskProjFile").

5. Once you have set the two properties in your resource file (or in the local code page) you can then use the following code to load the scripts into the script component:

'NOTE: You will need a reference to the following in your class:

'C:\Program Files\Microsoft SQL Server\90\DTS\Binn\Microsoft.SqlServer.VSAHosting.dll

'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ScriptTask.dll

'add a scripting task to the package

scriptTaskHost = TryCast(Me.m_pkg.Executables.Add("STOCKCRIPTTASK"), TaskHost)

scriptTaskHost.Properties("Name").SetValue(scriptTaskHost, "PkgUpdate")

scriptTaskHost.Properties("Description").SetValue(scriptTaskHost, "PkgUpdate")

'load the script task code from the resource file

scriptTask = TryCast(scriptTaskHost.InnerObject, ScriptTask)

scriptTask.SetUniqueVsaProjectName()

scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/ScriptMain.vsaitem", My.Resources.ScriptTaskCode)

scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/" & scriptTask.VsaProjectName & ".vsaproj", My.Resources.ScriptTaskProjFile)

scriptTask.PreCompile = False

'save everything

ssisApp = New Application()

ssisApp.SaveToDtsServer(Me.m_pkg, Nothing, "MSDB\" & Me.m_pkg.Name, serverName)

|||Have any of you managed to create a Source Script Component programmatically. I seem to only be able to create Transformation Script Components. Thanks.