May 16, 2016 at 2:24 pm
I am working on Project where I want to create a template SSIS Package. What I would like, when SSIS Package fails. Email the user
with Row Number/Row Numbers or Column/Columns are having a problem. Either Package fail in any Transformation/source or in the destination.
For e.g my simple package is
1) Flat File Source
2) Data Conversion Task
3) Derived Column Task
4) OLE Db Destination.
Please guide me how can I accomplish this task.
May 17, 2016 at 8:32 am
Add a Send Mail Task to you package.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 17, 2016 at 8:32 am
I would usually redirect any rows causing errors into an Excel file, and attach that file to an e-mail task. (Of course, not forgetting cleanup phases so that we don't end up with hundreds of thousands of files on our servers...)
I find this approach works for me as I can then easily identify the issues in Excel and go back to the client/amend data/fix SSIS package as appropriate.
To do this you will need two 'Destinations' for step 4. Successful rows should go to your OLE DB destination. Unsuccessful rows causing errors should go to a new file destination (Perhaps you'll create a variable to hold a dynamic filename)
Then it's simply a case of insert an e-mail task after your 'failed' destination and attach the file.
Let me know if you decide to go down this route and would like any more pointers 🙂
An alternative option, if you don't need to investigate the data, would involve using a for each loop and VB/C# script task to put each row into an e-mail body. I don't have any scripts of this type saved but I'm sure there will be some online!
May 18, 2016 at 8:39 am
Thanks, Welsh/tindog.
Welsh, I wish it would be that easy to add Send Mail task 🙂
Tindog, Here what I did in past and why I want different this time. Redirect any row into Flat File or Excel from OLE DESTINATION and attached with an email to the users.
This time two things.
1) I don't want to send a file through Attachment. User requested to just display a) Which Row Number b) Which Column is giving a problem to display in the email body.
2) How about Package is giving a problem in Derived Column Transformation or Data Conversion Transformation not in OLE DB Destination. Do I have to redirect from all transformation and Use UNION ALL Transformation to load the rows into Variable or Sys Obj Variable?
Any advice?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply