Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Getting started with the SSIS Script Task


By:   |   Last Updated: 2016-03-18   |   Comments (6)   |   Related Tips: More > Integration Services Development

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

  1. SSIS installed
  2. SQL Server Data Tools Installed (SSDT) or BIDS (Business Intelligence Development Studio)
  3. A SQL Server database backup
  4. 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.

Open the SQL Server Data Tools for Visual Studio

Go to File > New > Project

Create a new Project in the SQL Server Data Tools for Visual Studio

Select Integration Services Project.

Select Integration Services Project in the SQL Server Data Tools

Drag and drop the Script Task to the design pane and double click on it.

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.

Script Task Editor

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 your code in the Main Section

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.

Save your project in Visual Studio

In the Script Task Editor, press OK.

Scirpt Task Editor in SSIS

Right click on the Script Task and select the Execute Task option.

Execute Task in Visual Studio

If everything is done correctly, you will receive the following pop-up message:

Hello World Pop-up Screen

Once finished, you can stop the package as shown below.

Stop Running the SSIS Package in Visual Studio

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.

Regions Section of Code

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.

Help Section for SSIS variables

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.

Sample Variable Code

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.

SSIS Script Task Execution

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.

DTS Script Task Error that is not really descriptive

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.

Error Message from the Try and Catch blocks of logic

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.

ReadOnlyVariable set to System::StartTime

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.

Execution Time

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.

SSIS Variables menu in SSDT

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.

NumberofCopies Variable setup at the Package level

Double click on the Script Task and select the new variable we created for the ReadOnlyVariables section as shown below.

NumberOfCopies Variable configured at the Script Task level

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;

Namespaces region of the code

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.

Final output of numerous backup copies
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:



Last Updated: 2016-03-18


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, April 22, 2016 - 4:14:18 PM - carlos Back To Top

 im using visual studio 2010 with sql 2008 r2 and i was create my own encryptation dll but when i add the reference and use my instance of the classes properly of my dll i receive this error

DTS Script Task has encountered an exception in user code: Project name: xxxxx Exception has been thrown by the target of an invocation

i put the try catch like your example but not change nothing i still receiving the same error and the try catch no works looks like the error comes in the part that you set using mynamespace;

 

can somebody help me

 

 


Friday, March 18, 2016 - 11:10:09 PM - Oded Dror Back To Top

Daniel,

Thank you

Oded Dror

 

 

 


Friday, March 18, 2016 - 4:23:20 PM - Scott Back To Top

There is one Dts.Variables collection that includes all package variables, including the parent package.  Variables in any scope of the package container heirarchy will mask any variables of the same name in higher-level scopes, only the most local version will be visible.  In other words, a User::FileName variable in the parent package is unreachable if there is a User::Filename variable in the child package.

You can refer directly to parent package variables by typing them in to the ReadOnlyVariables or ReadWriteVariables boxes in the Script Task Editor.  You can't use the popup with checkboxes for each variable, because at design time the editor has no idea which package it would be called from and can't list its variables.  If this is all set up correctly, you can refer to Dts.Variables["ParentPackageVariable"] exactly like local variables.

Another method is to use package configuration.  You can create a config item with a source of Parent Package Variable, enter the variable name, and use it to configure the value of a local variable.  One feature/advantage/source of confusion is that this way you can use the same variable name in both packages, the package configuration process knows it is reading from a parent package variable and writing to a child package variable so there is no ambiguity.


Friday, March 18, 2016 - 3:07:10 PM - Oded Dror Back To Top

 Daniel,

Yes I'm using SQL server 2012 with Visual Studio 2010.

My question is there any DTSx package you may have to illustrate that

I've try to follow so many articles but no result ( also what is the fuuly qualified parent variable name?) can you give me some examples

Also in Package configuration -> Parent package variable the Parent variable dosent seems to modify the child package variable

Thanks,

Oded Dror

 

 

 


Friday, March 18, 2016 - 11:20:18 AM - Daniel Back To Top

 

If you are running SQL 2012 or later versions, the parameters can be used for that purpose.

 


Friday, March 18, 2016 - 9:57:51 AM - Oded Dror Back To Top

Daniel,

Is there anyway to read variable from another package?

I have in my Master package MasterStartDate variable and I want to read this value from my Child pakage?

How do I achive this using script task or even local child variable to hold the value from the master?

 

Thanks,

Oded Dror  

 

 


Learn more about SQL Server tools