November 18, 2011 at 7:02 am
I have got 10 stored procedures. Each procedure is in an ‘Execute SQL task’
The procedures work off of a parameter AC_YEAR. At the moment I only do the current AC_YEAR so in parameters its set as
Name - AC_YEAR
Data Type- String
Value - AC 11-12
But what I really want to do is run it again starting from 06-07 right up to 11-12
But I have no idea how to set this up.
All I can come up with is copying and pasting the package 5 times and the parameter is different for each one. Then have the top level go run the first package then the next then the next.
Seems a bit silly. Is anyone aware of a better way of doing it?
BTW unfortunately I cant do the whole thing in one go. This has been an ongoing issue (Argument) with the dba. The best I can hope for it to keep it the same (Saving lots of time) and running again and again for each year.
Any help would be greatly appreciated
Ta
Debbie
November 18, 2011 at 7:58 am
Ive had a thought but I dont know if its doable
A top level package which runs the package with the SPs in
Set the parameters up at this level
Delete the parameters from the SP level so it can run over and over again from the top level
E.g. Top Level AC_YEAR AC 06-07
Sequence container for 06-07 data
name AC_YEAR
Value AC 06-07
The next one:
Top Level AC_YEAR AC 07-08
Sequence container for 07-08 data
name AC_YEAR
Value AC 07-08
Etc
Is this workable?
November 18, 2011 at 8:40 am
Debbie Edwards (11/18/2011)
Ive had a thought but I dont know if its doableA top level package which runs the package with the SPs in
Set the parameters up at this level
Delete the parameters from the SP level so it can run over and over again from the top level
E.g. Top Level AC_YEAR AC 06-07
Sequence container for 06-07 data
name AC_YEAR
Value AC 06-07
The next one:
Top Level AC_YEAR AC 07-08
Sequence container for 07-08 data
name AC_YEAR
Value AC 07-08
Etc
Is this workable?
There are a lot of ways to do this, but I guess the question is whether this is a once off or whether this will continue in future?
The easiest way imho is to schedule the package through SQL Agent, and setting the package variable (overriding it effectively) in the job. Create a separate step to execute the (same) package for each year you want, but with a different parameter.
Just make sure that the package itself will not override the parameter you've passed through the job.
I can give some other alternatives, but they will require more coding on your part...
Hope this helps.
Martin.
November 18, 2011 at 8:53 am
I had thought about that but couldnt find where to do it in the job.
So is the above not possible? Setting the parameters at the top level for each lower level package within a container. Thus the same package in each container runs the correct year?
Ta
Debbie
November 18, 2011 at 8:57 am
Debbie
Create a table in your database that holds the years you are going to use as parameters. In your package, select from this table into an Object variable. Then loop through that variable's result set, passing a different parameter each time to your Execute SQL task.
John
November 18, 2011 at 9:03 am
Debbie Edwards (11/18/2011)
I had thought about that but couldnt find where to do it in the job.So is the above not possible? Setting the parameters at the top level for each lower level package within a container. Thus the same package in each container runs the correct year?
Ta
Debbie
It is possible, but a little different to what you envision. Child packages can be set up (through the configuration settings) to receive parameter values from a parent package. But that means you will have to set a variable in the parent package before each execution of the child package.
See the attached...you set package variables through the "Set Values" tab in the job step. You could use that.
John's suggestion is another method, and more sustainable if you want to be able to do this with any dates in future without changing packages and/or job steps. Would be a good learning curve for you to do it this way.
I just wanted to give you "easier" alternatives for a start in case this is just a once off.
November 18, 2011 at 9:08 am
I like the sound of that.
I already have a SELECT DISTINCT AC_YEAR FROM dim.Date_Time
Ill have a good old think. That sounds good!
November 18, 2011 at 9:10 am
Create a table in your database that holds the years you are going to use as parameters. In your package, select from this table into an Object variable. Then loop through that variable's result set, passing a different parameter each time to your Execute SQL task.
John
I like the sound of that.
I already have a SELECT DISTINCT AC_YEAR FROM dim.Date_Time
Ill have a good old think. That sounds good!
November 18, 2011 at 9:14 am
It is possible, but a little different to what you envision. Child packages can be set up (through the configuration settings) to receive parameter values from a parent package. But that means you will have to set a variable in the parent package before each execution of the child package.
See the attached...you set package variables through the "Set Values" tab in the job step. You could use that.
John's suggestion is another method, and more sustainable if you want to be able to do this with any dates in future without changing packages and/or job steps. Would be a good learning curve for you to do it this way.
I just wanted to give you "easier" alternatives for a start in case this is just a once off.
[/quote]
Ahhh that makes more sence. Thanks for the screen shot of how to set within the jobs. I couldnt find it for looking.
I will do a few tasts of the different options to see which ones (Im capable) of doing
Thanks so much for your help on this 🙂
November 21, 2011 at 7:36 am
John Mitchell-245523 (11/18/2011)
DebbieCreate a table in your database that holds the years you are going to use as parameters. In your package, select from this table into an Object variable. Then loop through that variable's result set, passing a different parameter each time to your Execute SQL task.
John
Hey again,
Im googling to see if I can find any websites that should you how to do this but Im nopt having an joy at the moment.I dont suppose you know of any good how to articles on this do you?
November 21, 2011 at 8:10 am
Have a look at the following, and see if it explains it well enough...otherwise I can have another look: http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html
November 21, 2011 at 8:12 am
November 21, 2011 at 8:23 am
Its kind of there but it doesnt use a table to populate the variables. it seems to be focused on the Filename variable again, It seems to be missing the bit whenre you create the object that contains the file name from the database. Ill have a look at the 2nd link.
Its suprising how little information there is out there for this specific issue
November 21, 2011 at 8:32 am
Debbie Edwards (11/21/2011)
Its kind of there but it doesnt use a table to populate the variables. it seems to be focused on the Filename variable again, It seems to be missing the bit whenre you create the object that contains the file name from the database. Ill have a look at the 2nd link.Its suprising how little information there is out there for this specific issue
Which part are you struggling with?
You will need:
1. An execute SQL task, with the query to select all the data you need. The results of this query needs to be saved in a local variable (of type object).
2. The Foreach loop container allows you to select a dataset (from the variable in this case) as input.
All the other tasks you want to perform for each iteration will be in the container....and you may also want to set other variables for the specific record you are on.
Please see the attached screenshots...hope it makes sense.
Martin.
November 21, 2011 at 8:51 am
Martin Schoombee (11/21/2011)
Debbie Edwards (11/21/2011)
Which part are you struggling with?
You will need:
1. An execute SQL task, with the query to select all the data you need. The results of this query needs to be saved in a local variable (of type object).
2. The Foreach loop container allows you to select a dataset (from the variable in this case) as input.
All the other tasks you want to perform for each iteration will be in the container....and you may also want to set other variables for the specific record you are on.
Please see the attached screenshots...hope it makes sense.
Martin.
Ive got to your second screen shot but you can only map to one variable (Ado object source variable) and I have 3 variables. I cant see how this can be done. Have I scupepred myself again because of the 3 variables?
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply