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:
- Go to parameter tab in SSIS and create a parameter as:
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:
3. Drag the script task from the SSIS toolbox to Control flow window:
Control flow will look like:
4. Double click the script task to open Script task editor as:
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’:
Next, select the parameter that we want to pass to Script task by checking the box:
Similarly click the check box next to ReadWriteVariables and select the variable:
After performing above steps, Script task editor will look like:
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!!
ssisblog 