Script Task
In SQL Server Integration services (SSIS), it may not be possible to meet all the business requirements using built-in tasks. In these cases, we can achieve the functionality using C# or VB.net code in script task. The script task is an ideal alternative solution to built-in tasks.
Script Task Vs Script Component
Both the Script Task and the Script component have the provision to extend the default functionality using C# or VB.Net. The script task is used in the control flow as a task. Whereas the script component has been used in the data flow as a source, destination or a transformation.
Following are few examples which describe the power of script task
- Querying Active Directory service Interface (AD) from SSIS to extract the list of membership groups for a given user id
- Filter source data files based on a number of criteria (size of the file, date and contents of the file, etc.)
Though there are many advantages of using the script task, it is usually undermined by the developers because of the complexity to debug and fix the issues.
Script Task Debugging
In this blog post, I have consolidated a number of techniques to debug the script task. These debugging techniques have been classified into two groups.
- Design time debugging techniques
- Run time debugging techniques
Design Time Debugging
This method is suitable while you are developing a package. You will be using the SSDT to debug and investigate.
Run Time Debugging
Run time analysis is useful, once the package has been deployed and scheduled to run at a specific time. In this case, you will not be able to use the SSDT to debug the package. Hence we need to use other alternative techniques to understand the procedure during execution.
Solution overview
To explain multiple debugging facilities, I have created a sample SSIS package with one script task. This script task accept one user variable (Filecount).
The below mentioned image represents the sample package at high level.
Using this sample package, we will be exploring the debugging techniques. There are four debugging methods are available.
- Using Message Box to print values
- Using Breakpoint options for debugging
- Using Logging for the Script Task
- Using Try/Catch within Script Task
The Message Box and Breakpoint options are suitable for design time debugging. The logging and “Try catch” methods are ideal to debug during the run time.
1. Message Box
In this technique, we will be using the Message Box (Dialogue Box) to show the value of the variable or a string in the screen. By displaying a string or a variable, the developer will be able to debug. This can be achieved by using the code below.
Messagebox.show(string value)
This is very primitive level debugging to know the details. The below mentioned picture represents the code within the script task to use the Message Box.
During the execution the SSIS will show a message box with a value as per the picture below.
2. Breakpoint
Setting a breakpoint will help us to stop the debugger during execution. This will help us see the value of the variable at the time of execution. Break point can be set by placing the cursor on a line and pressing the function key F9. Once set, the debugging line will be identified using the red dot in the left margin. The defined breakpoints can be managed in the VSTA and it can be removed from the “Set Breakpoints” dialog box in SSDT designer.
The below mentioned picture highlight the defined breakpoint in the script task.
The “Set Breakpoints” dialog box shows the breakpoints in the script task. The breakpoints will be listed in the bottom of the dialog box with the line number and the name of the function. At run time, the visual studio for application (VSTA) will open the script task in read only mode.
Now the breakpoint line will be highlighted and the execution will be paused.
In the above mentioned picture, we are reading the value of the user variable “Filecount” and assign it to local variable “Filecount”. The value of the variable will be derived and will be displayed in the message box. Now the value of the variable can be evaluated by placing the cursor over the variable.
The value of the variable can also be printed in the immediate window as well.
Failure Scenario
The main purpose of the debugging is to explore and understand the cause of the failure. So let’s have a look at a sample failure scenario.
To explain the failure scenario, I have purposely misspelled the variable as “user”, instead of “User”. Hence I am expecting the script task to fail, while evaluating the value of the variable.
In the below mentioned picture, the line has been hit during the execution.
As the variable collection has been misspelled, it is expected that the exception will be thrown.
The exception has been caught by the VSTA and details are displayed in the dialog box as per the picture below.
Success Scenario
Once the variable has been corrected (as “User”), the same line has been executed successfully. Now the value of the variable has been displayed when you place the cursor over the variable. The below mentioned picture represents the successful execution and the value of the variable.
The value of the “Filecount” variable can also be printed in the command window. This can be achieved by using the expression “?Filecount”. The value will be calculated and displayed on the next line.
Run Time Debugging
While the above mentioned solutions will help us to evaluate the variable during development, but this may not help during run time. As the SSIS package will be executed without manual intervention, we need other way to capture the value for post mortem analysis.
This can be achieved in two ways and these methodologies have been discussed below with some samples.
3. Enable Logging
Logging in Integration services will help us to record details such as execution progress and results. SSIS can be used to log, predefined events or the user defined messages. The Log method on the Dts object can be used to log user defined messages. To make use of the logging in the script task, the logging for the script task should be enabled. In addition the “ScriptTaskLogEntry” event should be selected on the “Configure SSIS logs” dialog box. The below mentioned steps will help us to enable and select a suitable log provider.
In the below mentioned picture, the logging has been enabled with the SQL server log provider.
Once the provider has been selected, it is mandatory to select the detailed the event “ScriptTaskLogEntry” as per the picture below.
The Log method of Dts object will be called and the user defined message will be passed with the value of the variable. The actual code for logging can be seen below.
Querying log table
After a successful execution of the script task, the SSIS has logged the user defined message in the log table. Now let’s query the SysSSISLog table for the script tasks log entry. The results of the query can be seen below.
4. Try catch / Error Handling
Though you programmed and planned for all circumstances, unexpected exceptions do occur over a period of time. It is possible to handle the unexpected exception in the code.
In script task, the unexpected exception can be captured using the try catch block. As per the picture below, the code should be placed in the try block.
If an exception has been found, then the call stack will be redirected to the catch block. The details of the exception will be available in the catch block. The exception will be caught using and the stack can be accessed using the properties.
As the exception has occurred, we need to inform the package that script task has failed. This can be done by raising the event Fire.Error in the Dts object. This event details can be logged for future purposes.
The below mentioned picture represents the log entry for the try catch block.
In addition to log entry the details of the error can also be found on the progress windows as per the image below.
Summary
We have seen few methods to debug the code in the script task. Debugging the code in SSIS is an art. I recommend you to try it out all the methods and see which one fits better in your case.
Reference
- Logging in the Script Task
- How to: Debug a Script by Setting Breakpoints in a Script Task
- MessageBox.Show Method (String, String, MessageBoxButton)
- Script Task Debugging in SQL Server Integration Services (SSIS) - December 29, 2016
He is passionate about SQL Server, SSIS, SSAS, SSRS and MDX. He has presented at SQLBits, SQLPass and the SQL London User Group. He has special interest towards Continuous Integration, Continuous Delivery and Deployment automation for the SQL BI (SSIS, SSAS and SSRS) stack.
View all posts by Nat Sundar