March 23, 2010 at 3:53 pm
Hi,
I have a SQL 2005 SSIS on Windows 2003. The package runs thru scheduled job daily. The package bring all csv files from different web servers into a folder in SQL server. Upload file into staging, process all rows and finally write csv result file to subfolder and send email to submitter with the attahcment. Not sure job failed at writing csv (permission or error in XML config file); so no email to users. However, the job did process and create rows in all required tables. I updated the SSIS, so say error in writing csv attachment, to call new procedure to send email with summary report in message part. It works fine. The job failed at the end and stop processing all other files. Is there way I could set package or job to ignore the error for temp fix? Thanks.
Chris
March 23, 2010 at 4:49 pm
You say that the job
failed at the end and stop processing all other files
What task did it fail on? Since you have this inside a loop container, you should be able to set up an OnError event handler for the task that fails. Inside the Event handler, instead of placing a task in there, go to edit the package variables. There is a variable that is scoped only to the OnError handler called Propagate. Set this to false. This tells SSIS to not bubble teh error up to the loop container and instead to continue execution with the next iteration of the loop.
March 23, 2010 at 5:32 pm
John,
Thanks for the help. Inside ForEach Loop, it tuncates, upload, process, write attachment (with Data Flow Task), send email with attachment and finally move file to Archive folder. It failed at writing attachment in Mail folder (subfolder of processing folder). I could not convince our DBA that it is permission issue. But again, it could be the XML config file I created....
Not sure how to create event handler, could I set ForceExecutionResult to truce for those steps on and after writing attachment? Thanks.
Chris
March 24, 2010 at 2:56 pm
To create an OnError Event Handler:
1. On the Control Flow, set the focus on the task that you would like to create the event handler on.
2. With the task selected, tab over to the Event Handlers tab. You'll notice that the screen is grayed out and a blue message appears in the center of the screen stating: "Click here to create an 'OnError' event handler for the executable 'your task name here'.
3. Click the link. This will create the OnError event handler. Here, on this page, you can drag over and use any of the tasks that you find in the toolbox. For what I described to you, you will not need to set up any tasks; rather, continue on with #4 below.
4. Open the Variables window. Ensure that the system variables are visible, if not, click on the 'Show System variables' icon.
5. Scroll through the list and look for the variable that named Propagate. You should see that it has a scope of OnError.
6. Set the Propagate Variable's value to False.
March 25, 2010 at 11:57 am
John,
Thanks a million. It works.
I can now focus why it could not write CSV attachment.
Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply