Dynamic Dataflow and mapping in SSIS

  • Is there a way to make a dynamic dataflow object, with dynamic mapping at the same time?

    I'm currently using a SQL statement to handle input variables in a SSIS package from a table that lists tables that i want to load.

    currently am using a for loop to go thru the loads, however for each table i have to define the dataflow item (with mapping) and using the package flow with expressions to coax the correct pathways to the data flows needed for a specific table.

    if i'm loading 3-4 tables in a database this isn't an issue... if i'm loading 100.... it's much more of a pain.

    I've yet to find a reasonable way around this.

    I see 3 possibilities:

    a) I could create a bunch of data flow ssis packages, and keep them in their own directory (as a sorting mechanism for me) and then call the appropriate SSIS package,

    b) keep doing what I am doing and have a spaghetti of connectors when I have a lot of tables to load.

    or (What I REALLY want)

    c) a way to simply create this data flow and the requisite parts via a script flow.

    I've done some searching, seems to come back as non-doable for C, as a limitation that came with SSIS where when it was DTS you could most definitely do this.

    Anyone find a work around, or is the "it's set at runtime and doesn't change", the final word?

  • Use SQL task Expressions to run your sql statement within foreach loop.

    Eg:

    Insert into "DestServer."+"DestDBName."+@User::EachTableName values select * from "SrcServer."+"SrcDBName."+@User::EachTableName

  • that looks a bit too easy.

    so as long as my connections are fine and i parameterize the to server/from server portion the SQL should work.

    If this is the case why would a dataflow object be utilized instead of this? and what am i losing doing it this way that would be included in the mapping portion.

    Thanks!

  • How is the need for dual connections strings, both to data originating server versus where the data is going, handled?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply