August 18, 2009 at 10:27 am
I am executing a Stored Procedure in SSIS using OLE DB Source. EXEC xsp_abc 'Monthly'
When I do preview, the stored procedure does its thing (sum...etc) and it returns data from a select * from #temp at the end of the stored procedure.
How do I get this data into another table. How do I map columns when the souce has none?
August 24, 2009 at 2:11 am
I dont quite understand what you have done. Could you take a screen shot of the control flow tab and data flow tab and add those as an attachmnet and I will have a look.
August 24, 2009 at 7:28 am
I am executing a stored procedure (sp) from SSIS and because the sp did not return any results, I could not map the columns from the sp to my destination table. I have figured this out.
Through some articles I have read, I change the sp to create a temp table as follows:
IF 1 = 0
BEGIN
SELECTCAST(NULL AS NCHAR(2)) AS Portfolio,
CAST(NULL AS NCHAR(2)) AS Col1,
CAST(NULL AS NCHAR(3)) AS Col2
END
CREATE TABLE #TEMP_TABLE
([Col1][nchar](2) NULL,
[Col2][nchar](3) NULL )
INSERT INTO #TEMP_TABLE
SELECT
B.Col1,
B.Col2
.........
At the end of the stored procedure I did a select with a 'RETURN'. When exec this sp from SSIS, I got columns.
SELECT Col1, Col2 FROM #TEMP_TABLE
RETURN
August 24, 2009 at 8:03 am
Created my own stored proc.
USE [PH_Datawarehouse]
GO
/****** Object: StoredProcedure [dbo].[sp_dts_addlogentry] Script Date: 08/24/2009 14:51:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_test]
AS
IF 1 = 0
BEGIN
SELECT CAST(NULL AS NCHAR(2)) AS Portfolio,
CAST(NULL AS NCHAR(2)) AS Col1,
CAST(NULL AS NCHAR(3)) AS Col2
END
CREATE TABLE #TEMP_TABLE
( [Col1] [nchar](2) NULL,
[Col2] [nchar](3) NULL )
INSERT INTO #TEMP_TABLE
SELECT
'ab','def'
SELECT Col1, Col2 FROM #TEMP_TABLE
RETURN
Now when I go into the OLE DB Source I select the connection manager. Then I set data access mode to SQL command . Then in the command box exec usp_test
Now when I look at the columns tab in the OLE DB Source editor up pops the columns Portfolio,Col1,Col2
Now in the data flow task add an OLE DB destination . Thake the output from the source and drag it down to the destination.
Right click and then just hit the new button to create a new table.
Hopefully that should work for you.
The answer with stored procs and SSIS 9 times out of 10 is getting the stored proc right.
Ells.
😎
August 25, 2009 at 2:57 pm
try using @temp_table instead of #temp_table
sometimes have it define in a table variable helps.
August 26, 2009 at 11:36 am
thank you. I had resolved it by creating the temp_table.
September 8, 2009 at 10:33 am
Should this work for Netezza SP too .. I am having the same problem here.
October 1, 2009 at 12:10 pm
Hello guys, I was facing same problem as you I have a store procedure that receives a parameter and depending the parameter it returns result sets from different tables. And I was unable to map the correct tables in the SSIS.
I found this article and I think it will help you to solve your problem just take and note on the considerations you should have to implement it.
http://simonworth.wordpress.com/2009/08/14/ssis-using-stored-procedures-as-a-data-source/
I hope it helps =)
June 22, 2018 at 7:00 am
gurur.20 - Tuesday, September 8, 2009 10:33 AMShould this work for Netezza SP too .. I am having the same problem here.
Hi,
I have same problem also, have you find anyway?
June 29, 2018 at 4:10 am
I hope this is applicable...
There are a lot of good examples here: https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers
Clone the repository, open the solution, and review the SSIS packages, and the stored procedures that some of the packages call.
HTH...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply