June 29, 2009 at 3:17 pm
I am wondering if there is a better way to perform a task in my package. I have a series of 25 tables that I am loading. I have an Insert Audit ID control flow item for each table. It does the same thing for each, creates an audit id and returns the id to the package. I also have an Update Audit Control flow item for each table that updates the run times and row counts for each table. I am wondering if I need to have 25 of these control flows tasks or whether I could combine them into one.
June 30, 2009 at 3:54 am
Hi
You could do this with a foreach loop container, with the sql task source type set to a variable and an initial sql task creating dynamic SQL code into an object variable.
Problems I see may be making the return values dynamic, however I'm sure this can be done, just never done it myself. How are you returning the ID to the package? Are you returning the ID into seperate variables, or just one? Where are you getting the audit ID from, are you generating it in the package? etc etc etc.
Laurence
June 30, 2009 at 10:34 pm
I am returning the audit id to the package using a Stored Procedure. I use just one user varialbe to store the id.
July 1, 2009 at 2:55 am
Well, that makes the problem much simpler. You could have a SQL task pull a list of tables into a recordset, use a script, or sql task to generate the sql code into a variable (something like INSERT INTO @TableName ....), then another sql task to run the code deposited in the variable.
Give me a shout if you need more detail.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply