Creating packages in code – Execute SQL Task
The Execute SQL Task is for obvious reasons very well used, so I thought if you are building packages in code the chances are you will be using it. Using the task basic features of the task are quite straightforward, add the task and set some properties, just like any other. When you start interacting with variables though it can be a little harder to grasp so these samples should see you through. Some of these more advanced features are explained in much more detail in our ever popular post The Execute SQL Task, here I’ll just be showing you how to implement them in code. The abbreviated code blocks below demonstrate the different features of the task. The complete code has been encapsulated into a sample class which you can download (ExecSqlPackage.cs). Each feature described has its own method in the sample class which is mentioned after the code block. This first sample just shows adding the task, setting the basic properties for a connection and of course an SQL statement. Package package = new Package();
// Add the SQL OLE-DB connection
ConnectionManager sqlConnection = AddSqlConnection(package, "localhost", "master");
// Add the SQL Task
package.Executables.Add("STOCK:SQLTask");
// Get the task host wrapper
TaskHost taskHost = package.Executables[0] as TaskHost;
// Set required properties
taskHost.Properties["Connection"].SetValue(taskHost, sqlConnection.ID);
taskHost.Properties["SqlStatementSource"].SetValue(taskHost, "SELECT * FROM sysobjects");
For the full version of this code, see the CreatePackage method in the sample class. The AddSqlConnection method is a helper method that adds an OLE-DB connection to the package, it is of course in the sample class file too.
Returning a single value with a Result Set
The following sample takes a different approach, getting a reference to the ExecuteSQLTask object task itself, rather than just using the non-specific TaskHost as above. Whilst it means we need to add an extra reference to our project (Microsoft.SqlServer.SQLTask) it makes coding much easier as we have compile time validation of any property and types we use. For the more complex properties that is very valuable and saves a lot of time during development. The query has also been changed to return a single value, one row and one column. The sample shows how we can return that value into a variable, which we also add to our package in the code. To do this manually you would set the Result Set property on the General page to Single Row and map the variable on the Result Set page in the editor.
Package package = new Package();
// Add the SQL OLE-DB connection
ConnectionManager sqlConnection = AddSqlConnection(package, "localhost", "master");
// Add the SQL Task
package.Executables.Add("STOCK:SQLTask");
// Get the task host wrapper
TaskHost taskHost = package.Executables[0] as TaskHost;
// Add variable to hold result value
package.Variables.Add("Variable", false, "User", 0);
// Get the task object
ExecuteSQLTask task = taskHost.InnerObject as ExecuteSQLTask;
// Set core properties
task.Connection = sqlConnection.Name;
task.SqlStatementSource = "SELECT id FROM sysobjects WHERE name = 'sysrowsets'";
// Set single row result set
task.ResultSetType = ResultSetType.ResultSetType_SingleRow;
// Add result set binding, map the id column to variable
task.ResultSetBindings.Add();
IDTSResultBinding resultBinding = task.ResultSetBindings.GetBinding(0);
resultBinding.ResultName = "id";
resultBinding.DtsVariableName = "User::Variable";
For the full version of this code, see the CreatePackageResultVariable method in the sample class. The other types of Result Set behaviour are just a variation on this theme, set the property and map the result binding as required.
Parameter Mapping for SQL Statements
This final example uses a parameterised SQL statement, with the coming from a variable. The syntax varies slightly between connection types, as explained in the Working with Parameters and Return Codes in the Execute SQL Taskhelp topic, but OLE-DB is the most commonly used, for which a question mark is the parameter value placeholder.
Package package = new Package();
// Add the SQL OLE-DB connection
ConnectionManager sqlConnection = AddSqlConnection(package, ".", "master");
// Add the SQL Task
package.Executables.Add("STOCK:SQLTask");
// Get the task host wrapper
TaskHost taskHost = package.Executables[0] as TaskHost;
// Get the task object
ExecuteSQLTask task = taskHost.InnerObject as ExecuteSQLTask;
// Set core properties
task.Connection = sqlConnection.Name;
task.SqlStatementSource = "SELECT id FROM sysobjects WHERE name = ?";
// Add variable to hold parameter value
package.Variables.Add("Variable", false, "User", "sysrowsets");
// Add input parameter binding
task.ParameterBindings.Add();
IDTSParameterBinding parameterBinding = task.ParameterBindings.GetBinding(0);
parameterBinding.DtsVariableName = "User::Variable";
parameterBinding.ParameterDirection = ParameterDirections.Input;
parameterBinding.DataType = (int)OleDBDataTypes.VARCHAR;
parameterBinding.ParameterName = "0";
parameterBinding.ParameterSize = 255;
For the full version of this code, see the CreatePackageParameterVariable method in the sample class.
You’ll notice the data type has to be specified for the parameter IDTSParameterBinding .DataType Property, and these type codes are connection specific too. My enumeration I wrote several years ago is shown below was probably done by reverse engineering a package and also the API header file, but I recently found a very handy post that covers more connections as well for exactly this, Setting the DataType of IDTSParameterBinding objects (Execute SQL Task).
/// <summary>
/// Enumeration of OLE-DB types, used when mapping OLE-DB parameters.
/// </summary>
private enum OleDBDataTypes
{
BYTE = 0x11,
CURRENCY = 6,
DATE = 7,
DB_VARNUMERIC = 0x8b,
DBDATE = 0x85,
DBTIME = 0x86,
DBTIMESTAMP = 0x87,
DECIMAL = 14,
DOUBLE = 5,
FILETIME = 0x40,
FLOAT = 4,
GUID = 0x48,
LARGE_INTEGER = 20,
LONG = 3,
NULL = 1,
NUMERIC = 0x83,
NVARCHAR = 130,
SHORT = 2,
SIGNEDCHAR = 0x10,
ULARGE_INTEGER = 0x15,
ULONG = 0x13,
USHORT = 0x12,
VARCHAR = 0x81,
VARIANT_BOOL = 11
}
Download
Sample code ExecSqlPackage.cs (10KB)
