Email out Stored Procedure results from SSIS task

• brettmorin2

  SSC Enthusiast

  Points: 169

  More actions

  December 23, 2010 at 10:05 am

  #232288

  Hello I am kind of new to SSIS but know my way pretty well around TSQL. Not an expert but understand the framework of SQL well enough, but to be honest as I learn SSIS I get more questions than answers, especially on the variables and their use in SSIS.

  My ultimate goal is I have a stored procedure. It used to get a set of results and wrap up in a plain ole text report but doing a line, then repeating the variable and adding to the next line with doing line breaks. It can output fine in SQL so I wish to send this report out to end users. However due to my security constraints I cannot use Database_Mail. So my options are ADO.NET or SSIS. I want to try SSIS as it can do a lot of flow tasks and I can then schedule the tasks.

  Here is my flow so far with the creation of a sample sproc and then what I am doing in SSIS project:

  1. I write a proc and the proc may be the whole problem as it is providing line breaks as well from SQL:

  use Test

  GO

  if OBJECT_ID('testout') is not null drop proc testout;

  Go

  create procedure testout

  as

  begin

  declare

  @NLchar(1)

  ,@NLSchar(2)

  ,@Brvarchar(128)

  ,@outvarchar(max)

  select

  @NL =char(10)

  ,@NLS =char(10) + char(10)

  ,@Br ='-------------------'

  set @out = ''

  select @out = @out + @Br + @NL

  select @out = @out + 'Randomn Header info' + @NL

  select @out = @out + @Br + @NLS

  select @out = @out + 'some text here'

  select @out

  End;

  GO

  2. I test the proc in SSMS and it works okay.

  3. I open up BIDS and create a new SSIS project.

  4. Create my connection string "(localhost).test" and it tests fine

  5. Create a Sequence Container in Control Flow

  6. Create an 'Execute SQL Task' in the Sequence Container

  7. On General I do this: Leave defaults except for

  a. Result set = 'Full Result Set'

  b. ConnectionType = 'ADO.NET'

  c. Connection = (localhost).test

  d. SQL Statement = 'testout'

  e. IsQueryStoredProcedure = "TRUE"

  8. Result set I set to 0 (because SSIS gives an error on this if I don't) and use the variable name of my namespace and name of 'SQL::Execute'

  a. SQL::Execute is a variable defined as an object I learned somewhere this was better than a string.

  Okay first off this works fine but now is the problem this is executing but how do I get it out to email cleanly? I have read in some places that if you don't have access to an SMTP server you need to script alternatives as the 'Send Mail Task' is incredibly limited. I would try to script something for GMail as I already know that I can create a GMAIL SMTP account successfully for my DatabaseMail at home(won't work in this case to use DatabaseMail)

  I got some help elsewhere where someone suggested they transfer the output to a For Each Loop Container. They mapped the Variable I set in step 8a to an ForEach with a collection using the ADO Enumerator and the ADO object source as my SQL::Execute. They then map under Variable Mappings a 'User::Variable1' with an index of 0. Then they set a script object that writes out the values to a Messagebox. I take it either in the task or somewhere else I could script the output to a message instead but then would it send a message for each part of the object? I am pretty much a novice at scripting as I have created some things in C# .NET but only simple windows apps and I don't always understand C# or VB well.

  Is there a simpler way I can just set an output variable or something for my sproc and directly link it to an object or am I forced to script ultimately by limited support for email options? I am open to trying new things.

  Any help is much appreciated.