May 23, 2022 at 4:17 pm
Hi,
I have a query like "Select tablenumber from a table"
Tablenumber
1
2
3
4
5
So, if i get values 1,2,3
1 executes Packge ----pacakge1
2 : execute Packge ---pacakge2
3 : Execute Packge ---Pacakge3 etc..
my solution is not working :
Execute SQL task : Calling the query . i get 5 values example:1,2,3,4,5 and passing that value into variable
In precedence constraints : i check the value from variable and based on value , i call execute package. But now, only "execute package" is being called only one time.
how to make it work to run all the packages as my query has 1,2,3,4,5 values returned.
Thanks.
May 23, 2022 at 7:16 pm
Are you trying to execute multiple packages sequentially from a single execute package task?
If so then I think your execute package task needs to be in a for each loop configured to use an object variable loaded with a column of table numbers by the initial sql task. There are tutorials on the net on how to use an object variable with a for each loop. I do it so rarely that I usually have to look it up.
If you want the packages to run in parallel then I think you need an execute package task for each package and a precedence constraint for each execute package task. The sql task that returns 1,2,3 would need to return a separate column for each table with a 1 or a 0, so it can populate a variable for each precedence constraint. The syntax for precedence constraint for package 4 would look something like this. @[User::Package4]==1, which will evaluate to True or False.
If you have too many packages to set up a task for each package, I think you have to start writing BIML, which I don't know how to do.
May 24, 2022 at 2:51 pm
Yes, I agree that a Foreach loop (FEL) with an object variable sounds like the way to go.
Is there really a direct mapping between the package names and the table numbers? 1 to 'Package1', 2 to 'Package2' etc? That mapping needs to be in place so that you can use an expression to set the 'current' package name inside the FEL.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 24, 2022 at 8:23 pm
Alternatively you can define five (or the number of packages you have) separate Data Flows, where your input will then be slightly modified queries:
"Select tablenumber from a table where tablenumber=1", "Select tablenumber from a table where tablenumber=2" etc.
The output goes into "garbarge bins" in the form of Recordset Destination variables.
If a row was selected into the "garbage bin" can then be checked by adding a RowCount control between the source and destination.
The row count can then be checked in the control flow following the Dataflows and based on that the packages can be executed (or not executed).
This should allow the packages to be executed in parallel (I think 🙂 )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply