The Use of Denali 'With Result Sets' Feature in SSIS Data Flow Task
October 15, 2011 at 3:58 PM
—
Steven Wang
The new T-SQL feature 'With Result Sets' in SQL Denali can be very useful for using the stored procedure as the data flow task data source in SSIS.
Before SQL Denali, there are some limitations for SSIS to use stored procedure as data flow task data source, like:
1. if you want the data source to use the different column names, you have to either change the stored procedure or use the derived column component, both are not convenient;
2. The same for the data type change, if you want to change the column data type you again have to either change the stored procedure or use the derived column component;
3. If your stored procedure is ended with dynamic SQL execution, like using Exec (@SQL) or sp_executeSQL @SQL, the SSIS was not able the return the column. A common work-around is to define a table variable inside the stored procedure, and use the SQL execution to insert the data to the table variable and then select the data from it. This is very awkward and the performance is bad when the data set is big.
The new T-SQL feature 'With Result Sets' solves this problem.
The syntax of the With Result Sets is simple and just like below: (More information for the Execute T-SQL, see BOL. link is here)
Exec Stored_Proc_Name @Variables
With Result Sets
(
(
Col1 Data_type,
Col2 Data_type,
.....
List All Columns here
)
);
A simple example to demo this functionality as SSIS data flow task data source to solve the problem 3 stated in the above.
1. Create a simple SP
USE [AdventureWorksDWDenali] --Denali sample DW database, free download
GO
CREATE Proc [dbo].[usp_UseWithResultSet_Dynamic_SQL_Denali]
As
Begin
Declare @SQL varchar(Max)
Set @SQL = '
SELECT [GeographyKey]
,[City]
,[StateProvinceName]
,[EnglishCountryRegionName]
FROM[dbo].[DimGeography]
'
Exec (@SQL);
End
GO
2. Create an SSIS data flow task and use exec [dbo].[usp_UseWithResultSet_Dynamic_SQL_Denali] as data source (SQL Command) while you click preview, it gets error like below:
3. By using the With Result sets, you can rename the column name and change the data type (I changed column from nvarchar to varchar), and best of all, there is no error to use this SP as the data source. see the below:
4. Terminate with a simple Union All Component and run. It is successful:
