Execute SQL Task

The Execute SQL task is probably not one of the more advanced SSIS tasks, but it is one of the most used task in SQL Server 2000 DTS. You'll use it as a jumping-off point to cover variables, expressions, and some advanced techniques of runtime property management.

The Execute SQL task is a task often used to truncate staging data table prior to import. Another common use is to call stored procedures to take on the task of performing complicated business logic that couldn't be modeled in the DTS package environment. The SSIS version can provide these same services, but now with a better configuration editor and methods to map stored procedure parameters and to read back result and output values.

Create an SSIS package and drag an Execute SQL task on the Control Flow design surface. The control will show a red icon on the task, and an error message appears in the error list to inform you that there is no connection associated with this task. You'll have to open up the Execute SQL task editor and set that up to satisfy that error condition. Double-click the task, and your editor should look similar to Figure 6-1.

Figure 6-1

The task has four sections in the Execute SQL task editor:

• General: Contains the properties to allow the setting of the task name, description, connection-related settings, setting to indicate how to execute the SQL statement, and the SQL statement to execute.

• Parameter Mapping: Contains a collection of parameters that can be matched to package- or container-level variables. Parameters allow the ability to provide variable input to a stored procedure or a prepared SQL statement at runtime.

• Result Set: Contains the collection of mapped variables to values returned in the row set formed as a result of executing the SQL statement in the SQLSourceProperty.

• Expressions: Contains the collection of expressions that can be used to configure properties of this task.

The General tab contains the core properties of the task. They include the following:

• ConnectionType and Connection: These properties are connection-related settings such as the type and name of the connection. (Providing the type of connection allows the component to validate the type of connection against the selected connection.)

• SQLStatement: This property holds the SQL statement for the task to execute. This statement can be a simple SELECT statement, the content of a large SQL script separated with GO statements, or a call to a stored procedure.

• SQLSourceType: This property is a new addition from the old DTS SQL task. It provides the option of populating the SQLStatement property at development or loading at runtime using either a variable or a file that contains a SQL script.

• ResultSet property: This property can be set to expect as a result of executing the SQL statement either no result set, a singleton result set, a full result set (multiple rows), or an XML string. Setting the property to NONE will deactivate the Result Set tab from accepting result set mappings.

There are plenty of examples in Books Online and elsewhere about setting up and configuring input parameters with in-line SQL and stored procedures as well as demonstrations using the task to load and execute SQL scripts using the File Connection property SQLSourceType. The example will demonstrate how to configure this task to retrieve Output parameters from stored procedures.

However, before you jump right into the example, you need to understand how SSIS design-time engines deal with variables, and you need to understand the power of something new to DTS: expressions.

Variables

In DTS 2000, all variables were basically global. In SSIS, variables have scope, starting with package scope (that works almost like a global) and ending with scope at a task or transformation task level. Variables can be created with separate namespaces (not shown in Figure 6-2). The default namespace is User. Variables are also case-sensitive. Why does all this matter? Because you can create some logic bugs if you aren't paying attention. Figure 6-2 shows an example of two variables named sSQL coexisting in a package.

Figure 6-2

The variable in the Execute SQL task will override the package-level variable, and the package-level variable value will take over after the task completes. It seems like common sense when you think about it, but even then it is easy to create scoped variables accidentally. You'll need to pay careful attention to the scope column when you create a variable. The scope is automatically provided based on the object you currently have selected in BIDS when you create the variable. Adding a variable while in a container, on a task, on a transform, or even in the Data Flow design surface creates local object-level scope variables. If you are on the package design surface, then a variable is created by default at the scope level of the package.

Why talk about variable scope in a chapter on advanced task and transformations? Because the old method of using the ActiveX task to set connection or other package properties is really not necessary anymore. Even though this method provided a great way to change properties at runtime, the whole way of doing things in SSIS has changed. First, there is a whole set of configuration tools that allow packages to be configured using XML files, environmental variables, and even registry settings. These techniques are much more manageable for multiple-environment deployments when the package settings are static and are known prior to runtime. However, if you retrieve settings from your input streams or need to alter a property using information provided during runtime, configurations are not going to help you. Three examples of using nonstatic configurations are calling packages with parameters, changing an output file name to a sequential naming scheme, or setting a connection property during execution. To do any of these three, you'll have to use a technique that is going to involve variables.

Variables can store values retrieved in one task and then can be passed to other tasks. Variables can be used to set properties of most Integrated Service (IS) components by using a new idea to SSIS called Property Expressions. Variables allow the setting and configuration of component tasks that use these values to exchange bits of information: flags, counts, or strings settings that control settings of the package itself. With this level of flexibility, you are going to be using a lot more variables than ever before.

Expressions

Most IS tasks, transforms, and containers have a collection of potential property expressions — this even includes the package itself. The expressions collection is designed to expose container properties to be set by a logical expression. An Execute SQL Task property that is exposed by an expression is the SQLStatementSource property, which is the SQL statement to be executed. Another property, ConnectionString, exposes the OLE DB connection string for an OLE DB connection object. To review the available properties exposed for any container, click on the container and view the property named "expressions" in the property window. A click on the ellipsis will bring up the property expressions editor. (Make sure the property window has refreshed and your intended container is displayed in the window.)

Figure 6-3 shows the property expressions editor for an Execute SQL Task property with a partial set of properties exposed in the drop-down view.

Figure 6-3

There are two columns in the property expressions editor: one for selecting the property and another for defining an expression that can be evaluated. The result of the evaluated expression will be substituted into the value of the property at runtime.

Creating an expression starts with clicking on the ellipsis in the expression column. Figure 6-4 is an example of the Expression Builder. This tool guides you through building a logic statement using items such as literals, system and user variables, columns, operators, and built-in functions. The operators and functions in the expression language will be familiar to C#, C, and TSQL programmers, as they are similar to all three — but not exactly. Visual Basic brothers and sisters will need to remember things like == mean "equals" and && means "AND." TSQL brothers and sisters will need to wrap literals in double quotes instead of single. The expression language is not case-sensitive, so the C# and C programmers can do a little extra work keeping track of uppercase and lowercase variables that won't matter. Spend a little time with the expression builder and you'll quickly work out the differences as compared to your preferred languages.

Figure 6-4

Now continue with an Execute SQL task using the two differently scoped variables discussed in the section on variables. Figure 6-4 shows the Expression Builder opened up for the SQLStatementSource property, and the variables node has been expanded to show all the available variables for this current expression.

You must click on the variable User::sSQL and drag it onto the expression window to create the expression. The value of that expression will be substituted into the property SQLStatementSource at runtime and will become the statement that is executed in the task. To find out the evaluated result of this variable, click the Evaluate Expression button on the lower left. Notice that the evaluated value displayed is "SELECT 2." In the variables section of this chapter, you looked at two variables with the same name "sSQL" but with different scope. The variable scoped to the Execute SQL task with the value of "SELECT 2" takes precedence inside this task. Although this was a quick introduction to variables and expressions, you will be using them heavily during this chapter and in more advanced ways. See Chapter 7 for more information and detail on using expressions.

Using SQL Output Parameters to Change Runtime Settings

Now you will return to the stored procedure output parameter example. In this example, you will set up a simple export of the [HumanResources].[Shift] table from the AdventureWorks database but change the destination file at runtime. You will use the Execute SQL task to retrieve the new export location from an output parameter.

Your client requires you to integrate SSIS packages with a proprietary database that contains all package and software settings. This database sits on each environment: development, quality control, and production. Settings are moved into the different environments during turnover with your packages. When the package loads, all settings should be pulled from the database at runtime using a set of standardized stored procedures.

Since you have to retrieve your settings at runtime and you have to interact with a set of stored procedures to retrieve the settings, the configuration options in SSIS are not available. (Remember, these options can only set up the package before the package is processed in runtime.) You do have expressions that can help you out, so see if you can come up with a solution.

1. If you don't have an SSIS package open with an Execute SQL task, set up one now.

2. Add a package-level variable by right-clicking on the Control Flow design surface and selecting Variables. Name the variable "MyFile." Set the data type to string and set the value to c:\Execute SQL Sample.txt.

   Note

   Don't put quotation marks around the contents of string variable values. Think of the value in the variables collection as being the result of the variable assignment.

3. To simulate the proprietary stored procedure that you are required to use, you need to create it. For simplicity, you'll put your sample retrieval proc in the same database. Run the following script in the AdventureWorks database on the server. This script will create the procedure that will allow you to retrieve your connection.

    USE adventureworks go CREATE PROC dbo.usp_GetConfigParamValue (         @ApplicationName   Varchar(30),        -- the name of the application.         @ParameterName     Varchar(50),        -- the name of the parameter         @ParameterTypeName Varchar(30),        -- the name of the parameter type         @ParameterValueVar Varchar(255) OUTPUT -- output buffer for value         ) AS       Set NOCOUNT ON       --Dummy proc to simulate the real usp_GetConfigParamValue       --Always outputs 'c:\ Execute SQL Sample Changed.txt'       SET @PARAMETERVALUEVAR='c:\Execute SQL Sample Changed.txt'       Set NOCOUNT OFF 

4. Set the ConnectionType property of the Execute SQL Task to ADO.NET using the .NET SQL Client provider. Then select <New Connection> from the Connection property. Create an ADO.NET connection object using the server name "." for the local machine. Use the dropdown to select your SQL Server instance and leave the default as NT Authentication. Select the database "AdventureWorks." Save this connection.

   Note

   An OLE DB connection for this example will not work properly. Each provider will handle stored procedure parameters differently.

5. Set the Execute SQL SQLStatement property to the following:

    EXEC usp_GetConfigParamValue 'MYAPP', 'MYPARM', 'STRING', @MYVALUE OUTPUT 

6. In the Parameter Mapping tab, add a mapping to the variable named MyFile to the parameter in the SQLStatement @MyValue by setting the variable name to User::MyFile, the direction to Output, the data type to String, and the parameter to MyValue. Click OK to save settings.

7. To finish out the package, drop a Data Flow task onto the Control Flow surface. Connect the output of the Execute SQL task to the Data Flow task.

8. In the Data Flow design surface, add a DataReader Source and a Flat File Destination.

   Note

   Make sure you don't add a Flat File Source. The source and destination transforms are at first easy to get confused.

9. Configure the DataReader Source to use the AdventureWorks connection and set SQLCommand property to the following:

    Select * from [HumanResources].[Shift] 

10. Connect the DataReader transformation to the Flat File Destination.

11. Configure the Flat File Destination by creating a new connection to a delimited file in the Destination File editor. Set the file name to "c:\myfile.txt." Click the Mappings tab to populate the default mapping and accept the rest of the defaults.

12. What you want to do is show that you can change the Flat File Destination at runtime. Right now this package is set up to pull all records from the [HumanResources].[Shift] table and dump to C:\Execute SQL Sample.txt in a delimited format. The Execute SQL task retrieves a string "C:\Execute SQL Sample Changed.txt" from the stored procedure. You want this file name to change at runtime. To do this, you're going to need an expression that changes the ConnectionString property on the Flat File Connection.

13. Locate and click on the Flat File Connection Manager — not the Flat File Destination. In the property window, locate the Expressions property and click the ellipsis to add an expression to a property. The property you want evaluated at runtime is ConnectionString. Use the Expression Builder to select the variable @[User::MyFile] or type this in. This expression will be evaluated at runtime and will change the ConnectionString property to the value stored in the variable MyFile.

Run the package and check your local drive. You should see the file "Execute SQL Sample Changed.txt" in the root of the c:\ drive. A quick look at the package execution steps shows that the output is changed and directed to the new location. Remember, you originally set the file to "Execute SQL Sample.txt."

This technique is used to output a flat file to one location in the development environment and then to another location in the production environment using a proprietary set of database utilities that store and retrieve application settings. An advantage to this method is that once the settings have been moved into the different environments, packages can be altered while testing and moved into production without having to keep track of all the embedded settings. More importantly, the example highlights the power of SSIS expressions and variables and demonstrates the use of output parameters using the ADO.NET provider. There are several things to notice that you may have passed over quickly:

• If your data provider doesn't recognize OUTPUT parameter by name or position, a workaround is to have the stored procedure return a result set and map the results to variables. If you can't modify the stored procedure, you can use the old-school method of executing and retrieving the value of the output variable in the SQLStatement and mapping the variables. The following would be the SQLStatement property setting for the preceding example:

   DECLARE @MYVALUE AS VARCHAR(255) EXEC usp_GetConfigParamValue 'MYAPP', 'MYPARM', 'STRING', @MYVALUE OUTPUT SELECT @MYVALUE AS MyValue 

• The IsQueryStoredProcedure property was left FALSE. It seems as though this should be true since you are using a stored procedure. But setting this property to true will create an error message that the stored procedure could not be found. This occurs with or without the addition of the EXEC statement.