How To - Tasks and Transformations : Execute SQL

While developing Mist, we found that there’s little in the way of online resources for learning how to use tasks and transformations in your workflows. While you can find explanations of tasks and transformations, examples aren’t as common for most of them.

Additionally, our experience with BIDS is that editing packages can be cumbersome. One goal for Mist was to make package workflows faster and easier to author.

Thus, this begins a series of blog posts that demonstrate how to use the over sixty various tasks and transformations in your packages, with examples in both BIDS and Mist.

Background:

For our inaugural post, I’m going to discuss the Execute SQL task. You might be wondering why I’m starting with a task that already has a ton of articles on it. Well, there’s a reason it warrants a lot of coverage; it’s a complex task. Additionally, it’s a great starting point for seeing with Mist can do and how it improves on BIDS. I’ll also be referring to this task in upcoming posts in this series.

The task’s purpose is to allow you to run SQL statements or stored procedures within a package’s workflow. You can capture values from the execution of the SQL query to be used later in the package. Some of the operations you might perform with the Execute SQL task include:

• Creating tables and views
• Returning a rowset from a query and saving it in a variable
• Executing a stored procedure
• The example I’m going to show you how to build will run a query on the AdventureWorksOLTP database, with that query stored in a SSIS variable and its result placed in another variable.

Using BIDS:

The first step is to add the Execute SQL task to a BIDS package. To do that, find the Execute SQL task in the Toolbox tool window.

Then drag and drop the task onto your package’s design surface. You’ll see a red X icon in the task, indicating that there are errors.

Opening the BIDS Error List tool window shows the actual errors pertaining to this task.

When you’re finished, there should be no errors on the Execute SQL Task, and the red X icon should be gone from the task.

Double click on the Execute SQL task to begin setting it up. A dialog box will open that lets you set properties on the task.

You can start by configuring where the SQL statement will run. Find the ConnectionType property in the SQL Statement group. Click inside the ConnectionType property field, and click on the down arrow to display its dropdown.

This dropdown lists the different connection types you can use. Since AdventureWorks is a SQL Server database, you’ll want to stick with the default, OLE DB connection type.

Beneath the ConnectionType property is the Connection property. If you click inside the Connection property field, you’ll again see a down arrow. Clicking on it displays a pop-up.

Select the New connection… item to bring up the OLE DB Connection Manager dialog box.

This dialog lets you create and manage data connections. For instance, if you already had a connection to Adventure Works, you could simply select it from the Data connections list and press OK. However, since there aren’t any connections, you’ll need to create one by pressing the New button, which greets you with a Connection Manager dialog box.

If you’ve installed Adventure Works on your local machine, you can type in your machine name in the Server name text field. Or you can simply enter a period to represent your local machine. Pressing the down arrow in the Server name text field causes the dialog to scan the network for SQL instances, which may take several minutes in some environments.

Once you enter a server name, you’ll notice that the radio buttons, in the Connect to a database groupbox, become enabled.

Select the AdventureWorks database from the enabled dropdown.

Finally, press OK to finish creating the connection. This will return to you to the Connection Manager dialog, which now lists the new connection.

Press OK again to set this data connection on the Execute SQL task.

Next up is the SQLSourceType property. This property specifies where the SQL statement comes from. The dropdown allows you to choose from three options:

Name Description Direct Input Enter text directly into a property File Connection Specify a file that contains the SQL statement. The file may already exist or may be created at runtime. Variable Create or choose a variable that contains the SQL statement. Select Variable from the SQLSourceType dropdown.

Notice that the SQLStatement property, directly underneath SQLSourceType, has been renamed to SourceVariable. Opening its dropdown lists all the built-in variables available to us.

However, the dropdown also includes a New variable… item at the top. Select that item to open the Add Variable dialog box.

This dialog allows you to set various properties on your new variable:

Name Description Container Indicates the scope of the variable. By selecting Package1, this variable becomes available to all containers, tasks, and event handlers within the package. Name Specifies a name for the variable Namespace SSIS provides two namespaces, System and User. The System namespace contains system variables and the User namespace contains custom variables. However, you are also allowed to create additional namespaces. Value type Specifies the type of literal that the variable stores. Note that the variable’s value must be of the value type. For example, having a variable value of abc with a value type of DateTime won’t work. Value The value that the variable stores.

In order to make the purpose of this variable clearer, rename it to SqlQuery. You can leave the remaining properties as-is.

Press OK to return to the properties dialog.

The last property in the SQL Statement group is BypassPrepare. The Execute SQL task can run the prepare command on a SQL statement before it executes on our specified connection. The BypassPrepare property makes it possible to skip that preparation. This is typically used when a SQL statement includes a keyword that the prepare command doesn’t support.

You’ve now established where the SQL statement will run and identified where the SQL statement will be stored. The next step is to actually write the SQL statement. To bring up the Query Builder dialog, click on the Build Query… button in the Execute SQL Task Editor dialog.

For this example, you can query your product inventory for items where the quantity on hand is less than 100. A query like this might be useful to inform someone that they need to increase production of an item. The most direct way to enter a query is to type it in the textbox where SELECT and FROM are already present.

After entering the query, if you click in the white space area above the data grid, you’ll see that:

1. The query text is reformatted
2. The data grid is populated with values that match my query
3. A visual representation of the table and the column selections appears at the top of the dialog

By pressing the Run button in the Query Builder’s toolbar, you can have the dialog execute your query so you can see the results.

This is great for confirming that your query returns the results you expect.

Now, press OK to save the query in the variable.

Returning to the Execute SQL Task Editor dialog, it’s time to discuss the ResultSet property. This property lets you control the format of the results from the executed query. The property value is a dropdown that lets us choose from:

Type When To Use None When the query returns no results, such as a statement to delete records from a table. Single Row When the query returns one row, such as a SELECT statement that returns a count. Full result set When the query returns more than one row, such as a SELECT statement that retrieves all the rows in a table. XML When the query returns a result set in an XML format.

In this example, it’s clearly possible to return multiple rows. Thus, you should select Full result set from the dropdown.

The final step is to define where the result set produced by the SQL query will be stored in the package. To do that, switch from the dialog’s General view to the Result Set view. Below, you can see that Result Set is selected in the left list box.

To add a Result Set mapping, click the Add button, which will add a default mapping to the list.

To provide a clearer name, change the result name to ‘ExecuteSqlResult’ and create a new variable named Result to store the result. Note that when creating this variable, its value type needs to be Object.

Unfortunately, if you try to run this task, you’ll hit an error that “The result binding name must be set to zero for full result set and XML results.” It turns out that there are specific rules about result set names when using various result set types. Microsoft has a webpage that lists these rules. In our case, we need to rename our Result to the number 0.

With that change, the Execute SQL task can be run successfully.

Using Mist:

Now that you’ve created the Execute SQL task in BIDS, let’s review how to create it in Mist. As I demonstrate the process, you’ll notice that the procedure for creating tasks will be familiar to BIDS users, but with some differences that help streamline the process of editing tasks.

To begin, you’ll need to create a package. If you’re unfamiliar with how to create a package, you can follow the first couple steps in the Mist User Guide’s Creating a Package topic.

Once your package is open, navigate to the Toolbox tool window.

Drag and drop the Execute SQL task onto the Package’s design surface.

The task has a red X icon, just like in BIDS, since it has errors.

Furthermore, the Error List tool window lists all the errors for the Execute SQL task upfront.

You’ll notice that these errors are much clearer than BIDS. Additionally, Mist lists all errors with the task upfront, unlike BIDS, which initially missed that SqlCommand was blank.

The next step is to display the Package Details tool window. The Package Details window is typically located in the lower portion of the Mist window.

If you can’t find the Package Details tab, you can make the window appear by switching to the Home ribbon tab and clicking on the View popup button.

In the list, click on the Package Details item to make the window appear.

The Package Details area displays settings for the selected task or transformation in the package. If the Execute SQL task isn’t selected, click on it in the designer to select it. The Package Details area will now be populated with properties for the task.

These properties should look familiar, since they’re the same properties you saw in the BIDS Execute SQL Task Editor dialog. However, you’ll notice that Mist is not using a modal dialog. In Mist, we’ve avoided dialog boxes as much as possible. Additionally, using tool windows lets you easily switch between tasks or make changes on the fly, instead of having to dismiss several dialogs and reopen them later.

Following the steps in the BIDS example, the first step is to create a connection to Adventure Works. To do that, you can follow the steps in the Mist User Guide’s Create New Connection section to create a connection, although you’ll want the Initial Catalog to be AdventureWorks, not AdventureWorks LT. It’s also worth reemphasizing that this approach allows you to easily find and reuse this connection elsewhere in your Mist project, including other packages

To associate this connection with the Execute SQL task, reopen the Package by double clicking on it. Then click on the Properties tab to display the Properties window. The Properties window is typically located in the lower right portion of the Mist window. If you can’t find it, you can use the aforementioned View button dropdown to make it appear.

You’ll see that the Connection property dropdown has a red border surrounding it. The red border makes it clear that this property needs to be set, and you’ll experience a build error until this is fixed. You can fix it by clicking on the dropdown’s arrow and selecting the AdventureWorks connection.

The next step is to write the SQL query. To do that, go back to the Package Details tab and look at the T-SQL Editor.

This control is designed to let you enter a T-SQL query, providing syntax highlighting and Intelliprompt along the way. You’ll notice that the Connection dropdown reads AdventureWorks; this dropdown is linked to the Execute SQL Task’s Connection property. The green icon to its right means that we’ve established a connection to the database, thereby enabling Intelliprompt.

As you type in the query, the Intelliprompt popup will offer you possible values. And those possibilities will be filtered as you continue typing.

Once you’ve entered the query, you can press the Preview Query button to execute the query and retrieve the first 100 results. Just like in BIDS, this lets you confirm that your query returns the results you expect.

Now that you’ve authored the query and know it’s valid, you need to move it into a variable and associate that variable with this task.

To start, select the SQL query and press Ctrl+C to copy it to the clipboard.

To create a package variable, go into the package designer surface and click anywhere in the white area. This causes the package to be selected. You may have noticed that the ribbon’s contents changed a bit when you did this. While all tasks and transformations have a common set of assets, the ribbon also lets you add items specific to the selected task or transformation. For instance, when the Execute SQL task is selected, the ribbon appears as:

Notice that the ‘Task Specific’ group, at the right, contains Parameter and Result buttons. Selecting the package changes the ribbon’s contents to:

The Parameter and Result buttons have been replaced with Connection Reference and Package Configuration buttons. Note that the first six buttons haven’t changed since they’re common to all tasks and transformations.

With the Package selected, click on the Variable button in the ribbon. This will add a variable to the package. Additionally, the Variable tool window will be brought to the front of the window.

The newly created Variable is already selected. Matching the BIDS sample, rename this variable to SqlQuery and set its namespace to User. To change its name, you can double click on its default name in the Variable column and type in the new name.

To store the query in this variable, double click on the Value cell and press Ctrl+V to paste in the query.

To associate the variable with the Execute SQL task, select the Execute SQL task in the designer. In the Package Details area, find the Method dropdown. This dropdown parallels the SqlSourceType dropdown in BIDS. Click on the dropdown to select Variable.

You’ll then see that the T-SQL editor vanishes and is replaced with a Variable dropdown. Don’t worry – if you want to test the query again or try another query, you can switch back by changing the Method value back to Direct.

In the Variable dropdown, scroll down to the bottom and select the User::SqlQuery variable.

To finish this up, you need to create a Result Set. First, go to the Result Set dropdown, directly underneath the Name textbox and select Full.

Next, in the ribbon, click on the Result button.

When you do that, you’ll see that the Result item was added to the Results data grid in the Package Details area.

Now you need to create another variable, using the same steps as before, named User::Result. Recall that the variable’s Type needs to be Object.

With that done, you can double click on the Result’s Variable cell to open its popup and select the User::Result variable.

Finally, change the Result’s Name to 0.

And with that, you’ve built a matching Execute SQL task in Mist. If you build your package, following the steps in the Mist User Guide’s Build the Example Project topic, you can open the Package1.dtproj in BIDS to see that it matches the sample you manually built in BIDS and also runs successfully.

Summary:

This concludes our first look at Mist’s package editing capabilities, along with how to construct an Execute SQL task. Next time, I'll talk about what you might do in your workflow if the Execute SQL task fails during execution.

Links:

Execute SQL documentation for the BIML language

Execute SQL task – MSDN

Craig