May 27, 2014 at 2:36 pm
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
June 5, 2014 at 9:17 am
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!
February 1, 2016 at 5:09 am
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
February 1, 2016 at 6:06 am
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.
February 1, 2016 at 7:18 am
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
February 1, 2016 at 7:28 am
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.
February 1, 2016 at 10:26 am
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
February 1, 2016 at 10:39 am
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
February 1, 2016 at 11:06 am
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.
February 1, 2016 at 11:33 am
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
February 1, 2016 at 11:42 am
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
February 1, 2016 at 11:47 am
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.
February 1, 2016 at 12:24 pm
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
February 2, 2016 at 1:51 am
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...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply