Creating SSIS packages with BIMLs that use stored procedures with temporary tables

Question

As most of you know, SSIS can hardly read metadata whenever a stored procedure with temporary tables is used as an OleDbSource. Previously, this could be easily prevented by adding SET FMTONLY OFF; before the EXEC statement. The disadvantage of this is that the stored procedure is executed during validation, and this may take some time. Starting with SQL 2012, we can use the WITH RESULT settings to specify the columns and their data types. SSIS will pick it up, and everything will be fine in the land of SQL.

However, I want to create a package with BIML that uses such a stored procedure as the source, and I cannot get it to work. Suppose I have a stored procedure called "dbo.csp_MyCsp" that uses a temporary table called "#MyTempTable" with 1 column "ColA int". I am trying to create 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 the error "Invalid object #MyTempTable". It's strange if I open the package and paste this code into my OleDbSource, it works without errors. I have a feeling that the step of checking SSIS and BIML is different.

Do any of you have a suitable solution? I can’t use FMTONLY OFF, since stored procedures take some time to load, and this leads to a generation timeout. I am using SQL Server / SSIS 2014.

Thanks in advance!

Answer 1

. . BIML, BIML Express Visual Studio 2015.

:

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;

BIML:

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

Answer 2

, Biml. , , , , Biml temp .

( ?) , . , , , temp. " " Biml, SSMS, ( Temp , ).

, 35 5 , "Generate Packages", d , .