Recently, I discovered an issue with SQL Server Integration Services (SSIS) regarding how the Execute SQL Task item handles returning a result set to the control flow. Lets start by demonstrating an existing issue with the Execute SQL Task using ODBC then I'll present a solution. First, create a new DSN that points to the database using the ODBC Data Sources Administrator (Control Panel | Administrator Tools | ODBC Data Sources). If you are only dealing with SQL Server you can still test this out by creating a DSN that uses the SQL Server ODBC driver.
Figure 1 shows our initial control flow that issues a query to an ODBC data source (in my initial case a DB2 database residing on a mainframe that needs to download data to a SQL Server 2005 and SQL Mobile database), iterates through the results, and carries out some additional data flow based on each row in the initial result set.
Figure 1. SSIS package control flow using Execute SQL Task
In Visual Studio 2005, create a new BI Integration Services project. In the Connection Managers window right-click and select New Connection... From the Add SSIS Connection Manager dialog, highlight ODBC and select the Add... button to continue. In the Configure ODBC Connection Manager dialog, Select the New... button, complete the necessary information in the Connection Manager dialog, select Test Connection to verify your settings, then select the OK button. Your ODBC Connection is now complete and should appear similar to the screen in Figure 2.
Figure 2. Configure ODBC Connection Manager dialog
Next, add an Execute SQL Task item to the package's control flow. Right-click the task, select the Edit... command. In the Execute SQL Task Editor dialog (Figure 3), set the necessary properties. Since we will be issuing a Select command and want to have access to the result set, begin by setting the Result set property to "Full result set". Next, set the ConnectionType to ODBC and assign the previously created ODBC connection to the Connection property. In the SQLStatement property, type the SQL for the select statement.
Figure 3. Execute SQL Task Editor - General Page
In the page list of the Execute SQL Task Editor, select the Result Set page (Figure 4). Select the Add... button to create a result set mapping. Change the Result Name to "0" (zero) and add a new variable to hold the result set. But sure the variable type is set to type Object and has package-level scope. Select OK to continue.
Figure 4. Execute SQL Task Editor - Result Set Page
Now we are ready to test the execution of the task. Simply right-click the task item and select Execute Task. The execution will produce the following error message:
[Execute SQL Task] Error: Executing the query "select locationid, name from production.location" failed with the following error: "Disconnected recordsets are not available from ODBC connections.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
According to feedback I received from Microsoft support, this is completely by design. The explanation I received, suggested that because the ODBC specs does support disconnected recordsets, the Execute SQL Task item is functioning as designed. What? Huh? Can't be? In .NET, a dataset is dataset is dataset, right?. It has no knowledge of the connection used to load it's data. That's ADO.NET 101 right? However, I must say that if you using an OLE provider the Execute SQL Task item functions as expected and returns the result set correctly, but not with ODBC and I haven't been able to get it to return the result set when the connection is SQL Mobile. So, how do we work around this when we need to have access to the result set using ODBC or other none OLE data providers? We begin by replacing the Execute SQL Task item with a Script Task (not an ActiveX Script Task, although you could use it also and write VBScript or JScript code). Right-click the Script Task and select the Edit... command. In the Script Task Editor (Figure 5), set the ReadWriteVariables property to include the variable (in this example the variable is named varDataset) that was previously defined to contain the result set using the Execute SQL Task.
Figure 5. Script Task Editor dialog
Next, select the Design Script... button to launch the Visual Basic for Application editor and enter the following code in Sub Main of the ScriptMain class:
Public Sub Main()
Dim connName As String = Dts.Connections(0).Name
Dim conn As Odbc.OdbcConnection = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)
Dim da As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter("select locationid, name from production.location", conn)
Dim ds As Data.DataSet = New Data.DataSet()
Try
da.Fill(ds)
Dts.Variables("varDataset").Value = ds
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
Return
Finally
da.Dispose()
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Notice that this code can reference the current DTS package directly including the connections and variables defined in the package. Don't forget to make the variables available to the script as previously described or the code will throw an exception because the variable hasn't been exposed. Our final control flow (Figure 6) will look much like it did before only we have replaced the first Execute SQL Task item.
Close the script window, select OK to continue, then right-click the Script Task and select the Execute Task command. The Script Task will execute the SQL command and assign the result set to the varDataset variable. The varDataset variable can now be used to iterate the dataset in subsequent data flow like a Foreach Loop Task as demonstrated in this example. In my opinion, this is what the Execute SQL Task should do, and does do, except for ODBC connection types. Although we had to do a little more work than if the Execute SQL Task worked properly, the real power of SSIS is demonstrated by the fact we can jump right in and add the correct plumbing so that data control flow is unaffected. With the Script Task item and ActiveX Script Task item we can create some powerful BI integration solutions and work around some shortcoming that are sure to get corrected over time.