May 12, 2014 at 11:35 pm
Hi,
I am trying to use a vb script to send an html email that shows query results in a table within the email - this much is in principle not an issue as there are many places that show how to do it.
The problem I am having is getting the text string from the query to the vb script... when I run my query that generates the html-formatted table, which I want to be the body of my email, it generates a single text string that is over 100,000 characters. (If I run my query from SSMS and save this text string to a .html file then it displays in IE just fine.) I was trying to set up my package to have an SQL task run the query with single result row and map the result into a string variable, but SSIS won't write from the SQL task to the variable at runtime - I think because the query returns such a long string. I wanted to use that variable, containing the html-formatted string, as my email body in my vb script.
I thought maybe I would generate a .txt file containing the query result; then it would be easy to read from that .txt file into the vb script. But this seems like a silly approach, generating a text file to pass info that should stay within SSIS - and I'm not confident to quickly get the data flow to work to write the text file since it will probably have some complication that I don't foresee. So I thought I'd ask here first before killing another few hours on a bad approach...
Can anyone tell me how to run my sql query and get the result - a single, 100k+character nvarchar - to be accessible to a vb script?
Thanks!
Tai
May 13, 2014 at 12:07 am
Maybe you can create a stored procedure that runs the query and call this stored procedure directly from VB.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2014 at 9:44 am
Thanks Koen. Would you be able to help me understand how to do that? I tried one approach (ExecuteScalar()) and gave up on it, now trying something else that I think might work but I am clearly not doing it right...
I tried this (ExecuteScalar):
'htmlBodyQuery
Dim sqlConn As System.Data.SqlClient.SqlConnection
Dim sqlComm As System.Data.SqlClient.SqlCommand
'code to run stored proc - from http://stackoverflow.com/questions/18128792/executing-sql-inside-ssis-script-task-is-not-working
'for potential future reference - this shows adding parameters: http://qa.sqlservercentral.com/Forums/Topic455923-148-3.aspx
Dim cm As ConnectionManager = Dts.Connections("myOLEDB_Connection") ''Retrive the reference to the managed Connections
Dim fireAgain As Boolean = True
'' Request an open connection
sqlConn = cm.AcquireConnection(Dts.Transaction)
'Dts.Events.FireInformation(0, "", "Connection is: " + sqlConn.State.ToString(), "", 0, fireAgain)
''Do your work
sqlComm = New System.Data.SqlClient.SqlCommand("EXEC dbo.myStoredProc", sqlConn)
strHtmlBody = sqlComm.ExecuteScalar()
MsgBox(strHtmlBody.ToString()) ''' test to see if the html code was obtained.
''Inform SSIS you're done your work
cm.ReleaseConnection(sqlConn)
...but it wasn't working and before I figured out what (else) I was doing wrong, I read that ExectueScalar() will only return four thousand and some characters.
Now I am trying this:
Public Sub Main()
'
' Sends an email...
'
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim strSubject As String, strHtmlBody As String, strSMTP As String
Dim arrCCs As String(), arrTos As String(), strFlex1 As String
''''''''''''''''''''''''''''''''BROKEN PART - THIS IS SUPPOSED TO GET THE HTML BODY STRING FROM THE STORED PROC''''''''''''''''''
'htmlBodyQuery
Dim sqlConn As System.Data.SqlClient.SqlConnection
Dim sqlComm As System.Data.SqlClient.SqlCommand
'code to run stored proc - from http://stackoverflow.com/questions/18128792/executing-sql-inside-ssis-script-task-is-not-working
'for potential future reference - this shows adding parameters: http://qa.sqlservercentral.com/Forums/Topic455923-148-3.aspx
Dim cm As ConnectionManager = Dts.Connections("myOLEDB_Connection") ''Retrive the reference to the managed Connections
Dim fireAgain As Boolean = True
'' Request an open connection
sqlConn = cm.AcquireConnection(Dts.Transaction)
''Do your work
sqlComm = New System.Data.SqlClient.SqlCommand("EXEC dbo.myStoredProc]", sqlConn)
Dim reader As SqlDataReader = sqlComm.ExecuteReader()
While reader.Read()
Console.WriteLine("{0}", reader(0))
reader.GetString(0)
End While
'MsgBox(strHtmlBody.ToString()) ''' test to see if the html code was obtained.
''Inform SSIS you're done your work
cm.ReleaseConnection(sqlConn)
'''''''''''''''''''''''''''''''''''''''''END BROKEN PART'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
strSubject = Dts.Variables("User::Table").Value
strSMTP = Dts.Variables("User::smtpForScript").Value
strFlex1 = Dts.Variables("User::EmailTo").Value
arrTos = Split(strFlex1, ";")
strFlex1 = Dts.Variables("User::EmailCCs").Value
arrCCs = Split(strFlex1, ";")
myHtmlMessage = New MailMessage() 'New MailMessage(strFrom, strTo)
myHtmlMessage.From = New MailAddress(Dts.Variables("User::FromAddress").Value, Dts.Variables("User::FromDisplay").Value) 'address , display name
For Each strFlex1 In arrTos
If Len(Replace(strFlex1, " ", "")) > 1 Then
strFlex1 = Trim(strFlex1)
myHtmlMessage.To.Add(New MailAddress(strFlex1, strFlex1)) 'address , display name
End If
Next
For Each strFlex1 In arrCCs
If Len(Replace(strFlex1, " ", "")) > 1 Then
strFlex1 = Trim(strFlex1)
myHtmlMessage.CC.Add(New MailAddress(strFlex1, strFlex1)) 'address , display name
End If
Next
myHtmlMessage.Subject = strSubject
myHtmlMessage.Body = strHtmlBody
mySmtpClient = New SmtpClient(strSMTP)
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
myHtmlMessage.IsBodyHtml = True
mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = DTSExecResult.Success
End Sub
...and it is giving me this error (in the outer SSIS window - I shamefully do not know the right way to debug a vb script, only know how to debug vba in Excel...):
SSIS package "SSIS Jobs Summary Report.dtsx" starting.
Error: 0x1 at Email SISS Jobs Summary: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
at ST_ffc99809f664496a92aaa4ffb47a5c75.vbproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Task failed: Email SISS Jobs Summary
Warning: 0x80019002 at SSIS Jobs Summary Report: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "SSIS Jobs Summary Report.dtsx" finished: Failure.
The email part was working when I passed in a fixed value from an SSIS variable, it is just getting the stored proc result into the vb script variable that is throwing me. Once that is working, I would like to modify my stored proc to return either multiple result sets or multiple rows (one text string each) and then write those multiple result sets or multiple rows to different vb script variables.
Please help me some more - I'm stuck! Thanks!!
Tai
May 14, 2014 at 6:11 am
I guess sqlConn = cm.AcquireConnection(Dts.Transaction) is the code that breaks, as you get an invalid cast error.
When I look for sample code - Use Connections Properly in an SSIS Script Task - they use an explicit cast (System.Data.SqlClient.SqlConnection).
Also, I think they use an ADO.NET connection instead of an OLE DB connection.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 8:39 am
Thanks Koen! You were right, it needed to be an ADO connection; the explicit casting turned out not to be needed. Below is what I ended up with.
'''''''''''''''''''''''''''''''USE SQL QUERY TO GET HTMLBODY AND SUBJECT'''''''''''''''''''''''
'htmlBodyQuery
Dim sqlConn As System.Data.SqlClient.SqlConnection
Dim sqlComm As System.Data.SqlClient.SqlCommand
'http://toddmcdermid.blogspot.be/2011/05/use-connections-properly-in-ssis-script.html#!/2011/05/use-connections-properly-in-ssis-script.html
'http://qa.sqlservercentral.com/Forums/Topic1570121-147-1.aspx#bm1570791
'code to run stored proc - from http://stackoverflow.com/questions/18128792/executing-sql-inside-ssis-script-task-is-not-working
'for potential future reference - this shows adding parameters: http://qa.sqlservercentral.com/Forums/Topic455923-148-3.aspx
Dim cm As ConnectionManager = Dts.Connections("myConnection- ADO") ''Retrive the reference to the managed Connections
'' Request an open connection
sqlConn = cm.AcquireConnection(Dts.Transaction)
''Do your work
sqlComm = New System.Data.SqlClient.SqlCommand(Dts.Variables("User::htmlBodyQuery").Value, sqlConn)
Dim reader As SqlDataReader = sqlComm.ExecuteReader()
'While reader.Read()
'Console.WriteLine("{0}", reader(0))
reader.Read() 'used this instead of while loop
strHtmlBody = reader.GetString(0)
reader.Read() 'used this instead of while loop
strSubject = reader.GetString(0) '"Jobs Summary - needs to read subject from query..."
'End While
''Inform SSIS you're done your work
cm.ReleaseConnection(sqlConn)
'''''''''''''''''''''''''''''''FINISHED USING SQL QUERY TO GET HTMLBODY AND SUBJECT'''''''''''''''''''''''
...I would rather have used multiple result sets but rather than figuring out how it was easier for this time to just union all in my stored proc so that my two text strings were both part of one result set; then read one line, read the other line.
Bottom line is now, it works! Thanks again for lending your expertise.
Tai
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply