Using SQL Server Stored Procedures in SSIS Data Flow Sources

  • Comments posted to this topic are about the item Using SQL Server Stored Procedures in SSIS Data Flow Sources

  • Why not simply use EXECUTE WITH RESULT SETS?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • We've run into some goofy metadata issues even with 2008. To work around them, we add the following as the first query within all of our stored procedures:

    [font="Courier New"]

    IF (1=0)

    BEGIN

    SELECT CAST(0 AS INT) AS Column1Name,

    CAST(0 AS INT) AS Column2Name,

    ....

    END;

    [/font]

    We set up this query to match the result set. Just be careful to keep the two in sync, or you could end up with some unexpected results. 🙂 There's probably a better way to do this - but this has been working for us so far.

  • tmac3217 (11/25/2014)


    We've run into some goofy metadata issues even with 2008. To work around them, we add the following as the first query within all of our stored procedures:

    [font="Courier New"]

    IF (1=0)

    BEGIN

    SELECT CAST(0 AS INT) AS Column1Name,

    CAST(0 AS INT) AS Column2Name,

    ....

    END;

    [/font]

    We set up this query to match the result set. Just be careful to keep the two in sync, or you could end up with some unexpected results. 🙂 There's probably a better way to do this - but this has been working for us so far.

    This method stops working in 2012 onwards. You'll need to use the WITH RESULT SETS method.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Good to know - Thanks!!!! We're looking to upgrade this next year. Just one more thing we'll need to tweak as part of our upgrade process/project.

  • Koen Verbeeck (11/25/2014)


    Why not simply use EXECUTE WITH RESULT SETS?

    Oh, that's because many of us still have to use procedures executing in versions previous to MSSQL2012.

    Thank you for reading the article, this is the first time I've wrote something and I appreciate your comments.

    Adán Bucio.

  • in SSIS 2008 we use FMTONLY in the ADO.Net Source task's SQL command text:

    SET FMTONLY OFF;

    EXEC SomeProcedureNameHere;

    does this method still work in SSIS 2012/2014? Microsoft says it will be discontinued in a future version but doesn't really say when: http://msdn.microsoft.com/en-us/library/ms173839.aspx

    There are situations where sys.dm_exec_describe_first_result_set doesn't work where FMTONLY does so I hope SSIS isn't trying to use that internally.

  • Chris Harshman (11/25/2014)


    in SSIS 2008 we use FMTONLY in the ADO.Net Source task's SQL command text:

    SET FMTONLY OFF;

    EXEC SomeProcedureNameHere;

    does this method still work in SSIS 2012/2014? Microsoft says it will be discontinued in a future version but doesn't really say when: http://msdn.microsoft.com/en-us/library/ms173839.aspx

    There are situations where sys.dm_exec_describe_first_result_set doesn't work where FMTONLY does so I hope SSIS isn't trying to use that internally.

    From memory (it was a while ago that I went through all of this) this does not work.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • beakdan (11/25/2014)


    Koen Verbeeck (11/25/2014)


    Why not simply use EXECUTE WITH RESULT SETS?

    Oh, that's because many of us still have to use procedures executing in versions previous to MSSQL2012.

    Thank you for reading the article, this is the first time I've wrote something and I appreciate your comments.

    Adán Bucio.

    It's a good article.

    I went over it again and I noticed you did mention WITH RESULT SETS. I apparently missed it at my first read.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I didn't test SET FMTONLY OFF nor I considered for this article because although It might work for some cases, those cases will waste resources. I used the profiler to see what the connection managers were doing behind the scenes, and for the OLEDB connection this would be the steps executed during the validation for a procedure:

    --1

    DECLARE@p1 INT;

    SET@p1 = 0;

    EXECUTE sp_prepare @p1 OUTPUT,

    NULL,

    --> This would be the command to execute from SSIS

    N'SET FMTONLY OFF; EXECUTE dbo.procedure;',

    1;

    --2

    SET NO_BROWSETABLE ON

    --3: Since the prepared command has FMTONLY in OFF the next sentence has no effect,

    --and the execution will return data

    SET FMTONLY ON

    EXECUTE sp_execute @p1

    SET FMTONLY OFF

    --4

    SET NO_BROWSETABLE OFF

    --5

    EXECUTE sp_unprepare @p1

    For the procedures where this works, they will return data for each validation call.

    And we all know that SSIS validates the metadata at various times during execution:

    1.- When the package starts if the property DelayValidation of the DataFlow is set to False (default)

    2.- During the PreExecute step of the DataFlow if the property ValidateExternalMetadata is set to True(default)

    3.- And I don't know why, the OLEDB source does another validation right before the real execution that the ADO.NET source does not.

    So unless you are changing the above mentioned properties from its default values, you do a real execution of your procedure by using this setting. If the procedure returns a big resultset... well you can imagine the effects.

    Adán Bucio.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply