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:
if OBJECT_ID('testout') is not null drop proc testout;
create procedure testout
, @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'
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.