May 21, 2012 at 3:46 pm
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?
May 21, 2012 at 6:13 pm
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
May 22, 2012 at 12:25 pm
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!
May 25, 2012 at 8:27 am
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