Adding the Data Flow Task Programmatically

Is it possible to add a Fuzzy Grouping Transformation in a Data flow task by Programmatically ? If it possible, what is the C# or VB .net code for that ?Yes, you can. If you see this topic in Books Online it explains how to do it, and how to get the appropriate ProgID. "Adding Data Flow Components Programmatically"

Adding multiple textboxes to PageHeader

I am creating matrix reports programmatically and have come up against a problem where I can only add a single textbox into the pageheader. Writing the report using the report designer software I can add multiple textboxes to the pageheader, however trying to add many of them using the ReportItem object I keep getting a syste.object[] cannot be used in this context. error.

for (int i = 0; i < Items.Count; i++)
{
reportItems.Items = new object[] { CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle) };
}

This is the line of code that I am hitting the problem. This works fine, however only inserts the last textbox in the Items Array.

If I change the code to

for (int i = 0; i < Items.Count; i++)

{

reportItems.Items = new object[] { CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle) };

}

It executes, but fails when trying to render it into XML.

Has anyone managed to get multiple textboxes programatically into the pageheader?In the first approach you are overwriting the Items array with each iteration of the for loop. And in the second approach you are setting each entry in the Items array to a new object array, so you end up with an array of arrays. Instead what you should do is just add the object to the array. Try using the following code.

// create a new array for the text boxes in the header
reportItems.Items = new object[Items.Count];

// iterate though all the Items creating a textbox, which is added to the report items object array
for (int i = 0; i < Items.Count; i++)
{
reportItems.Items = CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle);
}

Ian

Adding Lookup Programmatically:How can I add column from reference dataset to the transformation

Hello,

I have created SSIS package programmatically, I want to add Lookup transformation,

How can I add column from reference dataset to the transformation?

I have try to add new output column but it gives me an validation error, I write following coed to add new output column to lookup.

IDTSOutputColumn90 outputColumn = this.lookup.OutputCollection[0].OutputColumnCollection.New();

outputColumn.Name = col.Name;

outputColumn.Description = "Staging table output";

outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

outputColumn.ErrorOrTruncationOperation = "Copy Column";

outputColumn.SetDataTypeProperties(col.DataType, col.Length, col.Precision, col.Scale, col.CodePage);

Please suggest other way to add column from reference dataset to transformation output.

You have to set the reference column name to the appropriate custom property of the output column. You do not have to set any truncation or data type attributes, just get the IDTSDesigntimeComponent90 interface and call SetOutputColumnProperty on it. It should set these attributes for you (assuming your reference metadata is set to the component properly).

It should look like this:

designTimeComponent.SetOutputColumnProperty(outputID, outputColumnID, "CopyFromReferenceColumn", refColumnName);

HTH.

|||

Thanks !

Now I have added designtimeComponent property, but I got following error in setting reference table command

"Command text was not set fro the command object" ,before setting designtimeproperties it's working fine

code I used:

this.lookup = this.dataFlow.ComponentMetaDataCollection.New();

// Set component's stock properties.

this.lookup.ComponentClassID = "DTSTransform.Lookup";

this.lookup.Name = "LookupTransform";

this.lookup.Description = "Lookup component";

CManagedComponentWrapper instance = this.lookup.Instantiate();

instance.ProvideComponentProperties();

this.lookup.RuntimeConnectionCollection[0].ConnectionManagerID

= this.package.Connections["OLEDBConnectionLookup"].ID;

this.lookup.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(this.package.Connections["OLEDBConnectionLookup"]);

instance.SetComponentProperty("SqlCommand", "Select id as stg_id1 from [dbo].[Temp2]");

// Attach path between the OLEDB source components Output, and the Sort Component's Input.

this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(

this.oledbSource.OutputCollection[0], this.lookup.InputCollection[0]);

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

//Ingore on failuare

this.lookup.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;

IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

IDTSInputColumn90 col = instance.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

instance.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");

}

IDTSDesigntimeComponent90 designTimeComponent = this.lookup.Instantiate();

designTimeComponent.ProvideComponentProperties();

designTimeComponent.AcquireConnections(null);

designTimeComponent.ReinitializeMetaData();

IDTSOutputColumn90 newColumn = designTimeComponent.InsertOutputColumnAt(this.lookup.OutputCollection[0].ID, 0, "stg_id1", "reference column");

designTimeComponent.SetOutputColumnProperty(this.lookup.OutputCollection[0].ID, newColumn.ID, "CopyFromReferenceColumn", "stg_id1");

designTimeComponent.ReleaseConnections();

instance.ReleaseConnections();

Any suggetions,Can I set component property (Such as SqlCommand) at design time.

|||

Well, this code seems to have many issues. Let's try to fix it iteratively: for the start do not call ProvideComponentProperties twice, it will reset everything. Also, ReinitializeMetadata has to be called only once.

How many upstream columns are coming to your lookup, it seems like you are mapping all of them to stg_id1. Only one can be used for this lookup.

Try to fix these things and then post the cleaned code and the new errors you are getting. Mark the places in the code where the errors are captured.

Hopefully, we will be able to nail it in the next iteration.

|||

Thanks Bob !

U mean to say I set all component properties using 'IDTSDesigntimeComponent90' instance not using 'CManagedComponentWrapper' (right?)

Can u explain me how can i set component properties at design time

|||

Hi

Now code is working fine but it gives a Validation Error,''Input column "Name" has datatype which cannot be joined on"

I have to input columns id & name. and I want to join on single column 'id',Now what can I do to remove validation error

// Add the component to the DataFlow task.

this.lookup = this.dataFlow.ComponentMetaDataCollection.New();

// Set component's stock properties.

this.lookup.ComponentClassID = "DTSTransform.Lookup";

this.lookup.Name = "LookupTransform";

this.lookup.Description = "Lookup component";

IDTSDesigntimeComponent90 designTimeComponent = this.lookup.Instantiate();

designTimeComponent.ProvideComponentProperties();

this.lookup.RuntimeConnectionCollection[0].ConnectionManagerID

= this.package.Connections["OLEDBConnectionLookup"].ID;

this.lookup.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(this.package.Connections["OLEDBConnectionLookup"]);

designTimeComponent.SetComponentProperty("SqlCommand", "Select id as stg_id1 from [dbo].[Temp2]");

// Attach path between the OLEDB source components Output, and the Sort Component's Input.

this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(

this.oledbSource.OutputCollection[0], this.lookup.InputCollection[0]);

designTimeComponent.AcquireConnections(null);

designTimeComponent.ReinitializeMetaData();

IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

foreach (IDTSInputColumn90 col inthis.lookup.InputCollection[0].InputColumnCollection)

{

if (col.Name == "id")

designTimeComponent.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");

}

//Ingore on failuare

this.lookup.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;

IDTSOutputColumn90 newColumn = designTimeComponent.InsertOutputColumnAt(this.lookup.OutputCollection[0].ID, 0, "stg_id1", "reference column");

designTimeComponent.SetOutputColumnProperty(this.lookup.OutputCollection[0].ID, newColumn.ID, "CopyFromReferenceColumn", "stg_id1");

designTimeComponent.ReleaseConnections();

Thanks Bob for solution, can you give me ur e-mail id so I can ask you que. directly if I have

My id omkar.pimplekar@.gmail.com

|||It looks to me that you are selecting every column, as though they all take part in the join, see

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

Apply the same filter that you do in the following section, so only select the id column.

|||

Yes he is selecting every column, and it is not necessary. You can select only the column you want to join. Replace the two loops with one like this:

Code Snippet

IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

if (vColumn.Name == "id")

{

IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

designTimeComponent.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");

}

}

I do not think that is causing your error though. The error is caused by data type mismatch between your "id" and "stg_id1" columns. They need to exactly match in data types.

HTH.

|||

Hi,

Thanks Bob,

Now All working fine,again thanks for quick suggetions

Adding Fuzzy Lookup Programmatically

I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:

IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();

FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";

FuzzyLookupDF.Name = "FuzzyLookup";

I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.

Thanks!

amber

Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx

There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||

I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:

instance.SetComponentProperty("JoinType", 2);

I get the following error:

System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282

I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".

Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.

|||

For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||

You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.

"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.

HTH,

Bob

Adding Fuzzy Lookup Programmatically

I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:

IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();

FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";

FuzzyLookupDF.Name = "FuzzyLookup";

I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.

Thanks!

amber

Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx

There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||

I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:

instance.SetComponentProperty("JoinType", 2);

I get the following error:

System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282

I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".

Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.

|||

For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||

You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.

"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.

HTH,

Bob

Adding Fuzzy Lookup Programmatically

I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:

IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();

FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";

FuzzyLookupDF.Name = "FuzzyLookup";

I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.

Thanks!

amber

Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx

There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||

I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:

instance.SetComponentProperty("JoinType", 2);

I get the following error:

System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282

I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".

Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.

|||

For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||

You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.

"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.

HTH,

Bob

Adding Foreach container programmatically and setting the enumerator properties

Hi

I have a package which contains a foreach container. Can anyone help me in setting the properties for the enumerators programmatically? I am trying to set the properties for the enumerator "ForEach File Enumerator"

The properties which i need to set are

1. Folder

2. File Type

3. Traverse Subfolder

4. Retrieve File Name

Thanks in Advance

Suganya

Can anyone help me..?Am still searching for a solution

-Suganya