SSIS - DTS ActiveX Script Task to Scripting Task
How To Update a DTS ActiveX Script Task to a SSIS Scripting Task
This tutorial describes how to translate the FileSystemObject, ADODB connection and ADODB recordset and capture global variables of a DTS ActiveX Script Task into an SSIS Script Task using C# programming.
DTS ActiveX Script Conversion to SSIS Script Background
Following several unsuccessful attempts at getting my ActiveX Script tasks to function in SSIS (global variables issues, connection string problems, etc., etc.), I resigned myself to jumping into SSIS Script Tasks and translating my code into C#. For additional information on migrating a DTS Package to SSIS, click here: DTS to SSIS Migration.
DTS Script Converion Tasks to SSIS
Step 1: Reassess the ActiveX Script Task.
The image below is of an Active X Task from DTS - SQL Server 2000. It uses a Global Variable passed via the legacy exe dtsexec (called from a stored procedure which was used as a step in a SQL Server Job that executed each day), and ADODB objects to connect and retreive a file name and path in which to copy and modify files using the FileSystemObject. On Fridays, a dated version of the report is placed in a different folder on the server for delivery to a client.
Step 2: Create a New Package in SSIS and Add a Script Task
Drag and drop the Script Task from the Control Flow Items in the Toolbox.
Step 3: Create Global Variables for the Package
In the Menu Bar, go to SSIS, Variables to show the Variables in the project. Click on the New Variable button to Add a new variable and rename it, select the data type and value, if required.
Right click on the Script task and click on Edit. The Window has Read Only and ReadWrite variables. Click on the button with an ellipsis (3 dots) and select the variables to include in the script.
Check the box next to your variable name. In this case it is myString (a User defined variable).
Click OK and return to edit the script.
Step 4: Rewrite the ActiveX Script code into C# code.
Add code to replace the FileSystemObject and the ADODB objects by placing "using" statements at the top of the file.
using System.IO;
using System.Data.SqlClient;
Next, oFSO (FileSystemObject) will become a FileInfo object. The ADODB objects will become SqlCommand, SqlConnection and SqlDataReader objects.
a) Rewrite the DTSGlobalVariable using the C# DTS class into an object.
string varDBName;
string varPath = "";
string varXLSPath;
object oDBName = Dts.Variables["myString"].Value;
varDBName = oDBName.ToString();
b) Create the ADODB objects as SqlConnection (Connection) and SqlCommand and SqlDataReader (Recordset) objects. The task to be performed is to read the cValue (a network file path) from a table in a database.
string sql =
@"Data Source=SQLSERVER2008R2;
Initial Catalog=" + varDBName +
";Integrated Security=SSPI;";
//open the connection using the string
SqlConnection cn = new SqlConnection(sql);
cn.Open();
SqlCommand cmd =
new SqlCommand("Select cValue FROM tblConfig WHERE cKey = 'FilePath'", cn);
//open the recordset
SqlDataReader sr = cmd.ExecuteReader();
if (sr.HasRows)
{
while (sr.Read())//read the recordset data into the variable
{
varPath = sr[0].ToString();
}
}
else
varPath = "";
sr.Close();
c) Create the FileSystemObject as a FileInfo class object and copy the file to the new location provided by the recordset.
FileInfo oFSO = new FileInfo(@"C:\Data\Book1.xls");
varXLSPath = @"C:\Data\Book1.xls";
if (varPath != "")
{
sDestinationFile = @"C:\Data\" + varPath;
oFSO.CopyTo(sDestinationFile, true);
}
Here is the code for the Script Task put together using a try\catch block to capture any exceptions and pass back a Dts.TaskResult of Failure or Success, if no exceptions occur..
public void Main()
{
FileInfo oFSO = new FileInfo(@"C:\Data\Book1.xls");
string sSourceFile;
string sDestinationFile;
string varDBName;
string varPath = "";
string varXLSPath;
object oDBName = Dts.Variables["myString"].Value;
varDBName = oDBName.ToString();
varXLSPath = @"C:\Data\Book1.xls";
try
{
string sql = @"Data Source=SQLSERVER2008R2;Initial Catalog=" + varDBName +
";Integrated Security=SSPI;";
SqlConnection cn = new SqlConnection(sql);
cn.Open();
SqlCommand cmd =
new SqlCommand("Select cValue FROM tblConfig WHERE cKey = 'FilePath'", cn);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.HasRows)
{
while (sr.Read())
{
varPath = sr[0].ToString();
}
}
else
varPath = "";
sr.Close();
if (varPath != "")
{
sDestinationFile = @"C:\Data\" + varPath;
oFSO.CopyTo(sDestinationFile, true);
}
DateTime dt = DateTime.Now;
string sub = String.Format("{0:MM-dd-yyyy}", dt);
if (dt.DayOfWeek == DayOfWeek.Friday)
{
varXLSPath =varXLSPath.Replace(@"\Data2\", @"\Data2\Weekly Reports\");
varXLSPath = varXLSPath.Replace(@".xls", sub + ".xls");
sDestinationFile= varXLSPath;
oFSO.CopyTo(sDestinationFile, true);
}
cn.Close();
oFSO = null;
sr = null;
cn = null;
}
catch (Exception ex)
{
Dts.TaskResult = (int)ScriptResults.Failure;
MessageBox.Show(ex.Message);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
A test of the package shows success. For more information on DTEXEC, please click here: Link to DTEXEC.
With a few modifications to the ActiveX script into C#, a DTS package can be converted into an SSIS package for SQL Server 2008. (Note: This conversion is still possible in SQL Server 2005, but only available in Visual Basic .NET)