Problem
The SSIS Script Task allows you to add functionality to your SSIS package that does not already exist with the other predefined tasks. In this tip, we look at how to get started using the SSIS Script Task with a few examples.
Solution
The SSIS Script Task is one of the most interesting tools to increase SSIS capabilities. With the script task, you can program new functionality using C# or VB. This tip is for people with limited experience in SSIS and C#. If you have SSIS experience, but you do not how to use the Script Task this tip is also for you. The next tip will include more advanced features.
Requirements
- SSIS installed
- SQL Server Data Tools Installed (SSDT) or BIDS (Business Intelligence Development Studio)
- A SQL Server database backup
- You can use SQL Server 2005 or later versions. In this example, we are using SQL Server 2014.
Example 1 – Hello World
Let’s start with the Hello World example using a simple Script Task.
In order to start, open the SQL Server Data Tools for Visual Studio.
Go to File > New > Project
Select Integration Services Project.
Drag and drop the Script Task to the design pane and double click on it.
The following window will open. The ScriptLanguage is used to select which language to use, either C# or Visual Basic. EntryPoint is used to select where to start in the code, by default it starts in Main. The ReadOnlyVariables and ReadWriteVariables will be explained later. Press the Edit Script button to write your code.
A new Window will be displayed to allow you to write the code. Go to the main procedure, by default you will create your code there.
Add the following code in the Main section. This code will display a message with the Hello World message.
public void Main()
{
// TODO: Add your code here
MessageBox.Show("Hello World");
}
Save the code.
In the Script Task Editor, press OK.
Right click on the Script Task and select the Execute Task option.
If everything is done correctly, you will receive the following pop-up message:
Once finished, you can stop the package as shown below.
Example 2 – Help, Variables and Handling Errors
In this example, we will show how to work with variables and how to handle errors. Open the Script Task and press the Edit script button.
There are regions that can be expanded. For example, the Help introduction explains how to use the help.
By default, there are help samples to use SSIS variables, parameters, firing events and using the connection managers. In this tip, we will work with variables. In future tip, we will talk about firing events and the connection manager. Expand the help using the Integration Services variables tree as shown below.
There are samples to save SSIS variable values in a C# or VB variable. There are also samples to save values in an SSIS variable. There are also similar samples to work with parameters. Parameters are a new feature in SQL Server 2012 and they can work at the project level.
In this example, we are going to display the system time from a variable. Use the code below. The code is assigned to the C# variable named startTime from the SSIS system variable system::Starttime. The variable is converted from DateTime to Text using the ToString function and then displayed in a MessageBox. Finally, the TaskResult shows the task as a success (the green color in SSIS).
DateTime startTime = (DateTime)Dts.Variables["System::StartTime"].Value; MessageBox.Show(startTime.ToString()); Dts.TaskResult = (int)ScriptResults.Success;
Save the script and close it and then right click the Script Task and execute the task.
You will receive an error similar to this one: DTS Script Task has encountered an exception in user code: Project name: xxxxx Exception has been thrown by the target of an invocation.
By default, error messages are not really descriptive. That is why it is a good practice to use try and catch logic in your code. The try block contains the code to be executed and the catch code handles the exception. Stop the task and modify the code as follows:
try
{
DateTime startTime = (DateTime)Dts.Variables["System::StartTime"].Value;
MessageBox.Show(startTime.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
Save the script, close it and execute again. You will receive the following error message this time. The error message is now more descriptive. It says that the element cannot be found and this item is a variable. The error is that we did not include the variable in the Script Task.
Stop the task and double click on the Script Task to make some changes. Add the System:Starttime in the ReadOnlyVariables property. This variable is a system variable used to get the time when the package started to run.
Press OK on the Script Task and execute the task again. If everything is done correctly, a MessageBox will be displayed with the start time of the package as shown below.
Example 3 – User Variables and Loops
In this example, we are going to create a variable, assign a number and create several copies of a SQL Server backup. For example, if we assign a value of 100, it will create 100 copies. In order to start, we are going to create a new SSIS user variable. Go to the menu, SSIS > Variables as shown below.
Create a variable named NumberOfCopies of type Int32 (integer) and assign a value. In this example, we are specifying the number 2 to create two copies of the SQL Server backup.
Double click on the Script Task and select the new variable we created for the ReadOnlyVariables section as shown below.
Edit the code in the Script Task and expand the Namespaces region and add the System.IO namespace. This namespace is used to copy, replace, read and write files, show directory and file information as well as other functions:
using System.IO;
Add the following code to the script in the Main section which will create copies of the backup files:
try
{
int numberofcopies = Convert.ToInt16(Dts.Variables["User::NumberOfCopies"].Value);
for (int i = 1; i <= numberofcopies; i++)
{
File.Copy(@"C:\scripts\db1backup.bak", @"C:\scripts\db1backupcopy" + i.ToString() + ".bak");
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
The code stores the values of the SSIS variable in a C# variable. We use the Convert.ToInt16 function to convert the SSIS variable value to a integer in C#. The for (int i = 1; i <= numberofcopies; i++) is a loop used to copy the backup a specified number of times defined by the NumberOfCopies value. If the NumberOfCopies is 100, it will create 100 copies. Finally, the File.Copy function will create copies with a number at the end of the file name. For example, if I create 3 copies the files will be named db1backupcopy1.bak, db1backupcopy2.bak, db1backupcopy3.bak.
Save the code and close it and accept the changes and execute the Script Task. As you can see, two copies of the backup were created.
Conclusion
The Script Task is a very useful and powerful tool to accomplish whatever you need in your daily tasks. In this tip we learned how to work with variables, how to handle errors using try … catch and how to use loops.
Next Steps
Here are some links that will be useful to you:
