SSIS How to store data stream data in a temporary table and then attach it via email?
My goal is to retrieve data from an existing table and perform a basic transformation (e.g. derived column, merge join). After that, I want to store the data in a variable. Then I will use a Script task to write an email sending program from C # and call a variable to load this data into my email content. But I am confused where I can store this temporary data after multiple transformation tasks. Any help, I appreciate it.
1. The whole entire flow of control
2. My data stream with multiple conversion tasks. I would like to know how to store data after Sort4, then I can store it in variables.
source to share
You can store these lines internally DataTable
or List
with a Script component.
Add a variable of type the SSIS Object
(an ex: User::DataList
)
In Data Flow Task, add the Script component as Destination
. Mark all columns as "Login". And use the following code. (Vb.net)
Version list
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub PostExecute()
MyBase.PostExecute()
Variables.DataList = lstObject
End Sub
Dim lstObject As Collections.Generic.List(Of DataClass)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
lstObject.Add(New DataClass With {
.CompanyName = Row.CompanyName,
.CountryName = Row.Country,
.EmployeeName = Row.EmployeeName,
.Freight = CDbl(Row.Freight)})
End Sub
End Class
Public Class DataClass
Public Property CompanyName As String
Public Property CountryName As String
Public Property EmployeeName As String
Public Property Freight As Double?
End Class
DataTable version
Public Class ScriptMain
Inherits UserComponent
Dim dtData As New System.Data.DataTable("dtData")
Public Overrides Sub PostExecute()
MyBase.PostExecute()
Variables.DataList = dtData
End Sub
Public Overrides Sub PreExecute()
MyBase.PreExecute()
dtData.Columns.Add("CompanyName")
dtData.Columns.Add("CountryName")
dtData.Columns.Add("EmployeeName")
dtData.Columns.Add("Freight")
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim drRow As DataRow = dtData.NewRow
drRow("CompanyName") = Row.CompanyName
drRow("CountryName") = Row.Country
drRow("EmployeeName") = Row.EmployeeName
drRow("Freight") = Row.Freight
dtData.Rows.Add(drRow)
End Sub
End Class
source to share
Save the result in FlatFileDestination (the flat file name can be customized with the name datetime + datetime) and use this file as an attachment in the Send Mail Task. The attachment path can be customized. The uploaded file can also be deleted from local storage after sending e-mail using the File System.
You can also use the third party tool (licensed) CozyRoc. It includes tools like "Send Mail task plus" which support the email body as html. With a script task, you can create a dynamic table (html table) script that can be stored in a variable and can be displayed in an email.
source to share