Using Script Task

While developing an ETL solution, the developer may be required to write some .net code to perform some functions\ operations that are not supported by built in connection types. These operations may include:

  • Interacting with Active Directory to fetch user information
  • Interacting with file system
  • Creating performance counters

To perform all such above mention functions, we need some editor where we can write our .net code. Script task provides us with such capability wherein we can write .net code and can read\ write SSIS variables (both system and user defined). SQL Server 2012 has introduced the concept of ‘parameters’. These parameters can also be accessed within Script task like variables.

In the following example, I wanted to check if the text file exists in the directory. There can be many other ways to perform this task but here I will make use of Script task. Also I will show you how to access SSIS parameters\ variables in Script task.

The flow of execution will go as: I will pass the directory path to script task via parameters. Script task will read that path and check if the text file exist. I the file exists then script task will return full path of the file including filename to SSIS otherwise it will return 0. This return value will be captured in a variable to perform further processing if required.

Let’s begin with the example:

  1. Go to parameter tab in SSIS and create a parameter as:

Untitled0

This parameter contains the directory path where my text file is located.

2. Create a variable that will contain the value returned by the script task as:

Untitled2

3. Drag the script task from the SSIS toolbox to Control flow window:

Untitled1

Control flow will look like:

Untitled3

4. Double click the script task to open Script task editor as:

Untitled4

ReadOnlyVariables: contain variables\ parameters that the Script task can read but cannot modify. Those variables that we don’t want the script task to modify are kept in this section.

ReadWriteVariables: contain variables that script task can read as well as can modify the variables and return it to SSIS.

5. Click  the ellipses next to ‘ReadOnlyVariables’:

Untitled5

Next, select the parameter that we want to pass to Script task by checking the box:

Untitled6

Similarly click the check box next to ReadWriteVariables and select the variable:

Untitled7

After performing above steps, Script task editor will look like:

Untitled8

Click the editor as shown in above screenshot to open .cs file.

6. Once the .cs file opens up, type the following code in Main function:

string filepath = Dts.Variables[“$Package::param_FilePath”].Value.ToString();

if(String.IsNullOrEmpty(Directory.GetFiles(filepath,”*.txt”).FirstOrDefault()))

{

Dts.Variables[“User::var_ReturnValue”].Value = “0”;

}

else

{

Dts.Variables[“User::var_ReturnValue”].Value = Directory.GetFiles(filepath, “*.txt”).FirstOrDefault();

}

Dts.TaskResult = (int)ScriptResults.Success;

Note the use of parameters and variables in Script task. Using ‘DTS.Variables’, we can read\ write SSIS variables\ parameters. Also, variable\ parameter names are Case Sensitive. Be careful while using variable\ parameter names.

7. Now save the file and build the script. After the Build succeed, close the file. You will land on the Script task editor. Click OK and the job is done.

8. Since you have the script task output in variable ‘var_ReturnValue’, as per requirements you can decide how to use it.

Happy Coding!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s