Calling Parameterized Stored Procedure Through OLEDB

  • I have a complicated procedure that I run to obtain data, with part of the data cross tabbed into fields, as in Customer, Address, Problem1, Problem2, Problem3, Problem4.... where the "Problems" are separate records in the source table but I must combine them in the result file I am outputting as additional fields.

    For the first iteration of this project I am given IDs to use to obtains the results. In this case I import the IDs into a working table and use it in a join in the procedure to get my results.

    I have 2 temp tables I use to gather the initial results and the crosstab data, which is then combined with the data in the other tables and the pivot to produce the final result. In this query I need no parameters because the data needed to produce the result is contained in a working table.

    To make this work in SSIS I added a fake select to the beginning of the procedure which produces the metadata SSIS is looking for, and added SET FMTONLY OFF to the line above the procedure exec so that SSIS could get the metadata.

    OLEDB Command looks like this:

    SET FMTONLY OFF

    Exec uspGetPhysHistoryCETA

    First part of procedure looks like this:

    Set NoCount ON

    If 1 = 0

    Begin

    Select ID_Medicaid, Num_SSN, CDE_County, Title, Nam_First, Nam_Last

    From FakeTable

    End

    In the second iteration, I need produce the same thing, but this time I need to produce it using a date range. To do this I have eliminated the working table from the query and added two date parameters to pass to the procedure. This is where the difficulty arises.

    After the procedure is adjusted to be

    Create Procedure uspGetPhysHistoryByDateCETA (@BeginDate Int, @EndDate Int)

    I now have to modify the SSIS package. I added a new data flow and attempted to add an OLEDB source to the data flow.

    SET FMTONLY OFF

    Exec uspGetPhysHistoryByDateCETA @BeginDate = ?, @EndDate = ?

    When I attempt to add parameters to the procedure call I get an error, "Parameters cannot be extracted from the SQL command". So I removed the SET FMTONLY OFF and was able to add parameters. I added them per directions here: http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx and put the SET FMTONLY OFF back in. When I click OK I get

    Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

    So I remove SET FMTONLY OFF again and click OK. This time I get

    Hresult: 0x80004005 Description: "Invalid object name 'FakeTable'."

    So I go into the procedure and remove the table name completely, following the advice given here: http://www.windows-tech.info/15/e7e4a96178d88c7e.php

    It doesn't work. SQL Server won't even compile the procedure this way, so I must put a table name back in there.

    I try a real table name, but get this:

    Msg 207, Level 16, State 1, Procedure uspGetPhysHistoryByDateCETA, Line 38

    Invalid column name 'ID_Medicaid'. (and several more of these errors)

    This would be because the fields mentioned do not exist in this "real" table.

    So, it appears that I have to use the SET FMTONLY OFF in order to get the metadata for SSIS, because I must use the fake table in the metadata "declaration", and SQL/SSIS will accept this if the procedure has no parameters, but when I add parameters to a procedure all the rules change and I am stumped at this point.

    Does anyone know how to make a parameterized stored procedure work with SSIS OLEDB?

    This is all SQL2008 and SSIS 2008.

    Thanks,

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

Viewing post 1 (of 1 total)

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