hi folks
i have a stored procedure that i want to run within an SSIS data flow package (sql 2016, vs community 2019)
the ole db source is the sp, and the sp creates 3 temp tables as well as lot of other standard sql lookups
so i am getting what seems to be the standard error regarding not being able to read the meta data of the temp tables
i have changed the connection to delay validation, set the ole source to NOT validate external metadata,
added SET FMTONLY OFF; to the start of my SP, changed my temp tables to global temp tables
but i still get the error? only when i create the temp tables in ssms then open the source in ssis does the sp run, but as soon as i drop the tables and run it again from SSIS it fails again
any ideas?
i am not concerned about the performance hit of SET FMTONLY OFF; as this is will not be run often
thanks
mal
All the SET FMTONLY stuff is no longer required and can be removed.
Instead of
EXEC proc
Use
EXEC proc WITH RESULT SETS ((col1 datatype1, col2 datatype2, ...));
as the source for your data flow. The RESULT SETS bit describes the expected columns and datatypes which the proc will return – SSIS uses this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 7, 2020 at 12:45 pm
hi Phil
yes this works perfectly
i had quite a few columns so i basically wrote the result set into a table, scripted the table as a Create table and tagged the fields onto my sp
great stuff!!
mal
May 7, 2020 at 12:47 pm
Nice one! Thanks for posting back.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 15, 2020 at 1:25 pm
You can do this with older versions of SQL Server too ( I have to use SQL Server 2008, doesn't support WITH RESULT SETS), but you have to do a query (that returns no rows) before you call your proc, to fake up the metadata for the OLEDDB connection. so something like:
IF 1 = 2
BEGIN
SELECT
-- query that returns data in shape of proc, but no rows returned
CAST(NULL AS VARCHAR(30)) AS [someval]
END;
EXECUTE [dbo].[someproc]
It's weird because it seems like I don't always need to do this, but when I get that dumb error this'll take care of it. I don't remember where I saw this, but thanks to whomever found this first!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply