SSIS Script Task and running a stored procedure
-
The desired outcome - open an existing connection manager, run a stored procedure, save the results to a variable, close the connection manager.
I need to send HTML formatted email which means a script task. Fine. I've got that working. Yay me.
However the 4k character limit in SSIS variables has come back to bite me again. I'm sooooo hoping this doesn't extend into script task C# variables.
Now the plan is to send over everything but the body text, run the stored procedure into a new C# variable, use all those variables to send the email.
The problem for me is that I'm not a C# programmer and all the references I've found online have been less than helpful.
Can anyone point me to how to write this or provide what are probably the trivial lines of code to do it? Pretty please? - SQL 2012 and C#
-
JustMarie (1/27/2016)
The desired outcome - open an existing connection manager, run a stored procedure, save the results to a variable, close the connection manager.I need to send HTML formatted email which means a script task. Fine. I've got that working. Yay me.
However the 4k character limit in SSIS variables has come back to bite me again. I'm sooooo hoping this doesn't extend into script task C# variables.
SSIS string-variables are not limited to 4000 characters. You are probably hitting the 4000 character limit when trying to concatenate string variables using an SSIS Expression. Knowing the limitaitons, instead of using an Expression to build your string you can try doing it in a C# Script Task. This should be easier than doing all the data retrieval and emailing in C# as you described in your post as a workaround.
Steps to alternate solution:
1. Add Script Task
2. Map string variables you need to concatenate as read-only variables to your Script Task
3. Map string variable you want to hold the concatenated string as a read-write variable to your Script Task
4. Do the concatenation of the read-only variables in the C# script and map the result to your read-write variable
5. Use the variable to send the email from SSIS Mail Task
If Step 5 fails because mapping the string variable directly to a Mail Task property is still considered an Expression and is truncated at 4000 characters then you can just send the email from the C# Script Task.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I'm using a stored procedure to generate the HTML necessary for the email. That outputs to a string variable. However the SSIS string variable doesn't recognize varchar(max) as a valid output format and SQL doesn't go any higher than varchar(8000). The SSIS expression limit of 4k (thanks for the differentiation) doesn't apply here.
FYI - the stored procedure is an ugly beast that forms the HTML through XML first so even doing this in separate pieces won't help since the guts of it have the ability to exceed a varchar(8000) field.
So I'm back to hacking my way around the limitations.
Back to my original quest - finding the right code to execute a stored procedure in the script task and outputting the result to a string variable.
-
JustMarie (1/28/2016)
I'm using a stored procedure to generate the HTML necessary for the email. That outputs to a string variable.Can you clarify "outputs?" Do you mean as a resultset with one row and one column or as an output parameter of the stored procedure?
However the SSIS string variable doesn't recognize varchar(max) as a valid output format and SQL doesn't go any higher than varchar(8000)
Are you trying to map the output of the proc to an SSIS Variable in an Execute SQL Task? If you are trying this using a resultset (Single Row) it will not work. If you setup the proc to deliver an output parameter and map that to a variable in the Execute SQL Task you can get your HTML-string into an SSIS String-variable.
The SSIS expression limit of 4k (thanks for the differentiation) doesn't apply here.
FYI - the stored procedure is an ugly beast that forms the HTML through XML first so even doing this in separate pieces won't help since the guts of it have the ability to exceed a varchar(8000) field.
This is why I push my dev team to use SSRS to communicate pretty HTML through email. T-SQL is just not a great vehicle for this kind of work IMO.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
The original plan was to use SSRS but that's not an option here right now. So throw that one out the window.
Second plan was to make Excel files and send the link to them in email. Except that creating dynamic Excel destination files turned out to be something else SSIS didn't seem to like very much. Plus there was no need to store files when the data is in a table.
Straight out email is the current plan. I was using an Execute SQL task and running the stored procedure as a select statement to return the HTML string. Then I butted up against the varchar(max)/SSIS String variable problem. Varchar(8000) worked until the output was longer than 8,000 characters.
I've changed the stored procedure to use an output parameter for the return value while I poke around with C# in the Script task. Based on my latest Google search for how this works I may be onto something but since it's all new to me I'm learning as I go.
So yeah. If the SSIS Send Mail task would handle HTML my life would be lovely. Since it doesn't and I need to send a table as the message body I'm wading into scripting.
-
JustMarie (1/28/2016)
The original plan was to use SSRS but that's not an option here right now. So throw that one out the window.I commonly see the same few barriers to adoption on this one. Do you mind sharing what is the barrier or barriers in your case?
Second plan was to make Excel files and send the link to them in email. Except that creating dynamic Excel destination files turned out to be something else SSIS didn't seem to like very much. Plus there was no need to store files when the data is in a table.
A lot of people make the assumption working with an Excel file is like working with a flat-file in that you can just point an Excel Destination in a Data Flow to a file path and the Excel Driver will spin up a new Excel Workbook file on disk, on the fly, prior to the data being pushed into it. This would be analog to expecting that when you point an OLE DB Destination in a Data Flow Task to an instance of SQL Server and configure the component to load data into table DatabaseName.SchemaName.TableName that the SQL Server Native Client OLE DB Driver will create the database, the schema and the table on the fly before loading data into the table. While we know the latter is a preposterous idea many folks think SSIS is to blame for the former scenario not playing out as they might expect.
I've changed the stored procedure to use an output parameter for the return value while I poke around with C# in the Script task. Based on my latest Google search for how this works I may be onto something but since it's all new to me I'm learning as I go.
Sounds pretty straightforward to me...an Execute SQL Task will run your proc and map the value of the output parameter to an SSIS String-variable followed by a Script Task that accepts the String-variable as a ReadOnly input and the C# script sends an HTML email with that content.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
JustMarie (1/27/2016)
The desired outcome - open an existing connection manager, run a stored procedure, save the results to a variable, close the connection manager.I need to send HTML formatted email which means a script task. Fine. I've got that working. Yay me.
However the 4k character limit in SSIS variables has come back to bite me again. I'm sooooo hoping this doesn't extend into script task C# variables.
Now the plan is to send over everything but the body text, run the stored procedure into a new C# variable, use all those variables to send the email.
The problem for me is that I'm not a C# programmer and all the references I've found online have been less than helpful.
Can anyone point me to how to write this or provide what are probably the trivial lines of code to do it? Pretty please? - SQL 2012 and C#
You have a stored procedure that does all the work you're talking about. Why not just extend that stored procedure a bit to use the generated HTML and send the email from the proc itself? You could still include the proc in whatever SSIS controlled process you have.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Policies. We can connect to the SMTP server in SSIS but can't use db_sendmail.
I know I'm just missing some basic C# stuff here so I'll keep digging around on the interwebz.
-
https://msdn.microsoft.com/en-us/library/ms403365(v=sql.120).aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Orlando - I can send the message just fine. The step that's tripping me up is getting the output of a stored procedure into a string variable that can be used as the message body.
This is what happens when I don't learn C# and suddenly I need to use a script task.
-
I thought this was resolved...you meed to use an Output Parameter not a Resultset to get the MAX-value from the database into an SSIS Variable.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Sadly I have next to no C# experience nor do the folks around me. So I'm hacking through this.
I'm not getting a max value. I'm executing a stored procedure. The result is the same - I want the output in a string variable.
Trying to get there is the problem.
Send an email using input parameters was pretty cut and paste. If I ever get this thing working I'm saving the code so I have it!
-
I think you are making this harder on yourself than it needs to be. Do not let the C# aspect intimidate you.
Ingredients:
1. Stored Procedure
2. SSIS Package
3. Database Connection String
4. SMTP Settings
Recipe:
1. Develop a Stored Procedure with one Output Parameter of type NVARCHAR(MAX). When executed HTML content should be assigned to the Output Parameter.
2. Develop SSIS Package with:
2.1. Execute SQL Task that calls your new Procedure and maps the Output Parameter to an SSIS Variable of type String.
2.2. From EST, a C# Script Task will run that takes the SSIS String Variable as a Read-only input and sends an email (refer to Books Online article link in previous post).
A walk-through:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Got it! The basic problem was the size of the output string.
The information here explained that I needed to use an Object variable type since I overflowed the String.
Note that in the Execute SQL Task the output needs to be a Full Result Set even though there's only one row.
Then I need to run through the object and assign the row value to a variable.
For those of us who are C# challenged here's the changes to the code in that link:
Create an empty variable in the scope of the namespace
String SendMailBody = string.Empty;And change the message box to assign the row value to that variable
MessageBox.Show (rowLoop[0].ToString());to
SendMailBody = (rowLoop[0].ToString());This works just as I needed with minimal scripting - just enough to send the HTML formatted email.
Thanks Orlando for your help in trying to get this resolved. The big problem was the size of the output string and how SSIS handles those.
-
JustMarie (2/3/2016)
Note that in the Execute SQL Task the output needs to be a Full Result Set even though there's only one row.If you choose to use resultsets, yes, that is true but there is no need to do it that way. As I explained above you can use an Output Parameter to capture an NVARCHAR(MAX) into an SSIS Variable and not experience truncation. It is cleaner than using the Full Resultset method you found (i.e. no C# to write) but I am happy you finally got something working and got your feet wet with a Script Task.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply