CODEWITHSUNDEEP
sql-serverssisbiml
Marvin Schenkel
Marvin Schenkel

Reputation: 145

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

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

Upvotes: 4

Views: 945

Answers (2)

JodyT
JodyT

Reputation: 4412

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
    )
)

Upvotes: 1

Brennahan
Brennahan

Reputation: 13

I ran into a similar issue something similar in my current Biml project. The issue, like you mentioned, seems to be that Biml doesn't account for temp table generation within a stored procedure.

My solution (workaround?) was to first make them global temp tables, instead of just temp tables. Then, I created a new stored procedure that ran the same code as my main stored procedure to handle creation of the temp table. Before I run 'Generate Packages' in Biml, I open up a new query window in SSMS, run that stored procedure and keep that window open (Global Temp tables last as long as the session, or this case query window, is open).

It's a bit of a pain, but at the same time, it cut the execution time of my stored procedure from 35 minutes to 5 minutes and I only need to worry about this during the 'Generate Packages' step, so I'd say it was worth it.

Upvotes: 0

Related Questions