June 21, 2010 at 10:00 am
Last week I setup a new SSIS package script that handles my Index Maintenance during off hours for my biog tables; the ones with millions of rows. The way I have it all setup is that there is a SQL Job for each tables Indexes and the SSIS package script contains several EXECUTE SQL AGENT JOB tasks and each calls one of the Big Table Index Rebuilds.
I'm not an SSIS guru and as much as I've tried repeatedly to embrace SSIS I find that it does not want to embrace anyone else (but thats complaining for another forum). Apparently the folks who developed SSIS or at least the EXECUTE SQL AGENT JOB task did not realize that the term asynchornous means one after the other an not all together at the same time. Its either that or they did not understand that "UPON COMPLETION" actually means when the process has completed and not simply started. And so because of this when SSIS I executes this kind of task it doesn't actually wait until the Job has completed to perform the next task as designed in the package. This means that all of the tables begin their Index Rebuilds at the same fragging time.
I'm soliciting help in the SSIS forum to see if their is any way to make SSIS realize the error of its ways but I don;t hold out much hope based on past experiences dealing with SSIS. Therefore I'm looking for any suggestions, ideas, or tips on how to best do this; to have some process execute SQL Server Jobs in an asynchronous manner. The solution doesn't;t even have to be able to deal with Failure verse completion. I just need for the next Job in the flow to not start until the prior Job has completed regardless of whether the prior Job fails or suceeds.
Any ideas are greatly appreciated.
BTW - I know I could place all of these Index Rebuilds in a single Job and that would allow me to make the process asynchronous but I'm trying to compartmentalize or make module the Index Maintenace by wrapping up each Tables Index Maintenance in it's own SQL Job. By setting it up like this I don't need to put the code to Rebuild a tables INdexes in more then one place.
Thanks
Kindest Regards,
Just say No to Facebook!June 21, 2010 at 11:07 am
Asynchronous actually means "independently of the main program flow". This is the exact opposite of what you think it means. Synchronous events are events that happen one after another.
Unfortunately, calling a SQL Server Agent Job is typically asynchronous. Except for querying the job event tables (which I've never had any success with), there's not really any way to know when a job is complete. When you call a job from pretty much anywhere, it will start the job and not wait for it to complete before returning. That's just how jobs work. Again, you could attempt to query the job event tables and loop until its done, but this is not something I can help with.
On the other hand, I'm not sure I see the point of calling a job from an SSIS process, especially if you need synchronous execution. There almost has to be another way to go about whatever it is you are attempting to do. I'd do some brainstorming and then seriously consider an alternate approach.
--J
June 21, 2010 at 12:58 pm
jvanderberg (6/21/2010)
Asynchronous actually means "independently of the main program flow". This is the exact opposite of what you think it means. Synchronous events are events that happen one after another.Unfortunately, calling a SQL Server Agent Job is typically asynchronous. Except for querying the job event tables (which I've never had any success with), there's not really any way to know when a job is complete. When you call a job from pretty much anywhere, it will start the job and not wait for it to complete before returning. That's just how jobs work. Again, you could attempt to query the job event tables and loop until its done, but this is not something I can help with.
On the other hand, I'm not sure I see the point of calling a job from an SSIS process, especially if you need synchronous execution. There almost has to be another way to go about whatever it is you are attempting to do. I'd do some brainstorming and then seriously consider an alternate approach.
--J
jvanderberg
I too orginally thought there had to be a way to do this and still keep all my stuff mdoular but darned if I saw a way. I do need to add that it must be SQL 2005 compliant as that is the version of the SQL Server.
As for the aynchronous vs synchronous, I admit I am no guru on definitions but I double checked on this (assuming I switched them up based on your reply) and if I am wrong about these 2 definiotns, with regards to SQL Server Jobs and how they run then I'm not the only one. I came across one developer who wrote his own custom version of the SSIS Task that executes SQL Jobs because he too wanted a way for the Task to wait untilk the SQL Job actually completed before going to th enext task; a way to make the process work synchronously since the default is to run asynchronously meaning STEP X does not need to actuallyc omplete before STEP X + 1 begins its execution.
Thanks for the reply and anything you come up with is most appreciated.
Kindest Regards,
Just say No to Facebook!June 21, 2010 at 1:06 pm
Well, you could add the appropriate maintenance plan task to the SSIS package directly. That's pretty modular since it only takes a few parameters, and does all the heavy lifting for you. There's not much (any) duplicate code if you need to call this multiple times from multiple packages.
If you need more power that the maintenance plan task can offer, you'd probably have to script it out. If you need modularity, you're best bet would probably be to wrap the script into a stored procedure.
Either of these options would get around calling a SQL Server Agent job, and would provide sychnonous execution.
--J
June 21, 2010 at 3:40 pm
Boy, perhaps I am missing the problem here but.....
Why can't you run each of the individual index rebuild one at a time to determine the approximate length of runtime for any given rebuild. Once you have the individual run times, why can't you just create separate standalone SSIS index rebuild jobs that are SCHEDULED to run 'X' number of minutes apart, which happens to correspond with how long it takes a given index rebuild to complete and have this series of rebuilds run every night, or two times a week, or whenever....
So if you have 5 index rebuilds (or 50), you find out the index 1 takes 55 minutes, index 2 takes 12 minutes, index 3 takes 2hr, etc....you can set up job for index 1 that starts at 7:00pm, the job for index 2 will be scheduled to start at 8:00 pm ( a bit of over lap for any delays in processing), job for index 3 would starts at 8:20 pm, job for index 4 would be scheduled for 10:30 pm, etc.
Would this work so that each rebuild has the time to finish?...or are there so many rebuilds that you have to do that your rebuild window is not very large? Schedule some rebuilds on Mon, some on Tues, some on Wed, etc.
June 21, 2010 at 3:46 pm
nelsonj-902869 (6/21/2010)
Boy, perhaps I am missing the problem here but.....Why can't you run each of the individual index rebuild one at a time to determine the approximate length of runtime for any given rebuild. Once you have the individual run times, why can't you just create separate standalone SSIS index rebuild jobs that are SCHEDULED to run 'X' number of minutes apart, which happens to correspond with how long it takes a given index rebuild to complete and have this series of rebuilds run every night, or two times a week, or whenever....
So if you have 5 index rebuilds (or 50), you find out the index 1 takes 55 minutes, index 2 takes 12 minutes, index 3 takes 2hr, etc....you can set up job for index 1 that starts at 7:00pm, the job for index 2 will be scheduled to start at 8:00 pm ( a bit of over lap for any delays in processing), job for index 3 would starts at 8:20 pm, job for index 4 would be scheduled for 10:30 pm, etc.
Would this work so that each rebuild has the time to finish?...or are there so many rebuilds that you have to do that your rebuild window is not very large? Schedule some rebuilds on Mon, some on Tues, some on Wed, etc.
Currently your idea would work but its not scalable or flexable to changes/additions whish is are going to happen. I'm only using a few tables right now but the end goal is to have in place a system where each night the process scans a table that stores Defrag info and it Defrags the next Index on the list based on a wieghted value that comes from a combination of
Fragmentation percentage + Table Size + Use
This way the system rebuilds index during off hours and it gives priority to those Indexes whcih need it the most.
Right now I'm doing it all on a schedule and or manually but there's no reasom most if not all can't be automated with current technology. It amazes me the limitations I hit anytime I look at SSIS.
Thanks for chiming in.
Kindest Regards,
Just say No to Facebook!June 21, 2010 at 3:51 pm
jvanderberg (6/21/2010)
Well, you could add the appropriate maintenance plan task to the SSIS package directly. That's pretty modular since it only takes a few parameters, and does all the heavy lifting for you. There's not much (any) duplicate code if you need to call this multiple times from multiple packages.If you need more power that the maintenance plan task can offer, you'd probably have to script it out. If you need modularity, you're best bet would probably be to wrap the script into a stored procedure.
Either of these options would get around calling a SQL Server Agent job, and would provide sychnonous execution.
--J
I see where your going but they lock out flexabliity. Thanks.
Kindest Regards,
Just say No to Facebook!June 29, 2010 at 12:36 pm
I'm trying to craft an SSIS solution like this myself. So, just to make sure I'm understanding this...
Executing multiple tasks that start an agent jobs, using statements such as sp_start_job, although linked with completion constraints, will still run asynchronously?
However, executing multiple tasks that call something such as sprocs with the exec command will "wait in line"?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply