SSIS , OLEDB and Stored Procedures

SSIS , OLEDB and Stored Procedures.

SSIS cannot 'see' the metadata for returned data from stored procedures like it can for tables and views. Here is a collection of methods to try when using sprocs with SSIS.

On oledb connection managers, set -

DelayValidation = True

On data sources and components inside the data flow, set -

ValidateExternalMetaData = False

When calling your stored procedure, prefix as follows -

"SET FMTONLY OFF; EXEC dbo.myprocedure @param = 1"

In the stored procedure itself, create a header as follows -

CREATE PROCEDURE dbo.myprocedure (@param INT)
AS
BEGIN

-- {options to set...}
SET NOCOUNT ON; -- {supress 'rows affected' messages}

SET FMTONLY OFF; -- {ensure full data retuned. incase FMTONLY ON was in effect}


-- {false header to pass metadata}

IF 1 = 0
 BEGIN
 SELECT
   CAST(NULL AS INT) AS [intColumn1]
 , CAST(NULL AS INT) AS [intColumn2]
 , CAST(NULL AS VARCHAR(5)) AS [varcharColumn1] 
 , CAST(NULL AS VARCHAR(255)) AS [varcharColumn2] 
 , CAST(NULL AS DATETIME2(0)) AS [datetime2Column1]
     , CAST(NULL AS VARBINARY(20)) AS [varbinaryColumn1]  
 END

-- {genuine query}

SELECT intColumn1, intColumn2, varcharcolumn1 ...

END
GO

SSISTalk: Phil Brammer - Stored Procedures and the OLE DB Source
ReplicationAnswers : Coping with no column names in the oledb source