locked
SSIS OLE DB Source in Data Flow Task calling SP that returns variable number of column as output RRS feed

  • Question

  • We have one old Stored Proc that I need to reuse. But that stored proc returns different number of Columns as output depending on input Parameter. Say, if I pass 1 to 9 it will return 'ID', if i pass 10 to 33 it will return 'ID', 'Name',  if I pass 34 to 51 it will return 'ID', 'Name', 'DefaultPosition' 

    In this way.

    Now I wanted to use this in OLE DB Source in SSIS DFT. 

    During that time immediately it is picking all possible columns in Available External Columns. Even if I manually select/delete those, package is failing at Runtime, because in Runtime the parameter this will get, it should always return one & only one column which is ID. [Because in this package I will only have to pass parameters like, 1 or 2 or 5 or 7 or 9.

    Which in every  case should return only ID from stored proc.

    Any Idea how I can get this please?


    Regards, Avik M.

    Monday, March 17, 2014 6:21 PM

Answers

All replies

  • When you design the package just make it return one column. Then set validate metadata to false, delay validation to false, too. You should be all set then.


    Arthur My Blog

    Monday, March 17, 2014 8:42 PM
  • Has any one tried something like it? Could you please provide me a sample if you have done it.

    I tried these options but always got errors as

    [usp_return [64]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E55.An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E55  Description: "Column does not exist.".

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "usp_return" (64) returned error code 0xC0202009.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    ~ Thank you so much


    Regards, Avik M.

    Tuesday, March 18, 2014 4:55 AM
  • one way is this to make sure always only ID column alone is selected from SP output as per below

    http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

    Alternatively you could tweak SP to put placeholders (using NULL or '') for missing columns so that it always returns all columns and then in OLEDB source select only select columns you want ie ID alone.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Tuesday, March 18, 2014 5:53 AM
    • Marked as answer by AMJEE Tuesday, March 18, 2014 7:07 AM
    Tuesday, March 18, 2014 5:52 AM
  • This is what we call 'THE RIGHT SOLUTION'. 

    I got a similar hint from Dave's blog, so I implemented that just hours back. Although yes! I understand that, we don't really have options to fetch metadata info in any direct way from Stored Proc and we should ideally approach for Table Valued Function instead... but what to do. sometimes unavoidable.

    http://blog.sqlauthority.com/2013/09/26/sql-server-select-columns-from-stored-procedure-resultset/

    Thanks again Visakh, marked it as Answer.


    Regards, Avik M.

    Tuesday, March 18, 2014 7:07 AM