Recipe VB stubs for Scripting in SSIS

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".

 

  1. Logging in a Control Flow Script task
  2. Logging in a Data Flow Script
  3. Getting/Setting a SSIS variable in a Control Flow Script task
  4. Getting/Setting a SSIS variable in a Data Flow Script
  5. 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.

 

  1. 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.

  1. 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.

  1. 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:

 

  1. 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:

 

 

  1. Imports System

  2. Imports System.Data

  3. Imports System.Math

  4. Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

  5. Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

  6. Imports System.Net.Mail

  7. Imports System.Net


  8. Public Class ScriptMain

  9.     Inherits UserComponent


  10.     Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

  11.         Dim myHtmlMessage As MailMessage

  12.         Dim mySmtpClient As SmtpClient


  13.         Me.Log("Sending email to " & Row.Email, 0, Nothing)


  14.         myHtmlMessage = New MailMessage(" This e-mail address is being protected from spambots. You need JavaScript enabled to view it ", Row.Email, "my subject", & _

  15.            "Dear " & Row.Name & vbCrLf & vbCrLf & _

  16.            "my message" & vbCrLf & vbCrLf & _

  17.             "Love and Kisses," & vbCrLf & vbCrLf & _

  18.             "The SSIS Admin Dude")

  19.         mySmtpClient = New SmtpClient("my SMTP Server IP address or host name")

  20.         mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials

  21.         mySmtpClient.Send(myHtmlMessage)

  22.     End Sub

  23. 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.