Debugging SSIS Script Tasks

SSIS Script tasks provide codes to implement customized functions that a built-in SSIS task cannot achieve directly. It is often necessary to debug through a Script task to ensure it works properly. You can set breakpoints for events such as OnPreExecute or OnPostExecute from ten break conditions as we discussed in Set Breakpoints for SSIS Debugging. Moreover, you can define stopping points in a script task through Microsoft Visual Studio Tools for Applications (VSTA).

Set a breakpoint in VSTA for a Script Task?

After you click “Edit Script” button in Script Task, a VSTA window will open.

Click the line you would like to set a breakpoint. Then right-click it and select Breakpoint –> Insert Breakpoint.

Below shows setting breakpoints in SQL Server 2008 R2.

Now you have the breakpoint set right before Messagebox.Show(). This means that the execution should break immediately before the message box is popped up.

SQL Server 2012

The way to set breakpoints in Script Tasks for SQL Server 2012 is similar as shown.

Also, after clicking Insert Breakpoint, a popup window will let you to do more fine tunings for your breakpoint through locations of Line and Character.

Note that SQL Server 2012 has one additional option: Insert Tracepoint besides “Insert Breakpoint”.

 

How breakpoints work?

After you set up the breakpoint, you can close Script Task Editor window. Right Click the Script Task and then select Execute Task. You would expect a yellow arrow displayed at the breakpoint.

After you press F10 button, a message box pops up with the message “Hello World”. After you close the popup window, the VSTA window should appear like this.

Tweaks for SQL Server 2008

If you use SQL Server 2012, you are lucky to get the breakpoint hit as described above.

If you use SQL Server 2008, you need some tweaks to make breakpoints working as expected:

Step 1: Set SSIS runtime mode as 32 bit at PROJECT Level.

Step 2: Re-open Script Task Editor window and save it again. In this way, your script task code is set to be compiled as 32 bit.

 

Shortcut Keys for Debugging

You can use debugger shortcut keys to speed up debugging. SSIS Script tasks share the same set of shortcut keys for Visual Studio. Here is a list of some common shortcut keys.

Keys	Functions
F5	Run the application.
F10	Step Over (Execute the next line of codes but not follow execution through any function calls).
F11	Step Into.
SHIFT+F11	Step Out.
CTRL+BREAK	Stop execution (Break).
F9	Toggle breakpoints.

Now I hope that you have a solid understanding of how to debug script tasks in SSIS. Take time to play with it and you will find it save the day for you in troubleshooting. If you have any questions, please feel free to leave a comment.

Reference:

1. Debug a Script by Setting Breakpoints in a Script Task and Script Component
2. Debugger Shortcut Keys for Visual Studio

Set Breakpoints for SSIS Debugging

Like debugging codes for other languages or tools, it is often necessary to set breakpoints to pause execution so you can examine variable values where you think the problem can be. SSIS provides a very straightforward GUI to help you set breakpoints in SSIS packages.

Where to set breakpoints?

You can set breakpoints on a task or a container. A task can be Execute SQL Task, data flow tasks, script tasks, etc. A container can be a For Loop container, a Foreach Loop container, or a Sequence container.

Moreover, there are eleven break conditions that you can choose from as shown below

These break conditions are defined as:

1. OnPreExecute: Called by a task or a container immediately before it runs.
2. OnPostExecute: Called by a task or a container immediately after it runs.
3. OnError: Called by a task or container when an error occurs.
4. OnWarning: Called when the task is in a state that does not justify an error, but does warrant a warning.
5. OnInformation: Called when the task is required to provide information.
6. OnTaskFailed: Called by the task host when it fails.
7. OnProgress: Called when there is measurable progress about task execution.
8. OnQueryCancel: Called at any time in task processing when a cancel execution is fired.
9. OnVariableValueChanged: Called when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event.
10. OnCustomEvent: Called by a custom task-defined events.
11. Loop iterations: Called when the iteration condition in a loop is satisfied. This only appears for a For Loop container and a Foreach Loop container.

Moreover, there are four Hit Count types you can define:

• Always
• Hit count equals
• Hit count greater than or equal to
• Hit count multiple

After you define a Hit Count Type, you can specify a Hit Count at which the breakpoint executes. This is especially useful when you want to skip some iterations and break at some specific iterations.

How to set breakpoints?

In SSIS designer, navigate to the control flow panel.  Right-click the object where you want to set the breakpoint and then click the Edit Breakpoints option. You will see a Set Breakpoints window popup just like the picture shown above.

Next, select break conditions you like to have. Here you can combine multiple break conditions. For example, you can let it break at both OnPreExecute and OnPostExecute events so that you can examine the changes on variables. The default Hit Count Type is grayed out when the related break condition is unchecked. Once a break condition is selected, you can go further to define Hit Count Type and Hit Count. Below is a breakpoint set at a loop when its loop iteration is equal or larger than 2.

After you close the Set Breakpoints window, you will notice a red dot appears on the object with breakpoints.

Furthermore, setting breakpoints in SSIS Script tasks is a little different from the way shown above. If you would like to know more, please stayed tuned.

How to modify breakpoints?

You can modify the breakpoint in the same way you set breakpoints. Right-click the object and then click the Edit Breakpoints option.

Now you have a powerful tool to help your troubleshooting effectively. With breakpoints, you can step through your ETL package to keep track of your variables and status of packages.

Script Task: Find the Day of Week in SSIS

Problem

How to find out the day of week in SSIS to achieve the same result as DATENAME( dw, datecolumn ) as in TSQL? For example, we need to find out “2013-11-24” is Sunday or Saturday.

Solution

Suppose FROM_SERVICE_DATE is the date column to derive the info about day of week. Below are two options. Using “Script Task” seems more complicate than directly using “Derived Column”. But it is more readable, isn’t it? Moreover, from this example, you can learn to use “Script Task” to handle multiple CASE situations.

Using “Derived Column”

Below is the expression to input in “Derived Column” task by using nested Conditional Operator in SSIS.

DATEPART("dw",FROM_SERVICE_DATE)==1?"Sunday":DATEPART("dw",FROM_SERVICE_DATE)==2?"Monday":DATEPART("dw",FROM_SERVICE_DATE)==3?"Tuesday":DATEPART("dw",FROM_SERVICE_DATE)==4?"Wednesday":DATEPART("dw",FROM_SERVICE_DATE)==5?"Thursday":DATEPART("dw",FROM_SERVICE_DATE)==6?"Friday":DATEPART("dw",FROM_SERVICE_DATE)==7?"Saturday":""
 
Using “Script Task”

Step 1: Derive a new column with "dayNoOfWeek” as Name for the numeric number of day of week, such as 1st or 6th of the week.

Step 2: Drag and drop Script Component task to the Data Flow Task (DFT) pane, you will be prompted to select script component type as shown below. Check “Transformation”. Then connect it with the above Derived Column Task.

Step 3: Under “Input Column” page, check “dayNoOfWeek” as shown below.

Step 4:
Under “Inputs and Outputs” page, expand “Output0” and select “Output Columns”. Click “Add Column” button. A new column will appear. You can rename it to “NameOfWeekDay”.
Next, modify the column properties on the right. Set the new column to be a string with length of 10.

Now go back to “Script” page on the top, select the “ScriptLanguage” to be C# related. Then click “Edit Script” button at the low right corner.

Enter the following codes in the Sub function Input0_ProcessInputRow.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

        switch (Row.dayNoOfWeek) {             
            case 1: Row.NameOfWeekDay = "Sunday";       break;
            case 2: Row.NameOfWeekDay = "Monday";       break;
            case 3: Row.NameOfWeekDay = "Tuesday";      break;
            case 4: Row.NameOfWeekDay = "Wednesday";    break;
            case 5: Row.NameOfWeekDay = "Thursday";     break;
            case 6: Row.NameOfWeekDay = "Friday";       break;
            case 7: Row.NameOfWeekDay = "Saturday";     break;
        }
   }

If you prefer to Visual Basic as your script language, add the following code in the sub function of Input0_ProcessInputRow.       

SELECT CASE Row.dayNoOfWeek
            Case 1
                Row.NameOfWeekDay = "Sunday"
            Case 2
                Row.NameOfWeekDay = "Monday"
            Case 3
                Row.NameOfWeekDay = "Tuesday"
            Case 4
                Row.NameOfWeekDay = "Wednesday"
            Case 5
                Row.NameOfWeekDay = "Thursday"
            Case 6
                Row.NameOfWeekDay = "Friday"
            Case 7
                Row.NameOfWeekDay = "Saturday"
END SELECT
Next, build and close the Script Edit window.  

How to: Send Emails by Script Task in SSIS

Sending emails are common tasks in control flow of SSIS packages. We need to inform users when packages run completely or fail. Script Task in SSIS provides a more simple but more flexible approach than the 'Send Mail Task'. Using 'Script Task', we can customize our sending email tasks. That is to say, with 'Script Task', we can configure senders, recipients, subjects, and bodies of an e-mail message. OK, let's get started!

First, you need to add the following for the header of your script.

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net.Mail

Imports System.Net

Then, define variables:

Dim myHtmlMessage As MailMessage

Dim mySmtpClient As SmtpClient

The sender, recipient, subject, and body of an e-mail message may be specified as parameters when a MailMessage is used to initialize a MailMessage object.

Below is the whole script body:

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net

Public Class ScriptMain
  Public Sub Main()
    Dim myHtmlMessage As MailMessage
    Dim mySmtpClient As SmtpClient

    myHtmlMessage =
    New MailMessage(sender, recipient, "Subject", "body1." + vbCrLf + vbCrLf + "body2:" + Dts.Variables("System::MachineName").Value.ToString + vbCrLf + "Execution by:" + Dts.Variables("System::UserName").Value.ToString + vbCrLf + "Package StartTime: " + Dts.Variables("System::StartTime").Value.ToString + vbCrLf + vbCrLf + strSuccessMessage)

   mySmtpClient =
    New SmtpClient(Dts.Variables("cvarSMTPServer").Value.ToString())
  mySmtpClient.Send(myHtmlMessage)

  Dts.TaskResult = Dts.Results.Success
  End Sub
End Class

Now you can send emails by executing the task!

More freebies to addon

If you would like to achieve even more, keep reading.

1. Attachments 
   
   In order to add attachment to emails, you need use    myHtmlMessage..Attachments.Add(New Attachment("c:\textfile1.txt"))
   

     

2. Users and Password
   
   The credentials returned by DefaultNetworkCredentials represents the authentication credentials for the current security context in which the application is running. mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
   
   If you don't want to use windows authentication to connect to your SMTP host and want to specify a different username and password, you can then use NetworkCredential class as shown below
   
    mySmtpClient.Credentials = New NetworkCredential(UserName, Password, Domain)

Stairway to Integration Service: Script Task

Another effective way to make SSIS packages dynamic is to write custom code that perform tasks you cannot perform with the built-in components. Here is a cookbook/cheatsheet that will speed up your learning of customized script tasks or components.

There are two important features you need to determine before you click "Edit Scripts" button. First of all, you need to add existing variables to the ReadOnlyVariables and ReadWriteVariables lists in the Script Transformation Editor or Script Task Editor to make them available to the custom script. Secondly, you need to determine which languages you would like to use: C# or Visual Basic.
Besides these, you need to add necessary libraries at the beginning of scripts. You can refer the article of Adding the Script Task to Your SSIS Packages for detailed steps.

Note that the following examples are all for 2008 version. This is not an exhaustive list. Please stay tuned for more tasks accomplished by Script Task in SSIS:

• How to: Send Email by Script Task in SSIS  
• How to: Import HL7 Messages by Script Task in SSIS 
• How to: Convert Weekday Number into Weekday Names by Script Task in SSIS

Problems	Expression
Get files' name from readable variables into String sfilePathname.	Solution 1: Dim sfilePathname As String sfilePathname = Dts.Variables("vFilePathName").Value.ToString Solution 2: Dim sfilePathname As String = Dts.Variables("vFilePathName").Value
Get the date when the file was created.	Dts.Variables("vFileDate").Value = File.GetLastWriteTime(sfilePathname)
Get dateStamp from filename if it contains date info at last 6 strings.	Dim dateStamp As String = sfilePathname.Substring(sfilePathname.LastIndexOf(("_")) + 1, 6)
Output value of dateStamp to ReadWriteVariables vYrMon.	Dts.Variables("vYrMon").Value = CInt(dateStamp)
Get substring of dateStamp.	Dim sYear As String = dateStamp.Substring(0, 4)
Get the system information from system variables.	strSystemErrorEmailFrom = Trim(Dts.Variables("System::MachineName").Value.ToString())
Send SMTP mails.	// Add "Imports System.Net.Mail" at the top Dim myHtmlMessage As MailMessag  Dim mySmtpClient As SmtpClient myHtmlMessage=New MailMessage(strSystemErrorEmailFrom, strSystemErrorEmailTo, "Subject", "body")     mySmtpClient =  New SmtpClient( Dts.Variables("cvarSMTPServer").Value.ToString() )     mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials     mySmtpClient.Send(myHtmlMessage)     Dts.TaskResult = Dts.Results.Success
Convert text to proper case (ex. 1st letter in each word is uppercase)	Row.OutputName = StrConv(Row.InputName, VBStrConv.ProperCase)
Build Event Handler for OnError Event.	Public Sub Main()         Dim arrErrorMessages As Collections.ArrayList         Try             arrErrorMessages = CType(Dts.Variables("objErrorMessages").Value, Collections.ArrayList)         Catch ex As Exception             arrErrorMessages = New Collections.ArrayList()         End Try         arrErrorMessages.Add("Error on Component:[" + Dts.Variables("SourceName").Value.ToString() + "]: " + Dts.Variables("ErrorDescription").Value.ToString())         Dts.Variables("objErrorMessages").Value = arrErrorMessages         Dts.TaskResult = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success End Sub
Get the filename from a full file path.	Solution 1: Dim fileName As String = "C:\mydir\test.txt" Dim result As String = Path.GetFileName(fileName) Console.WriteLine("GetFileName('{0}') returns '{1}'", fileName, result) Solution 2: RIGHT( @[User::FileName], FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ) - 1 ) //Results: GetFileName('C:\mydir\test.txt') returns 'test.txt'
Dynamically pre-truncate table.	sSQLScript = "DELETE FROM " + sTableName + vbCrLf + " WHERE " + sPKName + " > 0" Dts.Variables("SQLScript1").Value = sSQLScript
Determine a varible's value depending on a file name	If strFileName.Contains("BROUGHT").ToString Then         intFacilityId = "1" Else         intFacilityId = "0" End If
Get the filename without extension from a full file path.	Dts.Variables("vFileName").Value = Path.GetFileNameWithoutExtension(Dts.Variables("vFilePath").Value.ToString)
Split strings that delimited by "|".	Dim sAry AsString() = col1.Split("|")
Check file exists or not.	Solution 1: Dim directory As DirectoryInfo = New DirectoryInfo("c:\") Dim file As FileInfo() = directory.GetFiles("*.txt") If file.Length > 0 Then    Dts.Variables("User::FileExists").Value = True Else    Dts.Variables("User::FileExists").Value = False End If Solution 2: If (System.IO.File.Exists("C:\mydir\test.txt")) Then       Dts.TaskResult = ScriptResults.Success     Else       Dts.TaskResult = ScriptResults.Failure End If