Parameterized Query in OLE DB data source component

Well, I got a simple task to build a simple ETL package by using few existing store procs.

It seems very easy at the beginning. I found the article in MSDN, just use parameterized query. However, the MSDN online document did not tell me preview doesn't work on parameterized store proc. Because  a stored proc can be written such that it conditionally returns different columns, or returns multiple data sets, or returns data from a table that doesn’t exist because it is created as part of the stored proc.  SSIS, however, needs to know what columns will be returned by the stored proc in order to provide the column metadata.  It does the best it can by providing output columns for the first statement in the procedure.

Few options involved to solve this problem:

 

1. Create an UDF table variable, specify my return columns and populate the table. Ref: http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx . But this is not always a good option, because multi-statement table-valued UDFs can be optimised very, very badly by SQL Server, as they don’t support set-based logic.  This would have to be an inline table valued function, which may not satisfy your requirement, and even then, you may have difficulty in persuading the DBAs that it is not going to cause problems.

 

2. Create a dummy selection at the beginning of the store proc. P.S.: If you have a store proc wrapper to call different SP in the if...else condition, you need this for all the SPs.

The dummy selection could be written as follow:

if 1 = 0 --- this will never be executed
begin
      select attr1= cast(null as varchar(10))
              ,attr2 = cast(null as decimal(12,0))
                .....
end

This is very important to make sure the data type in the dummy selection matchs the data type of the actual return column.

Well, this is simple, but tricky.  I really hope Microsoft could document their online work through better than just missleading people and assum everything they provided buttons should work fine. I don't understand what is the point to enable the preview button if it doesn't work. Or, please let us know.

Well, thanks to our SSIS team Phill and Mathew.
Also the SSIS blog: http://blogs.msdn.com/mattm/default.aspx