SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Serving Warm SSIS Errors


Serving Warm SSIS Errors

Author
Message
Frank Banin
Frank Banin
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 425
Comments posted to this topic are about the item Serving Warm SSIS Errors

Frank Banin
BI and Advanced Analytics Professional.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27151 Visits: 13268
Nice article, but why don't you use the system variable @ErrorDescription, instead of fetching it from the logging table?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
johnbrown105 56149
johnbrown105 56149
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 1201
... sp_send_dbmail ... which is stored in the MSDN database ...


Surely you meant the msdb database.
paulhutagalung
paulhutagalung
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 303
I prefer to use logging table as I need to collect events I need from multiple components or even packages and report it at scheduled time.


the problem for this script is that instead of using ssis components for email, we have to use senddbmail which mean setting a mail profile at database is a must.
is there any way to use ssis mail task with body contains html formatted strings?
kpatrick
kpatrick
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 89
SSIS already has a built-in component for sending email. What are the advantages of using a store proc that calls sp_send_dbmail over sending the email directly from the SSIS package? You can access the error code and error description as package variables and it seems you'd get more flexibility using SSIS?
ggareth
ggareth
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 424
Nice article but my company's security policy requires Database Mail to be deactivated. We use the SSIS 'Send Mail' task instead.
MWise
MWise
SSChasing Mays
SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)

Group: General Forum Members
Points: 604 Visits: 1794
We can't use the SSIS Send Mail task because the SMTP connector does not support a remote SMTP server that requires a user id and password. We use sp_send_dbmail and a specific SQLAlert profile for all of our error notifications.

MWise



cliffb
cliffb
Right there with Babe
Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)Right there with Babe (795 reputation)

Group: General Forum Members
Points: 795 Visits: 438
It's a good article and approach. I am confused however as to how you are getting just one email to send. Internally where I work we use a custom email component (handles formatting and some other things beyond what is available in the default task) and for every time the OnError event fires, an email is sent. What your procedure would do, if I understand correctly, is still send multiple emails, it's just that the last email would have all errors accounted for. Wouldn't a better design be to call that procedure on Post excute of the package to ensure only one error email was sent?

If I am not getting something, please let me know. I'd love to have something we can implement here where we only get one email.



Frank Banin
Frank Banin
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 425
CliffB,
You are right, the task that calls the SP should be assigned to the on post execute event, I made this change after the article was approved so I it did not take.
Sorry for joining the party a little late I am actually on vac with limited Internet access. But let me know if you have any questions.

Thanks

Frank Banin
BI and Advanced Analytics Professional.
sqlserver8650
sqlserver8650
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 774
Thanks

Good Article
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search