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
- 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
- Read/write ASCII files
- 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
in the above script task how can i find the number of rows?
I need a Script Task (in control flow) to count the number of rows in my file using C#. Can you help?