how to call a stored procedure in SSIS

I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.

Appreciate an early feedback.

Qadir Syed

Just create a new instance of "OLE DB Command".

Pick and choose your connection, and call the command as exec <procedure name>

|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.
If I have a select statement at the end of the Stored procedure that returns me some columns,then those columns are not recognized by "OLEDB Command" as out put columns.

Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.

Code Snippet

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON

IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END

-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')

SELECT cast(@.x as smallint)

RETURN

|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.

|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||

Please give an example of what doesn't work for you.

|||In the above post it should be table variable not temporary variable.

Conclusion:
It was table variable that Used instead of temporary table.

|||Hey try with the example below

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]

INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'

select id,name,SirName
from #test
drop table #test
RETURN

Please let me know the result.
|||

With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.

Code Snippet

IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL

DROP PROCEDURE [dbo].[GenMetadata]

GO

CREATE PROCEDURE [dbo].[GenMetadata]

AS

SET NOCOUNT ON

IF 1 = 0

BEGIN

-- Publish metadata

SELECT CAST(NULL AS INT) AS id,

CAST(NULL AS NCHAR(10)) AS [Name],

CAST(NULL AS NCHAR(10)) AS SirName

END

-- Do real work starting here

CREATE TABLE #test

(

[id] [int] NULL,

[Name] [nchar](10) NULL,

[SirName] [nchar](10) NULL

)

INSERT INTO #test

SELECT '1',

'A',

'Z'

UNION ALL

SELECT '2',

'b',

'y'

SELECTid,

[Name],

SirName

FROM#test

DROP TABLE #test

RETURN

GO

|||Hi Thank you very much..

It is now working fine for the changes you suggested..
|||Hello,

With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.

So SSIS chooses first dataset,So returns only one row having Null values for all columns.

How to overcome from this?
|||

Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)