SSIS ole db source using stored procedure cannot retrieve meta-data for columns

1
votes

I'm using a stored procedure for ole db source in SSIS. It always pops out error when I click on "Columns" panel. However, the same query executes successfully in SSMS and in "Build Query..." window. enter image description here

In the stored proc., no temp table but table variable is used. I did a lot research online but all similar cases seem to be caused by temp tables.

1
sqlstored-proceduresssis
Did you try applying the same solutions that are recommended when temp tables are involved? - Tab Alleman
Yes, I tried to use "SET FMTONLY OFF SET NOCOUNT ON EXEC [dbo].[AR_XXXX]" OR “IF 1=2 Begin EXEC dbo.AR_XXX END" But neither works - Echo
What about "IF 1=2 SELECT ..." and use the column list that will be returned by the proc. - Tab Alleman
You mean just to select from the source tables used in the stored proc? I have tried that and both tables are accessible even without "IF 1=2". However, I cannot apply the whole logic in stored proc. to a single "Select" statement since it's quite complex - Echo

1 Answers

2
votes

For SSIS to use a stored proc as a datasource, the stored proc has to result in only one possible resultset structure.

You can let SSIS see the meta data for a complex stored proc by starting with something like this:

IF 1=2
 SELECT
   1 AS MyIntColumn
 , 1.11 AS MyMoneyColumn
 , 'test' AS MyVarcharColumn
ELSE
 BEGIN
 ...

The thing is to put in a SELECT statement in the 1=2 block that would return the Columns and the DataTypes that your stored procedure will ultimately return. That way SSIS can look at that first statement and know what columns and datatypes to expect from the stored proc. That's all it needs. It doesn't need "real" data in that first select. It can be all dummy values that don't even come from a table.