Generating SSIS packages with BIML that use Stored Procedures with temp tables

Question

As most of you know, SSIS has a hard time reading metadata whenever a stored procedure with temp tables is used as an OleDbSource. Previously, this could be easily prevented by adding SET FMTONLY OFF; before the EXEC statement. The downside of this is that the stored procedure gets executed during validation and this might take a while. As of SQL 2012 we can use the WITH RESULT SETS to specify columns and their datatypes. SSIS will pick this up and all is well in SQL land.

However, I want to generate a package with BIML that uses such a stored procedure as a source and I can't get it to work. Suppose I have stored procedure called 'dbo.csp_MyCsp' that uses a temp table called '#MyTempTable' with 1 column 'ColA int'. I am trying to generate an OleDbSource with the following (similar) Biml Code:

<OleDbSource ConnectionName="MyConnection" Name="OLE_SRC Test">
    <DirectInput>
        EXEC dbo.csp_MyCsp
        WITH RESULT SETS 
        (
            ([Col1] int)
        )
    </DirectInput>
</OleDbSource>

I get an error that says 'Invalid object #MyTempTable'. The weird thing is, if I open a package and paste in that code in my OleDbSource, it works without any error. I have a gut feeling that the validation step of SSIS and BIML is different.

Does any of you guys have an appropriate solution? I can't use FMTONLY OFF, since the stored procedures take some time to load and this causes a generation timeout. I am using SQL Server / SSIS 2014.

Thanks in advance!

Marvin

Have you tried any other ways round besides temp tables, such as global temp tables or table variables? Is it possible to just create the temp table as a regular table in a staging environment? — iamdave
I have considered it, but that's not really an option. I am trying to load a (complex) fact table that requires some intermediate steps (temp tables) in order to perform well. Creating persisted temp tables does not fit the architecture and shouldn't be necessary because strange thing is, the query works when I paste it in an OLE_DB source, it just doesn't work when I try to generate it. I think it's a BIML validation thing. Maybe the engine still uses the SSIS2008 validation or something like that. As of 2012, the WITH RESULT SETS should work flawlessly — Marvin Schenkel
I added SET FMTONLY OFF to my stored procedure with a cte. which is called with result sets syntax. — Doc
Unfortunately SET FMTONLY OFF is not an option for me because the BIML generation will fail with a timeout. — Marvin Schenkel

JodyT JodyT · Accepted Answer · 2017-02-01T14:05:58

I encountered these problems myself before. I used the solution described here. The original answer is not about generating with BIML but I have succesfully used this solution with BIML Express in Visual Studio 2015.

I used this stored procedure as example:

CREATE PROCEDURE csp_MyCsp
AS
BEGIN

    SET NOCOUNT ON;

    IF 1 = 0
    BEGIN
        SELECT  CONVERT(INT, NULL) AS [database_id] 
        ,       CONVERT(SYSNAME, NULL) AS [name] 
    END;

    CREATE TABLE #mydatabases (
        [database_id] INT,
        [name] SYSNAME
    );

    INSERT INTO #mydatabases
    SELECT [database_id], [name]
    FROM sys.databases

    SELECT [database_id], [name]
    FROM #mydatabases

END;

And this was included in my BIML:

EXEC dbo.csp_MyCsp WITH RESULT SETS (
    (
        [database_id] INT,
        [database_name] SYSNAME
    )
)

Generating SSIS packages with BIML that use Stored Procedures with temp tables

2 Answers