STEPS TO CREATE SSIS PACKAGE TO RUN STORED PROC AND GET E-MAIL NOTIFICATIONS

Question

Post reply

STEPS TO CREATE SSIS PACKAGE TO RUN STORED PROC AND GET E-MAIL NOTIFICATIONS

wannalearn

SSChasing Mays

Points: 649
More actions
November 18, 2010 at 11:14 am

#143554

I am a new bie and want to know about how to create ssis package to run stored proc and get email notification.
any input is appreciated

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Use the Execute SQL Task and the Send Mail Task (luckily SSIS has very logical names for its components :-))

If you do not know how to configure, open a task's editor and press F1.

p.s.: your caps lock is stuck

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

wannalearn SSChasing Mays Points: 649 More actions · Answer 2

Thank you. I tried doing that but I still get an error. I think I didn't configure it correctly. Would you send me a link or any reference which will help me understand more?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Execute SQL Task: http://technet.microsoft.com/en-us/library/ms141003.aspx

Send Mail Task: http://technet.microsoft.com/en-us/library/ms142165(SQL.90).aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

wannalearn SSChasing Mays Points: 649 More actions · Answer 4

need to export outcome in flat file and send it to e-mail. I don't see flat file in tools or is there different name for it? could you help me configure this package?

thanks in advance

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

I think the easiest configuration (in SSIS) is this one:

* use a data flow

* in the data flow, use an OLE DB Source where you execute your stored procedure

* as destination, use the flat file destination

* send the file with the Send Email Task (you can reference a file there)

More easy would be if you used a simple stored procedure called sp_senddbmail in your database (no SSIS involved):

* make sure you have configured database mail (search the msdn pages on how to do that) or contact your DBA

* run the stored procedure sp_senddbmail (http://technet.microsoft.com/en-us/library/ms190307.aspx)

* in this sp, you can configure a query (your original stored procedure). You can specify that the results should be sent as an attachment.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

wannalearn SSChasing Mays Points: 649 More actions · Answer 6

I have sql statement to run in OLEDB source, If record>0 then I want e-mail to be sent to me.

If record<1, I want couple store procs to run and create flat flile with resultset and send it to me via e-mail

I have tried dataflow as you advised, it says there is only one record. it works but I am not to sure how to send e-mail to me now.

So I have tried using send e-mail in control flow, somhow It doesn't work.

If you could help me understand what went wrong,I would really appreciate it.

wannalearn SSChasing Mays Points: 649 More actions · Answer 7

By the way I have that sp_senddbmail in sql statement too. I tried it. It's still not working

Phil Parkin SSC Guru Points: 246965 More actions · Answer 8

wannalearn (11/19/2010)
By the way I have that sp_senddbmail in sql statement too. I tried it. It's still not working

If I told you that my car was 'not working', would you be able to tell me how to fix it?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

Allright, let's try again. What did you do exactly? What are the errors?

Did you use a flat file destination? Is the file there with data after the data flow has run?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

wannalearn SSChasing Mays Points: 649 More actions · Answer 10

Requirement:

1. Check if there are records avaialble to be sent

SELECT

2.If there are not records

send an email notifying that there were no records

3. If there are records, run the following stored procedures in orders

1.exec ..

2. exec ..

3. exec

4. exec

-- Send an email

5. select

I have tried both scheduling a job and creating SSIS. I could not get it done both ways. somewhere i am missing important steps. So I just want to figure out how to do this..

Thank you for being understanding:-)