SSIS : Using Stored Procedures with an OLEDB Connection in SSIS

Using Stored Procedures with an OLEDB Connection in SSIS

Large recordsets cause visual studio to freeze for long periods of time, giving the impression it has hung.
(This happens repeatedly when navigating data connections).

The Solution is to pass metadata up front to SSIS, hence avoiding LONG delays whilst SSIS validates your recordset.

There is a widely publicised work-around of using 'SET FMTONLY ON' to pass metadata only (passing 'SET FMTONLY ON; EXEC dbo.proc1') but I had limited success with this.


Basically, declare an empty record set of the correct type at the start of the procedure >

CREATE PROCEDURE dbo.usp_SSIS_Data_Fetch (@eventid BIGINT, @rows BIGINT) AS

BEGIN
SET NOCOUNT ON



-- Dummy Records to pass datatypes back to SSIS for validation without returning entire recordset.

-- (note, this expression always evaluates to false so is never used except to declare a result set of

-- the correct data types at the start of the procedure.



IF 1 = 0

BEGIN

SELECT
CAST(NULL AS BIGINT) AS [ID]
,CAST(NULL AS VARCHAR(255)) AS [TrackingUrl]
,CAST(NULL AS VARCHAR(255)) AS [SearchTerm]
END

-- Genuine Result set

SELECT ID, TrackingUrl, SearchTerm FROM dbo.TrackingTable

END
GO

In addition to this you have a ValidateExternalMetadata property on an OLEDB Source inside a data flow. You can set this to false if you are confident the output of your Stored Procedure is not going to change!


Links :
http://munishbansal.wordpress.com/2009/02/18/set-fmtonly-on-useful-in-tsql-ssis-packages-for-using-temp-tables/
http://sqlserverpedia.com/blog/sql-server-bloggers/ssis-stored-procedure-metadata/