SSIS - Can someone give me automation ideas? I made 3 packages that needs to run monthly,
-
Can someone give me automation ideas? I made 3 packages that needs to run monthly,
There are 3 packages that simply run sql SPs and copy the result set to an excel worksheet.
I know I can get this working in one package. But, my boss does not like it when I use time to try and figure things out.
So I just want to go with the 3 sperate packages they are getting the job done fine.
Now that all is working right, they need to run every month, so i will need your help on how i can set up variables
for the begin and end date so when someone goes to run the packages monthly, the are promted to imput the dates.
now the dates are hard coded in like spDelos_Location 'DS56125', '8/1/09', '8/31/09'
Maybe to enable you to help me better, what do you think of this plan.
1. go edit each package in SSIS and make the BeginDate and EndDate SSIS variables(*do not know how to do this yet)
Or better put like a function for the date parameters to get the previous months begin date and end date automatically.
2. copy the packages from my devel workstation to a centralized drive that gets backed up.
3. create a Job in the sql server 2005 to execute these 3 packages prompting me for the variables(or not if i create the dates with a function!),
and making it reconnect using the sa and the sa password as credentials (so there wont be any permission errors, i noticed today i had to go into the packages in SSIS edit mode and reconnect using login and password to the server since I made these packages last week
and i guess the sql server killed the sleeping connections)
Am I on the write track as a whole to automating the running of these 3 packages once per month?
thanks
-
You have some of the right stuff, but the first thing is that it will NOT be prompting you for anything, SSIS is a back-end process, it is not now nor has ever been in the business of asking for input at runtime. With that said you should build a query or two to populate those variables yourself at every run. For help I give you this:
http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
Thank you Lynn.
The way to do that is:
1. Get your query working
2. Add an EXEC SQL Task
3. Under General, ResultSet, set it to single row
4. Under ResultSet, Add two. For the result name you can either specify the index 0 for the first one or the fieldname and the variable to put that value in.
You should now have it setting the variables on its own each time..
And for the record, your boss should expect you to use time to try and figure things out. You don't know everything, none of us do.
CEWII
-
Thanks you very much, He, i can't believe it, said take some time to work on figuring this out. I am astonished.!
I am looking into to your suggestion.
-
Would love to hear back how it went.
CEWII
-
Well, I printed and followed your instructions and the steps are in sync., thanks!
I copied and pasted the dates from your link and tested them out and tweeked 1 of them to give me
'the end date of the previous month'. And I thought, I will alter and include these 2 date functions into the stored procedure that SSIS is running
and that will be that. But my Boss said we want it more flexible, we may run this package for the last 2 or 3 months sometimes,
needing a way to change the dates when we run the 3 packages in a job'. I guess we can just put them in a job and
change the dates before manually running the job, but still he and I still want to figure out how to make it work
so that SSIS can have 2 date variables (which we now have,thanx to your help) we can change for the package when we run it's sp with those 2 date parameters.
So I know you said we can't simply be promted or input the values for the variables at run time, they have to be part of the package like
using date functions as in your linked examples. But I to make that work becuase I can have those 3 fully automated packages in 1 job and make another job manually run for manually inputing the begin and end date values.
But now I am confused since now I have the same sql query which is a stored proc with the 2 date parms
in both the Control Flow Container (in the Execute SQL task) and the in the Data Flow tab in the (OLE DB source).
I am a bit confused, I know this will not work. Do I need to completely reconstruct the package in the designer or what
would you suggest? where does the sp need to be, in the control flow tab, data flow, does it need to be in both?
Not sure if these screen shots will show up.
Thanks Adam
-
Out of interest, how are you currently executing the packages? Via BIDS? Via SSMS? Commandline? DTSRunUI? If you opt for theUI or Cmdline options, you can easily set parameter values at that time, so while not prompting you, it definitely is a runtime setting of the values. If you're using the DTSRunUI type approach, there's two options - enter the full path to the variable in the UI and set it's value (erk, the path is not 'english' per se); or create a config file (XML document) and manually edit the values in this file before exeucting the packages (setting them to reference the file). IMHO, neither of these is super 'end user friendly' but again, they both 'work'.
If you're like many people, and are executing the package/s by clicking the little green play button in BIDS, then [please, stop!!, this is debug mode!!] you could simply change the parm values right there in the variables pane on screen (prior to clicking run).
Steve.
For what it's worth, you *can* get SSIS to prompt for data entry, but 1) you shouldn't 2) it's not overtly simple/easy and 3) you shouldn't (get the idea? ;-))
Steve.
-
I see that we have at least two seperate problems
1. Wider re-usability
2. 2 similar queries.
So I'll tackle #2 first
But now I am confused since now I have the same sql query which is a stored proc with the 2 date parms in both the Control Flow Container (in the Execute SQL task) and the in the Data Flow tab in the (OLE DB source).
I am a bit confused, I know this will not work. Do I need to completely reconstruct the package in the designer or what would you suggest? where does the sp need to be, in the control flow tab, data flow, does it need to be in both?
In your control flow you just need to get the dates, you aren't using them yet, I'm not sure why you would call your sproc twice. I re-read what you said, and I want to clarify something. If you have the logic in your sproc to calc the dates then the SSIS job should likely be passing NULLs for those parameters which would be the key to use the values calculated in the sproc. If how to do that is not clear respond back.
I hope that helped.
No on to problem #1
But my Boss said we want it more flexible, we may run this package for the last 2 or 3 months sometimes
As I said before there will be no prompting from it when it runs. It sounds like your boss is looking more for reporting than backend processing. What does this process do?
I was thinking of a solution to this, I might go down a little different path. I was thinking add a variable that says how many months you want to extract and then use that to calculate start and end dates in the control flow. The downside is that if you wanted 1 month, 2 month, and 3 month you would have three jobs that were effectively the same except the parameter passed in.
This option gives you the flexibility but this isn't how I would think SSIS would be used, I keep getting the reporting vibe from this discussion..
Did any of this make sense?
CEWII
-
stevefromOZ (9/4/2009)
For what it's worth, you *can* get SSIS to prompt for data entry, but 1) you shouldn't 2) it's not overtly simple/easy and 3) you shouldn't (get the idea? ;-))I think this is a partially true statement, only in debug mode when using a script task. In other words, not the way that 99.9% of us use SSIS. And I don't want people trying to do something as stupid as this, because for all practical reasons it isn't possible. If you do this from Integration Services Server, I can't be sure if the package will fail, or just hang waiting for input that you can't possibly give it. Like the three laws of busines (Location, Location, Location) this one is Don't, Don't, Don't..
CEWII
-
Elliot 9/4/2009
I think this is a partially true statement, only in debug mode when using a script taskAbsolutely true!
Elliot 9/4/2009
not the way that 99.9% of us use SSISNot so sure on that statistic. True of people (such as yourself) who know the architecture of the product and how it *should* be used. Not been my experience when talking to others using SSIS but YMMV.
Elliot 9/4/2009
I don't want people trying to do something as stupid as this100% behind you on this point (hence not posting how to actually do it)
I'm also with you, the vibe here is not of an automated extraction process as much as a requirement for a parameterized report.
Steve.
-
Ok, to clear things up, it is looking and sounding more like a paramterized report to me too after he had me name the to parameters @dtReportingBegin and @dtReportingEnd. But, what we are exactly doing is : we have created 3 seperate stored procedures to extract data from a sql server table and put each the result sets into 3 .xls files. We have done this because we then upload these 3 .xls files into a 3rd party system
which is incredible particular with column width, data types, business rules I.E. the actual value of the values we are uploading to them. So it is a report ,but not going for print, going to an upload to another system process.
I actually concluded i would like to make 2 jobs form these packages , one that automatically uses the time functions to get the previuos months begin and end date and run it the 5th of every month, and one that we need to change the dates in.
Currently right now I am just going to what I call 'dicking around' in SSIS to learn it more, becuase then your all's advice will make sense when i just understand SSIS better. I went through the first 5 chapters of microsoft press's SSIS . Now its time to just let myself loose with what i know at see what obvious things i was overlooking. I took a couple hours friday at work to go through the chapter on control flow which included assinging values to variables using the script task using the variable dispenser. Now i just what to Dick around in there and learn what i can and then take another stab at this, beuase i understand what you all are saying, just don't know the Bids SSIS template well enough to know just where to click and should the query going in the control flow or data task since everything and you all seem to point to the control flow > execute sql task when needed to use vaiables.
-
you all seem to point to the control flow > execute sql task when needed to use vaiables.
You don't really need to use a dataflow to get data into variables, the control flow works just great. If I can I try to capture all the variables I can in a single query so I don't have a bunch of control-flow tasks assigning variables.
There is a lot to SSIS and it is a VERY different beast from DTS, I really like it. If you have additional questions please do ask in the forum or contact me directly.
CEWII
-
Elliot 9/4/2009
not the way that 99.9% of us use SSISNot so sure on that statistic. True of people (such as yourself) who know the architecture of the product and how it *should* be used. Not been my experience when talking to others using SSIS but YMMV.
Ok, I can live with that. There are a bunch of things you can do if you are always going to run the package manually, however, I really don't think most of us plan to do that outside of debugging or developing the package.. Just my experience..
CEWII
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply