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