Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Email out Stored Procedure results from SSIS task Expand / Collapse
Author
Message
Posted Thursday, December 23, 2010 10:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 4:40 PM
Points: 3, 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
GO

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

create procedure testout

as

begin

declare
@NL char(1)
, @NLS char(2)
, @Br varchar(128)
, @out varchar(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.
Post #1038877
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse