Post reply

OLEDB Source Columns from Stored Procedure

  • May 2, 2017 at 3:43 pm

    #336712

    Hi SSC,

    I'm trying to use a stored procedure as a data source using an OLEDB Source component in my SSIS package. I originally wrote the package in SSDT 2012, but had to port it to 2008, which is where I started hitting issues.

    The connection manager is set up using a SQL Command. My actual statement has quite a few more columns, but the issue is the same. Consider the following SQL Command:


    exec dbo.GetData @SSISGuid = ?
    with result sets
    (
      (
       LSNTime datetime
    ...
      )
    )

    When I try to pull up the column info, I get the following error


    The same process works fine in SSDT, but in BIDS 2008, it doesn't. Doing some Googling, I came across a suggestion to put SET FMTONLY ON. But when I do that, I get a different error:

    I've also tried omitting the WITH RESULT SETS and making it a procedure without a parameter, but to no avail. should also mention the proc returns data based on DSQL.

    Does anyone know why I'm getting this and/or know how I can get SSIS to recognize column definitions from a stored procedure in this way?

    FYI, this was the article I came across suggesting  using FMTONLY: http://www.sqldbpros.com/2011/12/ssis-no-column-information-was-returned-by-the-sql-command/

    Executive Junior Cowboy Developer, Esq.[/url]

  • May 3, 2017 at 6:51 am

    #1940879

    I believe, with result sets, only appeared from SQL 2012 onward to address the issue of using CTE's and getting similar problems to what you are experiencing. See here for a possible solution using SET FMONLY
    https://simonworth.wordpress.com/2009/08/14/ssis-using-stored-procedures-as-a-data-source/

  • May 3, 2017 at 10:47 am

    #1940938

    tim.ffitch 25252 - Wednesday, May 3, 2017 6:51 AM

    I believe, with result sets, only appeared from SQL 2012 onward to address the issue of using CTE's and getting similar problems to what you are experiencing. See here for a possible solution using SET FMONLY
    https://simonworth.wordpress.com/2009/08/14/ssis-using-stored-procedures-as-a-data-source/

    Hi Tim,

    Thanks for the reply and the article. I think maybe it has to do with the contents of the proc then.

    Executive Junior Cowboy Developer, Esq.[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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