With the introduction of C#, and the embedding of the new Visual Studio Tools for Applications into SSIS, you can ’ t think of using the Script Task and Script Component as scripting anymore; now it ’ s all – out programming. Typically, you ’ d code logic into these Script Tasks to control the
execution and logic flow within a package or to perform some specialized business validation.

The two Scripting Components provide access into a new scripting development environment using Microsoft Visual Studio Tools for Applications (VSTA). This change finally allows us to script logic into packages using Visual Basic 2008 or Visual C# 2008 .NET code.

It is not necessary to know the C# universe in order to write valuable C# script tasks and components . Keep in mind that it is being used as a scripting language, not a full-fledged application development language.

The ability to perform the following basic operations will allow you to do quite a lot.

• Access SSIS variables
• Modifying SSIS variables
• File validation Task (File properties task)
• Read/write ASCII files
• Parse strings with the split function
• Create and loop through list arrays
• Copy, move and delete files
• Capture a listing of specified files in a sub directory
• Create a database connections to SQL Server
• Execute T-SQL queries and stored procedures and capture any results

Our first task is  accessing  variables

1. Access SSIS variables

create a variable

drag and drop a script task in control flow

Double click on the variables and use the below shown settings

click on edit script ,once you click the edit script button a window pops pup (Microsoft Visual Studio Tools for Applications (VSTA))

Result

as

Code:

string str = null;

Dts.VariableDispenser.LockForRead(“User::user”);

str = (string) Dts.Variables[“user”].Value;

MessageBox.Show(str);
Dts.TaskResult = (int)ScriptResults.Success;

2. Modifying variables in SSIS using script task

click on the script task and remove the variable “user” as read only variable and assign it to the read write variable

Modifying the variable

string str = null;

Dts.VariableDispenser.LockForWrite(“User::user”);

str = (string)Dts.Variables[“user”].Value;

MessageBox.Show(str,”variable value before changing “);

Dts.Variables[“user”].Value = “Sql junkie share”;

str = (string)Dts.Variables[“user”].Value;

MessageBox.Show(str,”variable value after changing “);

3. File Validation Task 

In any of the ETL processes when dealing with flat files, some times there will be a need to check the existence of the file so lets create a simple file validation task using C#

create a  variable “file exits” in  control flow and assign default value as False

as

Click the button Edit Script

Script

File exists

File did not exist

Success

Script Component

1. Read/write ASCII files
2. Parse strings with the split function

we will use the earlier package , in this section  we will learn how to create a flat file source connection using script component and read the contents of the flat file and push the columns into the data flow task and  parse the contents of the text using split function

Replace the file exists part with a Data flow task , in the data flow task drag and drop a script component , click on the script component and select the component type as Source

and the flat file we will be dealing with here  looks like this

1,john,apt#3 – California USA\
2,tom,st 112 ca;lifornia USA

Task 

• Read the flat file
• Separate the columns which are delimited by ‘,’
• and parse the address column and remove the special characters (apt#3 – California USA\) to (apt 3  California USA)

back to the source component

and in the script section select the scripting language as C# and click the button edit script

Code:
using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Text;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

public override void CreateNewOutputRows()
{

StreamReader reader = new StreamReader(“D:\\New Text Document.txt”);

string line;

while ((line = reader.ReadLine()) != null)
{
string[] columns = line.Split(‘,’);

this.Output0Buffer.AddRow();
if (columns.Length > 0)
{
Output0Buffer.id = (columns[0]);
}
if (columns.Length > 1)
{
Output0Buffer.name = (columns[1]);
}
if (columns.Length > 2)
{

string[] split = columns[2].Split(new Char[] { ‘#’, ‘,’, ‘.’, ‘:’, ‘-‘, ‘\\’, ‘;’ });

Output0Buffer.address = ConvertStringArrayToString(split);

}
}
}
static string ConvertStringArrayToString(string[] array)
{
//
// Concatenate all the elements into a StringBuilder.
//
StringBuilder builder = new StringBuilder();
foreach (string value in array)
{
builder.Append(value);
}
return builder.ToString();
}
}

Output