Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access tasks in Data Flow Task dynamically


Access tasks in Data Flow Task dynamically

Author
Message
revathiganesh55
revathiganesh55
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 189
Hi friends,

I have a package where the number of columns in DB tables could be changed. I want to change the meta data of data flow task dynamically. Also the mapping details will be in a seperate table as the source and destination column names need not be same.

I am able to show the newly added columns in the package but I am not able to map the columns in Data Flow Task.
Could anyone please help me on the same.

Thanks and Regards,
Reva
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16518 Visits: 13210
Out of the box, SSIS doesn't support changing metadata.
There are some 3rd party products, such as Dataflow+ of CozyRoc that can do this.

If you need to stay with native SSIS, then you'll need to resort to scripting.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
revathiganesh55
revathiganesh55
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 189
Thanks for the reply.
But can't we do the column mapping in C# and use Pipeline dlls?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16518 Visits: 13210
That's a bit out of my comfort zone.
I know that some people regenerate the package dynamically every time, so that when it runs the dataflow is mapped as it should be.

But modify an existing package on the fly? I don't know.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
revathiganesh55
revathiganesh55
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 189
I am able to add the newly added columns in my package via C# but am not able to map the columns using my reference table in DB. Also here my Source and Destination column names can be different.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16518 Visits: 13210
I know you can change the source and destination tables dynamically, but I have no clue how you would handle the changing metadata.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
Koen Verbeeck (11/16/2012)
I know you can change the source and destination tables dynamically, but I have no clue how you would handle the changing metadata.


I am able to modify table names dynamically in data flow tasks. But, I need to change destination column names dynamically. How can we do that?

Thanks
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
There are things you can change easily in an SSIS package dynamically. The easy way to tell if you can is if they're in the list of properties under expressions for the source/destination or the datasource itself.

There are things you CAN'T change easily in an SSIS package dynamically, one of which is the metadata of the dataflow. The dataflow uses internal IDs based on the object that 'sources' the column to determine its type, value, and other items. There are third party objects out there that can allow you to have some variable source constructions, but even they are limited to having the same output results into the metadata (at least for used columns).

SSIS isn't meant to be that dynamic. In the same way that you need to write dynamic T-SQL to allow for major changes to queries, you need to write dynamic packages to account for variable metadata. This is nowhere near as easy as dynamic SQL however, and will require a pretty solid background in coding.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
Evil Kraig F (3/4/2013)
There are things you can change easily in an SSIS package dynamically. The easy way to tell if you can is if they're in the list of properties under expressions for the source/destination or the datasource itself.

There are things you CAN'T change easily in an SSIS package dynamically, one of which is the metadata of the dataflow. The dataflow uses internal IDs based on the object that 'sources' the column to determine its type, value, and other items. There are third party objects out there that can allow you to have some variable source constructions, but even they are limited to having the same output results into the metadata (at least for used columns).

SSIS isn't meant to be that dynamic. In the same way that you need to write dynamic T-SQL to allow for major changes to queries, you need to write dynamic packages to account for variable metadata. This is nowhere near as easy as dynamic SQL however, and will require a pretty solid background in coding.

I am able to update column names.

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search