Using temp tables in stored procedures for data flow source in SSIS 2012?

Question

Using temp tables in stored procedures for data flow source in SSIS 2012?

Eric Mamet

SSChampion

Points: 11720
More actions
May 27, 2014 at 2:36 pm

#286962

Hi there,
I tend to use stored procedures as dataflow sources and, quite often, I use temporary tables within these stored procedures.
I have just tried this in SSIS 2012 and the "usual trick" to present a dummy recordset to SSIS (IF 1 = 2 SELECT ...) does not work as SSIS still complains (explicitely) that the stored procedure uses a temporary table.
It does find the right columns from the dummy select but also complains about the temp tables...
Is there a way around this? Or is it really not possible anymore to use temp tables in stored procedures used as SSIS 2012 datasources?
Thanks
Eric

Viewing 15 posts - 1 through 15 (of 17 total)

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

There is a way. Have a look at the WITH RESULT SETS technique here.

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.

Eric Mamet SSChampion Points: 11720 More actions · Answer 2

Phil,

Sorry for the delay in replying but I got burried under "emergencies".

If that interests anyone, here is a way to reproduce my problem and the solution brought by Phil.

With this procedure CREATE PROCEDURE dbo.UseTempTable

AS

BEGIN

SET NOCOUNT ON;

CREATE TABLE #MyTable(WhoCares INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Whatever VARCHAR(99));

INSERT INTO #MyTable(Whatever) VALUES('Minger');

INSERT INTO #MyTable(Whatever) VALUES('Bonjour');

SELECT ColInt = WhoCares

, ColVarChar = Whatever

FROM #MyTable

END;

I would get an error in SSIS 2012 if I tried to use it as a datasource in a dataflow.

Before 2012, there were various tricks to show a "pretend" recordset such as SET FMTONLY ON... SET FMTONLY OFF but they don't work in 2012.

From Phil's article, we just need to specify our Data Flow datasource asEXEC('EXEC dbo.UseTempTable')

WITH RESULT SETS

( ( ColInt INT NOT NULL, ColVarChar VARCHAR(99)) )

Still if you are interested, we can imitate SSIS 2012 behaviour in assessing our datasource like this

EXEC sp_describe_first_result_set @tsql = N'EXEC(''EXEC dbo.UseTempTable'')

WITH RESULT SETS( ( ColInt INT NOT NULL, ColVarChar VARCHAR(99)))'

Thanks Phil!

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 3

This is case can some one tell me what would be the syntax if the Stored Procedure has got some input / put put parameter?

Thanks & Regards,
MC

Phil Parkin SSC Guru Points: 246695 More actions · Answer 4

only4mithunc (2/1/2016)
This is case can some one tell me what would be the syntax if the Stored Procedure has got some input / put put parameter?

What have you tried so far?

Exec dbo.Proc @param1 = 'x'

with result sets

blah blah

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.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

I was trying to use it in SSIS , OLEDB Source and data access more as SQL command and I tried as bellow but wanted to know how to pass parameter.

EXEC('EXEC dbo.UseTempTable')

WITH RESULT SETS

( ( ColInt INT NOT NULL, ColVarChar VARCHAR(99)) )

Thanks & Regards,
MC

Phil Parkin SSC Guru Points: 246695 More actions · Answer 6

Instead of that, strip out the outer EXEC:

EXEC dbo.UseTempTable [params here]

WITH RESULT SETS

( ( ColInt INT NOT NULL, ColVarChar VARCHAR(99)) )

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.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 7

Nice.. that worked thanks for your help.

I have one more question.. if there are two result sets like below, is there any way map them in SSIS?

EXEC dbo.GetOrderInfo @orderid = 43671

WITH RESULT SETS

(

SalesOrderID INT NOT NULL,

OrderDate DATETIME NOT NULL,

TotalDue MONEY NOT NULL

),

(

SalesOrderID INT NOT NULL,

SalesOrderDetailID INT NOT NULL,

OrderQty SMALLINT NOT NULL

)

);

Thanks & Regards,
MC

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 8

Nice.. that worked thanks for your help.

I have one more question.. if there are two result sets like below, is there any way map them in SSIS?

EXEC dbo.GetOrderInfo @orderid = 43671

WITH RESULT SETS

(

SalesOrderID INT NOT NULL,

OrderDate DATETIME NOT NULL,

TotalDue MONEY NOT NULL

),

(

SalesOrderID INT NOT NULL,

SalesOrderDetailID INT NOT NULL,

OrderQty SMALLINT NOT NULL

)

);

Thanks & Regards,
MC

Phil Parkin SSC Guru Points: 246695 More actions · Answer 9

only4mithunc (2/1/2016)
Nice.. that worked thanks for your help.
I have one more question.. if there are two result sets like below, is there any way map them in SSIS?
EXEC dbo.GetOrderInfo @orderid = 43671
WITH RESULT SETS
(
(
SalesOrderID INT NOT NULL,
OrderDate DATETIME NOT NULL,
TotalDue MONEY NOT NULL
),
(
SalesOrderID INT NOT NULL,
SalesOrderDetailID INT NOT NULL,
OrderQty SMALLINT NOT NULL
)
);

Not sure exactly what you mean here by 'map'. Is this[/url] of any use to you?

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.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 10

For example, I have one Stored Procedure that gives me two result sets... and I wan to use this Stored Procedure as Data source in DFT of SSIS.

So checking if I can map these two result sets to two destinations.

Thanks & Regards,
MC

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 11

I'm using this stored procedure as data source in the DFT of SSIS. This Stored procedure has got two result set.. so just trying to see if I can map those two result sets to different destinations.

Thanks & Regards,
MC

Phil Parkin SSC Guru Points: 246695 More actions · Answer 12

only4mithunc (2/1/2016)
I'm using this stored procedure as data source in the DFT of SSIS. This Stored procedure has got two result set.. so just trying to see if I can map those two result sets to different destinations.

Not as far as I know. A single data flow source cannot broadcast two result sets to two separate outputs (cool as that might be).

I have not tried this, but you may be able to create two data flow sources, one for each result set (with different result set definitions, of course). That would call the proc twice, though, and it feels a bit untidy.

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.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 13

Thanks , yes I can use it as different data results , but I will end up in executing the Same Stored Procesure multiple times.

Thanks & Regards,
MC

Eric Mamet SSChampion Points: 11720 More actions · Answer 14

Can't you make the resultsets "look" the same and bundle them into one resultset?

You could add a column "ResultSetNumber" and split it in your dataflow...