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 Reference and Importing .NET into Script Transformation

I have a .NET component that I want to import into a Script Transformation of a Data Flow. Going into the script code (editing Script transformation and clocking "Design Script" button), I try to "Add Reference" to the component (Add Reference selection under "Project" menu), but I do not see it - nor do I have the option to Browse for the component.

How do I establish a reference to an external .NET component so I can use it in my transformation? It seems unnecessary to have to add all of the class modules for the .NET component into the transformation or Copy/Paste the code from those same class modules just to execute that code?

Any ideas?

Many thanks...

You'll need to put the assembly into the framework folder for it to be found by the Add Reference dialog-

C:\WINDOWS\Microsoft.NET\Framework\v2.0.<whatever>\

You will also need to GAC it for runtime.

|||Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.|||

I have a very simple .dll with a method that returns a string.

I can see it, and therefore reference it through the script. However, when I run the package I get the following error:

Could not load file assembly ‘Test_dll’, version=1.0.0.0,Culture=neutral, Publickey=null’ or one of its dependencies. The system cannot find the file specified.

What does it mean?

Thanks.
-w

|||I see that publickey is null, hence it's not strong named, hence it could not have been placed in the GAC. You need to strong name it (look up docs for sn.exe) and place it in the GAC (gacutil /if mydll.dll). Hope this helps.|||

Where do I add the DLL and GAC it - on the Client machine performing the development or on the local SQL Server machine?

|||Wherever you execute the package, so both.

I assume you will execute the package on the workstation during development, so it will need to be GAC'd there just for testing the package, and when you deploy to the server it will also need to be in the server's GAC.|||If the package is saved in File System as opposed to SQL Server, am I correct in assuming that the package, even initiated from a command prompt or BAT/CMD file, still "runs" on the SQL Server?|||The package runs on the machine that is running the bat file (dtexec).|||

DouglasL wrote:

Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.

So is there a way to refence other assemblies from a VSA script task. For example we have a web service that uses WSE and need to reference this from our script. We also have a generic proxy which uses this which we need to reference. How do we do this?

The reason I'm asking is that I'm working with one of our .Net guys on this and he recoiled and squirmed his face when I talked about putting DLLs into Windows\Microsoft.Net\Framework folder. And I can kinda see his point. VSA does seem rather limited in this respect.

-Jamie|||Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.
|||

DouglasL wrote:

Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.

Thanks for the confirmation Doug.

We've got 2 options at the moment. Stick them in .NET framework dir or roll our own tasks. Guess which is looking most likely at the moment!!!

-Jamie|||Its worth pointing something out here that I believe to be true.

The DLL does need to get put into the .NET Framework directory as Doug has said - but only to enable you to use it at design-time. The DLL does not need to be there at runtime - VSA can pick it up from the GAC. To prove this - change the name of one of your DLLs that has been put in this folder in order for VSA to use it. You'll get errors at design-time because VSA won't be able to find the DLL but your package will still execute successfully if the DLL is GAC'd. This means that the DLL only has to go into the nasty .NET Framework directory on development machines - not on your machines that execute the packages in a live environment

Perhaps this is obvious to people that know .Net intimately but it was a pleasant surprise to me.

-Jamie|||Just for future reference, a BOL link to the topic Doug mentions. It doesn't highlight the runtime vs design-time GAC requirement though.

Using the .NET Framework and Other Assemblies in the Script Component

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/c3913c15-66aa-4b61-89b5-68488fa5f0a4.htm

Adding Reference and Importing .NET into Script Transformation

I have a .NET component that I want to import into a Script Transformation of a Data Flow. Going into the script code (editing Script transformation and clocking "Design Script" button), I try to "Add Reference" to the component (Add Reference selection under "Project" menu), but I do not see it - nor do I have the option to Browse for the component.

How do I establish a reference to an external .NET component so I can use it in my transformation? It seems unnecessary to have to add all of the class modules for the .NET component into the transformation or Copy/Paste the code from those same class modules just to execute that code?

Any ideas?

Many thanks...

You'll need to put the assembly into the framework folder for it to be found by the Add Reference dialog-

C:\WINDOWS\Microsoft.NET\Framework\v2.0.<whatever>\

You will also need to GAC it for runtime.

|||Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.|||

I have a very simple .dll with a method that returns a string.

I can see it, and therefore reference it through the script. However, when I run the package I get the following error:

Could not load file assembly ‘Test_dll’, version=1.0.0.0,Culture=neutral, Publickey=null’ or one of its dependencies. The system cannot find the file specified.

What does it mean?

Thanks.
-w

|||I see that publickey is null, hence it's not strong named, hence it could not have been placed in the GAC. You need to strong name it (look up docs for sn.exe) and place it in the GAC (gacutil /if mydll.dll). Hope this helps.|||

Where do I add the DLL and GAC it - on the Client machine performing the development or on the local SQL Server machine?

|||Wherever you execute the package, so both.

I assume you will execute the package on the workstation during development, so it will need to be GAC'd there just for testing the package, and when you deploy to the server it will also need to be in the server's GAC.|||If the package is saved in File System as opposed to SQL Server, am I correct in assuming that the package, even initiated from a command prompt or BAT/CMD file, still "runs" on the SQL Server?|||The package runs on the machine that is running the bat file (dtexec).|||

DouglasL wrote:

Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.

So is there a way to refence other assemblies from a VSA script task. For example we have a web service that uses WSE and need to reference this from our script. We also have a generic proxy which uses this which we need to reference. How do we do this?

The reason I'm asking is that I'm working with one of our .Net guys on this and he recoiled and squirmed his face when I talked about putting DLLs into Windows\Microsoft.Net\Framework folder. And I can kinda see his point. VSA does seem rather limited in this respect.

-Jamie|||Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.|||

DouglasL wrote:

Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.

Thanks for the confirmation Doug.

We've got 2 options at the moment. Stick them in .NET framework dir or roll our own tasks. Guess which is looking most likely at the moment!!!

-Jamie|||Its worth pointing something out here that I believe to be true.

The DLL does need to get put into the .NET Framework directory as Doug has said - but only to enable you to use it at design-time. The DLL does not need to be there at runtime - VSA can pick it up from the GAC. To prove this - change the name of one of your DLLs that has been put in this folder in order for VSA to use it. You'll get errors at design-time because VSA won't be able to find the DLL but your package will still execute successfully if the DLL is GAC'd. This means that the DLL only has to go into the nasty .NET Framework directory on development machines - not on your machines that execute the packages in a live environment

Perhaps this is obvious to people that know .Net intimately but it was a pleasant surprise to me.

-Jamie|||Just for future reference, a BOL link to the topic Doug mentions. It doesn't highlight the runtime vs design-time GAC requirement though.

Using the .NET Framework and Other Assemblies in the Script Component

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/c3913c15-66aa-4b61-89b5-68488fa5f0a4.htm

adding more to the complex query

my head a splode.
so i'm finalizing the ugly transformation query, and i think i'm almost
done. there's only two problems that i'm having trouble with. first,
some ddl simplified samples for what i'm working with (please assume
foreign keys and such by context where possible, i want to keep this
brief - and once again CELKO, no need to harp on artificial keys):
create table orders (
new_id uniqueidentifier primary key not null,
old_id int identity (1, 1) unique not null
)
-- make four orders
insert into orders (new_id) values (newid())
insert into orders (new_id) values (newid())
insert into orders (new_id) values (newid())
insert into orders (new_id) values (newid())
create table old_transactions (
transactionid int identity (1, 1) primary key not null,
orderid int not null,
transactiontype int not null,
amount money not null,
transactiondate datetime not null
)
-- make eight OLD transactions for those orders
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
1, 100, 10.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
1, 200, 10.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
2, 100, 10.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
2, 300, 5.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
3, 100, 10.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
3, 200, 15.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
4, 100, 5.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
4, 300, 0.00, getdate())
create table new_transactions (
transactionid uniqueidentifier primary key not null,
orderid uniqueidentifier not null,
productcode varchar(50) not null,
amount money not null,
transactiondate datetime not null
)
-- and here is the part i'm having trouble with
insert into new_transactions (
transactionid,
orderid,
productcode,
amount,
transactiondate)
select newid(), -- PROBLEM 1 (detailed below)
o.new_id,
case
when (select count(*) from old_transactions as ot2
where ot2.transactionid = ot1.transactionid and
ot2.transactiontype in (100, 200)
group by ot2.orderid) = 2
then 'PRODUCT 1'
when (select count(*) from old_transactions as ot2
where ot2.transactionid = ot1.transactionid and
ot2.transactiontype in (100, 300)
group by ot2.orderid) = 2
then 'PRODUCT 2'
end,
0.00, -- PROBLEM 2 (detailed below)
transactiondate
from old_transactions as ot1
join orders as o on ot1.orderid = o.old_id
PROBLEM 1: this statement inserts 1 row per transaction in the
old_transactions table. what i actually want is 1 row per distinct
orderid in the old_transactions table. as you might be able to tell
from the other logic in the statement, i'm collapsing all the
transactions in the old_transactions table for the same orderid into a
single new transaction in the new_transactions table.
PROBLEM 2: i need for the amount in the new_transactions table to be a
SUM aggregate of the amount in the old_transactions table for the
transactions of each orderid that i'm collapsing.
hopefully the ddl and the problem descriptions make sense. and thanks
in advance for any help!
jasonfirst, give the new_transactions.transactionid column a default of
newid(), then try
note: the transactiontype in (...) criteria assumes that there wouldn't
be duplicate transactiontypes per order
insert into new_transactions
(orderid, productcode, amount, transactiondate)
select o.new_id,
'PRODUCT 1',
sum(ot.amount),
min(ot.transactiondate)
from orders o
join old_transactions ot on o.old_id=ot.orderid
where ot.transactiontype in (100,200)
group by o.new_id
having count(*)=2
union all
select o.new_id,
'PRODUCT 2',
sum(ot.amount),
min(ot.transactiondate)
from orders o
join old_transactions ot on o.old_id=ot.orderid
where ot.transactiontype in (100,300)
group by o.new_id
having count(*)=2
jason wrote:

>my head a splode.
>so i'm finalizing the ugly transformation query, and i think i'm almost
>done. there's only two problems that i'm having trouble with. first,
>some ddl simplified samples for what i'm working with (please assume
>foreign keys and such by context where possible, i want to keep this
>brief - and once again CELKO, no need to harp on artificial keys):
>create table orders (
> new_id uniqueidentifier primary key not null,
> old_id int identity (1, 1) unique not null
> )
>-- make four orders
>insert into orders (new_id) values (newid())
>insert into orders (new_id) values (newid())
>insert into orders (new_id) values (newid())
>insert into orders (new_id) values (newid())
>create table old_transactions (
> transactionid int identity (1, 1) primary key not null,
> orderid int not null,
> transactiontype int not null,
> amount money not null,
> transactiondate datetime not null
> )
>-- make eight OLD transactions for those orders
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 1, 100, 10.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 1, 200, 10.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 2, 100, 10.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 2, 300, 5.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 3, 100, 10.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 3, 200, 15.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 4, 100, 5.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 4, 300, 0.00, getdate())
>create table new_transactions (
> transactionid uniqueidentifier primary key not null,
> orderid uniqueidentifier not null,
> productcode varchar(50) not null,
> amount money not null,
> transactiondate datetime not null
> )
>-- and here is the part i'm having trouble with
>insert into new_transactions (
> transactionid,
> orderid,
> productcode,
> amount,
> transactiondate)
>select newid(), -- PROBLEM 1 (detailed below)
> o.new_id,
> case
> when (select count(*) from old_transactions as ot2
> where ot2.transactionid = ot1.transactionid and
> ot2.transactiontype in (100, 200)
> group by ot2.orderid) = 2
> then 'PRODUCT 1'
> when (select count(*) from old_transactions as ot2
> where ot2.transactionid = ot1.transactionid and
> ot2.transactiontype in (100, 300)
> group by ot2.orderid) = 2
> then 'PRODUCT 2'
> end,
> 0.00, -- PROBLEM 2 (detailed below)
> transactiondate
>from old_transactions as ot1
>join orders as o on ot1.orderid = o.old_id
>
>PROBLEM 1: this statement inserts 1 row per transaction in the
>old_transactions table. what i actually want is 1 row per distinct
>orderid in the old_transactions table. as you might be able to tell
>from the other logic in the statement, i'm collapsing all the
>transactions in the old_transactions table for the same orderid into a
>single new transaction in the new_transactions table.
>PROBLEM 2: i need for the amount in the new_transactions table to be a
>SUM aggregate of the amount in the old_transactions table for the
>transactions of each orderid that i'm collapsing.
>hopefully the ddl and the problem descriptions make sense. and thanks
>in advance for any help!
>jason
>
>|||worked perfectly, and what's better, i understood it! thanks a lot,
this is exactly what i was needing.
jason

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