![]()
1.Open 'Business Intelligence Development Studio' comes with SQL 2005 or VS 2008 and create Integration Services project
2.Design .dtsx ( pakage file)
3.Our goal is to import .xlsx file data to SQL DB, log package status and if error occurs log error & send Email.
4.
Create connection managers like ADO.NET connection, OLEDB connection, and SMTP connection etc
![]() |
| xlsx file-OLEDB connection |
-
4. Drag & drop control flow items as shown here depends on the design. Create Variables as shown in the figure.
5. In the 'Data Flow' connect the source and destination connections through a data convertor.
6. Create an onError Event Handler, and create a 'Script Task'. Set the values like 'System::ErrorDescription' as read only variable and 'User::errorMessages' as readwrite variable.
7. Click on Design Script & create code to capture the all errors into a Colloection.
8. Create 'Sequence Containers' as shown in the SSIS package and create 'Script Task' to capture all errors into a single string and insert to DB and send Email.
9. Click on Design Script
' Microsoft SQL Server Integration Services Script Task' Write scripts using Microsoft Visual Basic' The ScriptMain class is the entry point of the Script Task.'Developed By : Rajesh SubbiahImports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient
Imports System.Net
Imports System.Net.Mail
Public Class ScriptMain
Public Sub Main()
Dim errorDesc As String
Dim messages As Collections.ArrayList
Try
messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)
Catch ex As Exception
'If there is an exception – the object was never initialized, so there were no errorsReturn
End Try
For Each errorDesc In messages
Dts.Variables("emailText").Value = Dts.Variables("emailText").Value.ToString + errorDesc + vbCrLf
Next
Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP_CONN_MAIL_SERVER").AcquireConnection(Dts.Transaction), String)
Dim smtpServer As String = smtpConnectionString.Split(New Char() {"="c, ";"c})(1)
Dim htmlMessageFrom As String = Dts.Variables("emailFrom").Value.ToString()
Dim htmlMessageTo As String = Dts.Variables("emailTo").Value.ToString()
Dim msgErrors As String = Dts.Variables("emailText").Value.ToString()
Dim htmlMessageSubject As String = Dts.Variables("emailSubject").Value.ToString()
Dim htmlMessageBody As String = "
Warning ! : The following error occured while running the SSIS package
"
htmlMessageBody = htmlMessageBody + " " + msgErrors + "."
LogError(msgErrors) ' Log the error into DB
SendMailMessage(htmlMessageTo, htmlMessageFrom, htmlMessageSubject, htmlMessageBody, True, smtpServer)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub SendMailMessage( _
ByVal SendTo As String, ByVal From As String,ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)
If (SendTo.Trim() = "") Then SendTo = From
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim maSendTo As New MailAddress(SendTo)
Dim maFrom As New MailAddress(From)
htmlMessage = New MailMessage(maFrom, maSendTo)
htmlMessage.Subject = Subject
htmlMessage.Body = Body
htmlMessage.IsBodyHtml = IsBodyHtml
mySmtpClient = New SmtpClient(Server)
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
htmlMessage.Dispose()
End Sub
Private Sub LogError(ByVal msgErrors As String)
Dim cm1 As ConnectionManager = Dts.Connections("ISTools_ADO.NET")
'Dim cm2 As ConnectionManager = Dts.Connections("ISTOOLS_OLEDB")' For an ADO.Net ConnectionManager using a SqlClient providerDim sqlConn As System.Data.SqlClient.SqlConnection = DirectCast(cm1.AcquireConnection(Dts.Transaction), SqlConnection)
Dim oCmd As SqlCommand = New SqlCommand
oCmd.CommandType = Data.CommandType.Text
oCmd.CommandText = "INSERT INTO Log_SSIS_Status(PkgName,PkgStatus,IsSuccessful,Notes) VALUES('HScan-ImportFile','SSIS-Failure.Logged,Sent Email',0,'" + msgErrors + "')"
oCmd.Connection = sqlConn
'sqlConn.Open()oCmd.ExecuteNonQuery()
'release the object finallycm1.ReleaseConnection(sqlConn)
End Sub
End Class
10. The variables can be configured via a config file.
11. run in development environment.Set Break points to debug.
12.Deploy the package to the Server and view the job through 'Server Agent' in the management studio.
