Sending Result set as Email via Script task
-
Hello,
I completely succeeded in creating a package that sends result set as Email. But the issue here is type of the connection I am using, right now using ADO.NET connection.
But my lead wants to have OLDB connection in order to maintain uniformity across all packages.
Requirement : querying a table having 3 fields, need to send this table content as an Email.
Steps Following : Execute SQL task to store the Result set into a System.Object variable, here connection type using ADO.NET , but my lead wants it as OLDB.
Script task: Using bellow code to load data from system.Object variable to a string variable
Send mail Task : Using string variable ( which was populated in script task) as Message source in Send mail task .
So everything working fine, getting proper email with bellow code in script task. But the issue is if I use OLDB connection type in Execute SQL task instead of ADO.NET
Connection type, Script task is not working . Bellow script is throwing some error . Can anyone please provide script that works for OLDB connection type
To load system.object variable to string variable
Error Message in Script task ( if I use OLDB connection in Execute SQL task instead of Ado.NET)
“Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'. 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”
Public Sub Main()
Dim ds As DataSet
Dim dr As DataRow
Dim str As String
Dts.Variables("Msg").Value = ""
ds = CType(Dts.Variables("SendMsg").Value, DataSet)
If ds.Tables(0).Rows.Count > 0 Then
str = vbCrLf & vbCrLf & "Record Count" & vbTab & vbTab & vbTab & vbTab & "Critical Flag" & vbTab & vbTab & "Staging Table Name" & vbCr & "============" & vbTab & vbTab & vbTab & vbTab & "=============" & vbTab & vbTab & "===================" & vbCr
For Each dr In ds.Tables(0).Rows
str &= Trim(dr.Item(1).ToString) & vbTab & vbTab & vbTab & vbTab & vbTab & Trim(dr.Item(2).ToString) & vbTab & vbTab & Trim(dr.Item(0).ToString) & vbCr
Next
Dts.Variables("Msg").Value = str
End If
Dts.TaskResult = Dts.Results.Success
Cheers
Vijay
-
Vijay,
Check this post by Matt Masson.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
CozyRoc (9/16/2010)
Vijay,Check this post by Matt Masson.
Thanks for the quick revision...:-):-):-)
Raunak J
-
This one is very simple and Helpful .....
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64014/
-
Thanks for your response .
Found very simple solution.
Used for each loop container with ADO Enumerator
Used 3 variables for variable mappings in For each loop conainer, inside used script component with bello simple script
Public Sub Main()
Dim str As String
Dim Col1 As String
Dim Col2 As String
Dim Col3 As String
Dim header As String
Dim Str1 As String
Col1 = Dts.Variables("Column1").Value.ToString()
Col2 = Dts.Variables("Column2").Value.ToString()
Col3 = Dts.Variables("Column3").Value.ToString()
str = ""
If (Dts.Variables("Msg").Value Is "") Then
Dts.Variables("Msg").Value = vbCrLf & vbCrLf & "Record Count" & vbTab & vbTab & vbTab & vbTab & "Critical Flag" & vbTab & vbTab & "Staging Table Name" & vbCr & "============" & vbTab & vbTab & vbTab & vbTab & "=============" & vbTab & vbTab & "===================" & vbCr
End If
str = Col2 & vbTab & vbTab & vbTab & vbTab & vbTab & Col3 & vbTab & vbTab & vbTab & vbTab & Col1 & vbTab & vbTab & vbCr
Str1 = Dts.Variables("Msg").Value.ToString + str
Dts.Variables("Msg").Value = Str1
Dts.TaskResult = Dts.Results.Success
-
How to display that in a table instead of using tabs?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply