how to call a stored procedure in SSIS

Fri, 21 Jul 2006 17:35:59 Z

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

how to call a stored procedure in SSIS

Fri, 21 Jul 2006 17:44:22 Z

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

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

how to call a stored procedure in SSIS

Fri, 11 May 2007 08:04:28 Z

"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?

how to call a stored procedure in SSIS

Fri, 11 May 2007 09:59:24 Z

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

how to call a stored procedure in SSIS

Mon, 28 May 2007 21:25:29 Z

jaegd - thanks!!!

how to call a stored procedure in SSIS

Thu, 28 Jun 2007 08:34:50 Z

It does not work If there are more than one rows are coming out of stored procedure.

how to call a stored procedure in SSIS

Thu, 28 Jun 2007 08:37:44 Z

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

how to call a stored procedure in SSIS

Thu, 28 Jun 2007 08:50:13 Z

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

how to call a stored procedure in SSIS

Fri, 29 Jun 2007 09:13:59 Z

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

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

how to call a stored procedure in SSIS

Fri, 29 Jun 2007 09:51:01 Z

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.

how to call a stored procedure in SSIS

Fri, 29 Jun 2007 14:59:50 Z

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]

CREATE PROCEDURE [dbo].[GenMetadata]

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'

SELECT id,

[Name],

SirName

FROM #test

DROP TABLE #test

RETURN

how to call a stored procedure in SSIS

Thu, 12 Jul 2007 13:48:40 Z

Hi Thank you very much..

It is now working fine for the changes you suggested..

how to call a stored procedure in SSIS

Tue, 14 Aug 2007 08:50:27 Z

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?

how to call a stored procedure in SSIS

Wed, 15 Aug 2007 03:32:12 Z

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

how to call a stored procedure in SSIS

Fri, 16 Nov 2007 04:09:27 Z

what if you want to execute a dynamic script i.e.

Code Block

create procedure etl.executeScript @scriptId int, @sessionId varchar(50)
as
    set nocount on
    declare @script nvarchar(max)

    select @script=replace(script,'SESSION-ID',@sessionId)
    from etl.script
    where id=@scriptId

    exec sp_executesql @script
return

exec etl.executeScript 1, 'my session'

basically every script has an id, and a sessionid. ole db command can't pick the meta data coming out of this stored procedure.

in the etl.script table, usually scripts meta data don't change, mostly it's the where class that changes.

when executing the ssis package, script id remains the same, the only thing that change is the sessionid. so for the engine meta data never changes, only the body does. for example a tipical script would look like

select fundid, fundname, descrptin
from dbo.currentsession
where sessionid='SESSION-ID'

so the etl.executeScript stroed procedure replace the sessionid with the passesed sessionid.

any help as to how i can get ole db source to show up the meta data in the columns section.

cheers

how to call a stored procedure in SSIS

Sun, 18 Nov 2007 22:30:15 Z

I realize this probably isn't the answer you are looking for, but why not just put in the actual SQL, with a parameter for SessionID? What's the point of using a dynamic script if the metadata will always be the same?

how to call a stored procedure in SSIS

Wed, 21 Nov 2007 03:00:18 Z

hi jewlch.
basically i'm trying to avoide opening up SSIS for every scrpt change. i only want to open SSIS if there's a meta data change to fix it up. also as you probably know there's a sql command text limit of 4000 characters. sometimes my queries get a little big, (trust me they are optimised to the best extent, but some of them have derived queries with in, so no point in breaking up really)

how to call a stored procedure in SSIS

Wed, 11 Jun 2008 22:37:35 Z

Hey I am facing another problem that I have multiple #tables and multiple select statement in Store proce and SSIS says # table does not exist. As suggested by him if I put Return any where in between it will come out without executing multiple record set. Please advice

how to call a stored procedure in SSIS

Fri, 13 Jun 2008 21:07:41 Z

Try putting a non-executing SELECT statement at the beginning of the procedure, as jaegd showed earlier in this thread.

how to call a stored procedure in SSIS

Thu, 19 Jun 2008 17:44:16 Z

I've been having similar issues which jaegd's example looked like it would solve. It worked when I tested with a SQL server simple example of a proc using a temp table but failed for a Sybase data source with a more complex proc.

I cannot get this workaround to work with Sybase as the OLEDB source, even with a very simple proc example?

Are there additional complications with using Sybase?

Many thanks,

Chris

how to call a stored procedure in SSIS

Thu, 19 Mar 2009 15:38:58 Z

Hi I still can't see the outpout columns in OleDb Command Output Columns
, how can i get the output columns. I want to use them to insert in ole db destination

here is my sp:
create PROCEDURE [dbo].[GetData] (
    @user        varchar(50)
) AS
set nocount on
-- Publish metadata for ssis
if 1=0
begin
    select '' x, '' y, '' z
end

declare @user_tmp table
(
    x varchar(max),
    y varchar(max),
    z varchar(max)
)

insert into @user_tmp
    select 'x1' x, 'y1' y, 'z1' z

select distinct * from @user_tmp
set nocount off

how to call a stored procedure in SSIS

Fri, 03 Apr 2009 16:31:45 Z

i had the same problem--
there is two things u need to do . u need to alter your stored procedure and add is just after the begin statment.
SET NOCOUNT ON

SET

kkkkFMTONLY OFF

how to call a stored procedure in SSIS

Fri, 03 Apr 2009 16:34:05 Z

Here is what u need to do alter your stored procdures and just after the

Begin statement do this

SET NOCOUNT ON

SET FMTONLY OFF

kkkk