Anonymous261977


I want to perform following using Execute SQL task

declare @LogID int, @OperationID int

exec usp_CreateLog

@DataProvider='ABC'

,@Source_Original = ''

,@NoTables = 3

,@UserName = '@userName'

,@LogID =@LogID output

select @LogID as LogID

I want @LogID as output. Please suggest how to achive in SSIS




Re: Execute SQL task

PedroCGD


Use OLEDB COmmand, ans in the SQL Command write EXEC SP_Name , , , , OUTPUT

And in the second tab link the input/output columns

Regards!







Re: Execute SQL task

Anonymous261977

Can you please explain in detail as I am new in SSIS.

EXEC SP_Name , , , , OUTPUT

What is






Re: Execute SQL task

Anonymous261977

can you please give more detail as am new in SSIS

what is in the sql statement... considering my sql statement.





Re: Execute SQL task

PedroCGD

This is your stored procedure that you can create in your database.

I didnt tested yet, but is more or less this:

Code Snippet

CREATE PROCEDURE usp_CreateLog

@MyDataProvider char(2),

@MySource_Original varchar(20),

@MyNoTables int,

@MyUserName char(10)

AS

DECLARE @LogID int, @OperationID int

INSERT INTO [MyTable] (DataProvider, SourceOriginal, NoTables, Username)

VALUES (@MyDataProvider, @MySource_Original, @MyNoTables, @MyUername)

SET @LogID= SCOPE_IDENTITY()

RETURN @LogID

Now, create an OLEDB Command to execute it.

In the second tab of OLEDB Command, link the input and output columns, and use your own variables [@@User::] or system variables [@@system::]

I can give some images if you want.

Regards!






Re: Execute SQL task

PedroCGD

dont worry, I willl help you...

If you need I can give some images!

Regards!






Re: Execute SQL task

Anonymous261977

thanks for detail. It works

Can u please give me one more help

I want to execute following function with parameter LogId

like

set @Dest= dbo.fn_GetArchiveFileName ( )

And I want @Dest as output string

Can u please help me in this





Re: Execute SQL task

PedroCGD

Explain me better...

Where you need to execute the function In database In the controlFlow Dataflow

Give me more details!

Regards!