0

As I understand it, one can create an SSIS package that will run on a schedule with SQL Server Agent, and email the results in an Excel format. YAY IDEAL!

I've looked into how to do this, and everything seems to fall just short of EXACTLY HOW.

What I've Tried in SSIS Building - I'm new to it:

  • I've tried dragging "Execute SQL Task" to the Control Flow. In there I can get things started with my SQL task, but I can't seem to make it use today's date as the parameter, or push it to Excel.
  • I've been able to drag a "Data Flow Task" over which gives me the options for excel. But it appears to not allow me to call a stored procedure.

What I'd Love:

Any tutorial that says, "HEY, that stored procedure you run every month, this is how to automate it so it runs and emails itself to the needed people.

Any and all resources very welcome.

Gant Laborde
  • 6,484
  • 1
  • 21
  • 30
  • The steps are: 1. Drop on a dataflow task to export data from the database to an Excel file. It sounds like you need to use the stored procedure as your source in the data flow task. 2. Drop on an email task (drag the green line from data flow to the email to ensure the email task waits). Configure the email task to email what you just sent. Where does 'todays date' come into it? Is it a parameter to the stored procedure? – Nick.Mc Feb 26 '14 at 00:06

1 Answers1

0

You may have to add these lines to the stored procedure:

SET FMTONLY OFF

SET NOCOUNT ON

Jamie T blogged about this issue years ago:

http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mike Honey
  • 14,523
  • 1
  • 24
  • 40