Calling Parameterized Stored Procedure Through OLEDB

• Stamey

  SSCertifiable

  Points: 5600

  More actions

  January 13, 2011 at 9:29 am

  #243655

  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.