November 16, 2011 at 5:35 pm
Hi,
I have some fairly simple SSIS jobs that load tables via several "Execute SQL task"
then I do BI-Cube processing and such...
{ Some Back ground before you ask:
I am migrating these from Job Agent to SSIS so our Corporate Job scheduler can kick it off dependent on other teams jobs...
I tried having the SSIS package kick off the Job Agent jobs and it doesn't work, because SSIS just kicks it off then considers the job complete and goes to the next step, it does not wait for the job to actually complete so this messes up the flow.}
There are a couple of steps that fail (due to source table locks) about 5% of the time, If we wait 1 or 2 minutes and run it again it will normally run fine (if not a 3rd time almost always catches it)
What I need help with is...
1. For these specific steps, If they fail I need to make it wait a minute or 2 then retry the step (up to 2 times) Guessing I can just duplicate the step and lead it out on failure to basically repeat a copy of the original step, but there has to be a neater way of doing it. Not sure how to control a for loop in this instance
2. Once this step (or any of my SQL Task steps) fails, It sends an alert email, this is in place and working but I want the email to include the actual step name that failed. I currently have the steps grouped into logical containers and the on failure email attached to the container, I need the failed job to pass it's ID-Name to this failure step and it wont. It seems the name only stays within the scope INSIDE the container, and won't pass it our of the container. I do not want to have to attach a hard coded failure email to each and every step, that would just seem like overkill!
any tips advice?
Any Help is appreciated...
(I am signed up for an SSIS class in January, but really need to get this started now)
-James
November 16, 2011 at 10:17 pm
garudatx (11/16/2011)
Hi,I have some fairly simple SSIS jobs that load tables via several "Execute SQL task"
then I do BI-Cube processing and such...
{ Some Back ground before you ask:
I am migrating these from Job Agent to SSIS so our Corporate Job scheduler can kick it off dependent on other teams jobs...
I tried having the SSIS package kick off the Job Agent jobs and it doesn't work, because SSIS just kicks it off then considers the job complete and goes to the next step, it does not wait for the job to actually complete so this messes up the flow.}
There are a couple of steps that fail (due to source table locks) about 5% of the time, If we wait 1 or 2 minutes and run it again it will normally run fine (if not a 3rd time almost always catches it)
What I need help with is...
1. For these specific steps, If they fail I need to make it wait a minute or 2 then retry the step (up to 2 times) Guessing I can just duplicate the step and lead it out on failure to basically repeat a copy of the original step, but there has to be a neater way of doing it. Not sure how to control a for loop in this instance
for-loop, to iterate over values 1,2,3
execute sql task --success-> script task to set the loop variable to 3 (so, done looping)
--failure -> script task to call .net sleep function to wait for X seconds (then will loop again)
also, set max error count on the container to 3 so the loop will fail after 3 attempts.
2. Once this step (or any of my SQL Task steps) fails, It sends an alert email, this is in place and working but I want the email to include the actual step name that failed. I currently have the steps grouped into logical containers and the on failure email attached to the container, I need the failed job to pass it's ID-Name to this failure step and it wont. It seems the name only stays within the scope INSIDE the container, and won't pass it our of the container. I do not want to have to attach a hard coded failure email to each and every step, that would just seem like overkill!
any tips advice?
declare a variable at the package scope to hold the ID-name and error message(s). Add an error handler to each job step that you want an error email for. In it set the variable and append the error message. You can then link up several containers with "OR" and failure precedence constraints to 1 send-email task.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply