June 23, 2016 at 6:33 pm
I have a package of 3 files which loads to destination, i want to set them to only run if there is data in all three tables for the current month (the ones we use for the three files
made sure loaded). Also, only run if it hasn't run before for this month or if a parameter is passed in to overload whether to run.
can anyone help me out....
June 24, 2016 at 1:15 am
kalyaniraj27 (6/23/2016)
I have a package of 3 files which loads to destination, i want to set them to only run if there is data in all three tables for the current month (the ones we use for the three filesmade sure loaded). Also, only run if it hasn't run before for this month or if a parameter is passed in to overload whether to run.
can anyone help me out....
There's a few ways of doing this but since this is an SSIS forum then let's encapsulate it in there. You'll need 4 variables; 4 booleans - 1 for each table and an override. Set up 3 sql tasks with simple counts for each table based on a where clause for the current month and using a SELECT COUNT(*) FROM (SELECT TOP 1 whatever FROM wherever WHERE month = whatever) pattern - this will either produce 1 or 0 which SSIS will read as true or false in the boolean. Output the result to the relevant variable and then have each of the precedent constraints coming from these tasks set to Expression and Constraint. In the expression it will be something like @TableVariable == True || @Override == True.
June 24, 2016 at 2:12 pm
Yes, use precedence constraints to handle the first part of your requirements. As far as the second part, whether or not your package has run this month, you'll have to come up with a way to track package executions. You could query the SSISDB internal operations table to find the last run. Another option is to track your package executions in your own tables. I typically create a set of table to track my package stats for run times, rows staged, errors, etc. This also gives you a way to query to determine if you've run this month already. Combine the result from this query with your override parameter and you can create a solve for this requirement.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply