SQLServerCentral.com / Business Intelligence / SQL Server 2005 / Access tasks in Data Flow Task dynamically / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 12:24:55 GMT20RE: Access tasks in Data Flow Task dynamicallyhttp://www.sqlservercentral.com/Forums/Topic1385464-147-1.aspx[quote][b]Evil Kraig F (3/4/2013)[/b][hr]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.[/quote]I am able to update column names.Wed, 06 Mar 2013 11:52:18 GMTHardy21RE: Access tasks in Data Flow Task dynamicallyhttp://www.sqlservercentral.com/Forums/Topic1385464-147-1.aspxThere 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.Mon, 04 Mar 2013 12:55:09 GMTEvil Kraig FRE: Access tasks in Data Flow Task dynamicallyhttp://www.sqlservercentral.com/Forums/Topic1385464-147-1.aspx[quote][b]Koen Verbeeck (11/16/2012)[/b][hr]I know you can change the source and destination tables dynamically, but I have no clue how you would handle the changing metadata.[/quote]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?Mon, 04 Mar 2013 12:40:08 GMTHardy21RE: Access tasks in Data Flow Task dynamicallyhttp://www.sqlservercentral.com/Forums/Topic1385464-147-1.aspxI know you can change the source and destination tables dynamically, but I have no clue how you would handle the changing metadata.Fri, 16 Nov 2012 01:34:27 GMTKoen VerbeeckRE: Access tasks in Data Flow Task dynamicallyhttp://www.sqlservercentral.com/Forums/Topic1385464-147-1.aspxI 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.Fri, 16 Nov 2012 01:32:45 GMTrevathiganesh55RE: Access tasks in Data Flow Task dynamicallyhttp://www.sqlservercentral.com/Forums/Topic1385464-147-1.aspxThat'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.Fri, 16 Nov 2012 01:28:33 GMTKoen VerbeeckRE: Access tasks in Data Flow Task dynamicallyhttp://www.sqlservercentral.com/Forums/Topic1385464-147-1.aspxThanks for the reply. But can't we do the column mapping in C# and use Pipeline dlls?Fri, 16 Nov 2012 01:25:15 GMTrevathiganesh55RE: Access tasks in Data Flow Task dynamicallyhttp://www.sqlservercentral.com/Forums/Topic1385464-147-1.aspxOut 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.Fri, 16 Nov 2012 00:22:04 GMTKoen VerbeeckAccess tasks in Data Flow Task dynamicallyhttp://www.sqlservercentral.com/Forums/Topic1385464-147-1.aspxHi 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.[b]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.[/b]Could anyone please help me on the same.Thanks and Regards,RevaThu, 15 Nov 2012 22:10:08 GMTrevathiganesh55