« August 2007 | Main | November 2007 »
October 28, 2007
Performance Point Training and Documentation Links
Training Videos:http://www.microsoft.com/business/performancepoint/resources/training.aspx
Documentation:
http://technet.microsoft.com/en-us/library/bb794633.aspx
Share:
Posted by Steve Mann at 8:42 PM | Comments (0)
October 24, 2007
Using a stored procedure in SSIS OLE DB Source
by Mark Meyerovich, Senior Software Engineer, RDA CorporationIf you want to use a stored procedure with an OLE DB Source, be aware of this metadata caveat. The text below applies to SQL Server 2005. Feel free to comment if you have an insight on this problem on other DB platforms.
SSIS requests the metadata of the query from the OLE DB Provider. Jamie Thomson explains this problem in detail in his article ( http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx).
He suggests using table-valued functions, but what if you have a stored procedure that alters the data before returning the result?
Note how OLE DB Provider guesses the metadata from "reading" the stored procedure code. SSIS designer can obtain the correct metadata if finds a select statement or a declaration of a table variable that matches the result set appears in the beginning of the stored procedure. But if you happen to change the table variable to a temporary table or include some other code that does not produce the final result set, no metadata is available anymore.
Tracking the problem down with SQL Profiler shows that designer tries to execute the stored procedure in metadata-only mode. According to TechNet (http://technet.microsoft.com/en-us/library/ms173839.aspx) SET FMTONLY ON option "can be used to test the format of the response without actually running the query." In that mode no rows are processed or sent to the client. For example a “select count(*)†query returns NULL with that option.
Having this knowledge one can try to unset that option in the beginning of the stored procedure, so that the code executes normally and returns the full result with metadata. That may not work with a parameterized stored procedure, as SSIS designer may send unacceptable values for the parameters. In addition, running some code that alters the state of data at design time may be undesirable.
A better approach may be to detect the metadata mode and execute an empty statement returning just the metadata. Consider a stored procedure that returns customer orders below. Suppose that procedure alters database state and should only be executed as part of actual ETL process.
create procedure GetCustomerOrders
@CustomerID int
as
begin
set nocount on
-- Perform select against a known table as a layman's way of detecting FMTONLY option :)
if (select count (*) from MyAlwaysPresentAndPopulatedTable) is null
begin
-- The types in select statement must match those in the final result set
select
cast(0 as int) as OrderID
, cast(0 as int) as CustomerID
, getdate() as OrderDate
, cast(null as money) as SaleAmount
, cast(null as varchar (100)) as OrderDescription
return
end
-- Validate the parameters (omitted)
-- Create temporary table for data manipulation.
-- Assume that table holds large number of rows, so using table-typed
-- variable is not recommended due to inferior performance.
create table #CustOrders
(
OrderID int not null
, CustomerID int not null
, OrderDescription varchar(100)
, SaleAmount money
, OrderDate datetime
)
-- Perform the magic of extracting the customer orders
-- Return the result
select * from #CustOrders
drop table #CustOrders
end
By running a count query against MyAlwaysPresentAndPopulatedTable table, we detect the FMTONLY option when the query returns NULL. In that case the procedure executes an empty selects statement that returns correctly-typed result set and terminates. When the package actually executes, the procedure runs normally because the test query does not return NULL anymore.
Share:
Posted by Steve Mann at 7:19 PM | Comments (0)
