Use the Language of the Devil to commit evil
And to make SSIS do some really basic and common things, acutally.
It is only possible to script in SSIS using the appalling VB "language" - if you want do it in C# (a proper langauge), then you have to write a C# DLL and call it with a thunk of VB script from an SSIS component or SSIS task. You can see a full description of this process here. In the mean time, here are some recipe scripts to make the journey into the VB environment of SSIS 2005 as quick and as painless as possible.
Note: VB Scripting differs in Control Flow Script-tasks and Data Flow Script-tasks, since the script that you write overwrites methods of different VB "objects".
- Logging in a Control Flow Script task
- Logging in a Data Flow Script
- Getting/Setting a SSIS variable in a Control Flow Script task
- Getting/Setting a SSIS variable in a Data Flow Script
- Sending an email for every every record in a Data Flow Script
Recipe 1: Logging in a Control Flow Script task
Remember to enable logging in your control flow for this scriped task.
- Dts.Log("Some log message", 0, Nothing)
Recipe 2: Logging in a Data Flow Script task
Use the 'Me' construct (Real OO languages have a 'this' pointer!).
Remember to enable logging in your control flow for the dataflow task that contains this scriped task: Set ScriptComponentLogEntry to TRUE.
- Me.Log("Some log message", 0, Nothing)
Recipe 3: Getting/Setting a SSIS variable in a Control Flow Script task
Declare your ReadOnly or ReadWrite variables in the components properties field (Note that multiple variables are comma-separated with no spaces). By declaring the variable, you do not need to add any lock-and-release code.
- Dim s as String = Dts.Variables("SomeVariable").Value.ToString
Recipe 4: Getting/Setting a SSIS variable in a Data Flow Scrip-task
Declare your ReadOnly or ReadWrite variables in the components properties field:
- Dim server As String = Me.Variables.AS400Server
Recipe 5: Sending an email for every every record in a Data Flow Script-task
SSIS offers you the possibility to send an email as an SSIS task, which makes it awkward to send a different email for every record in a table, say. Assuming that we data flow columns called Email and Name and that you have access to an SMTP server:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Net.Mail
Imports System.Net
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Me.Log("Sending email to " & Row.Email, 0, Nothing)
myHtmlMessage = New MailMessage("
This email address is being protected from spambots. You need JavaScript enabled to view it. ", Row.Email, "my subject", & _"Dear " & Row.Name & vbCrLf & vbCrLf & _
"my message" & vbCrLf & vbCrLf & _
"Love and Kisses," & vbCrLf & vbCrLf & _
"The SSIS Admin Dude")
mySmtpClient = New SmtpClient("my SMTP Server IP address or host name")
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
End Sub
End Class
Set values like the SMTP server host and the sender's email address in variables using Recipe 4 above - this makes SSIS packages easily migratable between test and production environments.