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

Email out Stored Procedure results from SSIS task

Email out Stored Procedure results from SSIS task

Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 36
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

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

create procedure testout



@NL char(1)
, @NLS char(2)
, @Br varchar(128)
, @out varchar(max)

@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

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.


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