Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supportedsql

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

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 SCRIPT and LOOK UP components using C#

Hi,

Could you please tell me how to add SCRIPT and LOOK UP component to a data flow using C#? Also, is there any artilces/site which gives information about the SSIS programming?

Regards,

Gopi

Gopinath M wrote:

Also, is there any artilces/site which gives information about the SSIS programming?

from the bol: http://msdn2.microsoft.com/en-us/library/ms136025(sql.90).aspx

|||

Douglas,

The bol link gives information about the basic components and tasks. I'm looking for more information related to the components like LOOK UP transerformation, SCRIPT componenet, Conditional Branching component. Could you please let me if you know where we get reference material ?

Regards,

Gopi

|||

Adding components programatically is pretty much the same for any component - you just need to be aware of specific properties for each, and they are described in BOL.

The exception is the script component which relies on the VSA project infrastructure - we are looking to improve this capability in the future. Having said that, if you can programatically create a package, you certainly have the skills needed to write a custom component rather than using a script component - custom components are more flexible and easier to reuse, especially programatically.

Donald

Adding Row Numbers to Flat File Source

Hi,

I was wondering if it was possible to add an identity column to a flat file data source as it is being processed in a data flow. I need to know the record number of each row in the file. Can this be done with the derived column task or is it possible to return the value of row count on each row of the data?

Any help on this is greatly recieved.

Cheers,

Grant

Generating Surrogate Keys
(http://www.sqlis.com/default.aspx?37)

-Jamie

|||

You may have more than one option here; I would try with a script task in control flow that uses a variable to generate and adds it as a column to the pipeline.

Create a SSIS variable Int32. eg MyIdentityColSeed that is set to zero.

Then, in your data flow, after the flat file source create a script task with 1 output column (named for example MyIdentityCol) and use and script like this:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.MyIdentityCol = Variables.MyIdentityColSeed + Counter

Counter += 1

End Sub

Even when the code is very simple; I did not tested it, so take the time to debug it.

|||Rafael, Jamie,

Thanks for both your posts on this matter. I'm looking into Rafael's suggestion at present and see that the only way to access variables in the data flow section is within the PostExecute phase. That problem with this is that it doesn't seem to update the variable as i move through the rows in the dataflow. Is this because of how SSIS has been developped. Can you not set a variable anywhere else within the script. I'll look at Jamie's suggestion once this one has been exhausted.

Thanks,

Grant|||

The Pre and Post execute limitation is just that. It also makes more sense as you only need to read and write the variables pre/post, which is faster than accessing them for every row. Use local variables in the actual main execution code.

You may want to take a quick look at the Row Number Transformation, it may do exactly what you want with seed and increment settings already- http://www.sqlis.com/default.aspx?93

|||Got Rafaels suggestion to work. Didn't need to use a variable in the end just a counter that was initialized on the preexecute event which was incremented for each row. Many thanks for everyone's help.

Yet more new things learned today :) .

Thank you all.

Grant|||

Grant Swan wrote:

Rafael, Jamie,

Thanks for both your posts on this matter. I'm looking into Rafael's suggestion at present and see that the only way to access variables in the data flow section is within the PostExecute phase. That problem with this is that it doesn't seem to update the variable as i move through the rows in the dataflow. Is this because of how SSIS has been developped. Can you not set a variable anywhere else within the script. I'll look at Jamie's suggestion once this one has been exhausted.

Thanks,

Grant

Errr...both Rafael's and my suggestions were exactly the same

Glad you got it working anyhow!

-Jamie

|||

Grant,

I think Jamie is right; the approaches are identical. I do not keep records of web resources I used; so I was unable, like Jamie, to post a link to the original sorce. I guess a owe some credits to author of the script

Rafael Salas

|||Jamie,

Sorry, I'd had a previous link open from the same website and was looking at this which had some resemblence to what i wanted to do:

http://www.sqlis.com/default.aspx?93

As apposed to the link i'd clicked from your post. Apologies and i'll mark your post as an answer also.

Its been a bad day and i've got so many SSIS things in my head :)

Cheers,

Grant|||

Grant Swan wrote:

Jamie,

Sorry, I'd had a previous link open from the same website and was looking at this which had some resemblence to what i wanted to do:

http://www.sqlis.com/default.aspx?93

As apposed to the link i'd clicked from your post. Apologies and i'll mark your post as an answer also.

Its been a bad day and i've got so many SSIS things in my head :)

Cheers,

Grant

Ha ha. Cool. I'm not bothered about getting my "answer count" up, as long as the thread has at least one reply marked as the answer. thanks anyway tho!

-Jamie

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 expression in Data Flow Task

Hi

I am trying to develop a data flow task in C#. I need to add an expression tab in the task where i can write expressions on the input columns and map it to outputs. Please let me know how to go about it. I am new to SSIS coding so dnt have much idea abt it.

Thanks in advance,

Vipul

Try using the Derived Column tool.|||

Hi TGnat

I am looking for some apis which will help me to write a code. I am not sure abt Derive Column Tool as there are some of the things which are not supported in it and i am looking to enhance the functionality for my use.

Thanks

Vipul

|||

Can you give an example of what you are trying to do.

When I first started out I thought I would need a script task or a custom control for everything... Now I find that the stock components can meet most of my needs!

|||

I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.

Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.

Thanks

Vipul

|||

Vipul123 wrote:

I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.

Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.

Thanks

Vipul

"Stock components" simply means the stuff that is already in the toolbox.

If you want to build something that does the same as a LOOKUP and a DERIVED COLUMN, why not just use the LOOKUP and the DERIVED COLUMN?

-Jamie

|||

Also... Your lookup can return multiple columns, one or more for the look up match, and then you can select other columns to be added to your data flow. Follow this up with a derived column that implements your logic and you may have the functionality you need.

|||

Jamie:

Thanks for the reply. I have few more queries. In the OLEDB Command, there is a tab called "Column Mapping". In here we can map the input columns to the output columns.

I am writing this code in the function :

public override void ProvideComponentProperties()

{

ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();

ComponentMetaData.Name = "Test";

ComponentMetaData.Description = "Test";

ComponentMetaData.ContactInfo = "Test";

IDTSInput90 input = ComponentMetaData.InputCollection.New();

input.Name = "Input";

input.Description = "Input Desc";

input.ExternalMetadataColumnCollection.IsUsed = true;

IDTSRuntimeConnection90 rtc = ComponentMetaData.RuntimeConnectionCollection.New();

rtc.Name = "Source";

rtc.Description = "Source Desc";

IDTSOutput90 output = ComponentMetaData.OutputCollection.New();

output.Name = "Output";

output.Description = "Output desc";

output.ExternalMetadataColumnCollection.IsUsed = true;

}

I am not able to connect(link) the input to the output ports. I am able to add new output columns in "Input and Output Properties" tab. The output ports which i am adding in this tab are not visible in "Column Mapping" tab. Please let me know if i am missing something in the component properties which will enable the linking of the input to output ports.

Thanks,

Vipul

|||

TGnat,

Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.

Thanks

Vipul

|||

Vipul123 wrote:

TGnat,

Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.

Thanks

Vipul

So you want to be able to reuse functionality? Currently the only way to do this is to write your own component (very difficult) or good old copy-and-paste (very easy). I know which I would choose.

Incidentally, there is functionality probably coming in Vnext that would allow you to do exactly what you want to do (i.e. build reusable collections of components) without writing code.

-Jamie