CODEWITHSUNDEEP
sql-server-2008
PS078
PS078

Reputation: 461

How to capture what parameters are passed to stored procedure inside ssis package runtime

I have a scenario where my client informed me that their sql server agent jobs fail sometimes. On investigation, we found that the sql agent job calls an ssis package, which internally calls an SP with parameters (?,?,?), and the error is being generated in TSQL inside SP as per the error message.

I want to capture the parameters to test SP execution in a new query window, manually. We do not have an SSDT tool in the client environment with which to debug the SSIS package to identify parameters, however we have imported the ssis package in SSMS.

Is there any way I can capture the parameter passed at runtime to SP via the ssis package in SSMS?

Upvotes: 1

Views: 398

Answers (3)

Raj
Raj

Reputation: 502

This one is easy as you can write code in the SP which you want to debug :

  1- Create a table for variable capture 
  2- in procedure just give insert statement like 

 Insert into dbo.VariableLog values (@param1 , @param2 )

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Modify your sp to capture the parameters before doing further processing. Like this :

    CREATE TABLE dbo.ProcParameters
    (
     SeqNo int identity(1,1),
    ProcNm VARCHAR(255),
    Param1 nvarchar(255),
    Param2 nvarchar(255),
ExecResult NVARCHAR(MAX),
    ExecTime DATETIME DEFAULT(GETDATE())
    )

    create procedure MyProc
    (
     @Param1 nvarchar(255),
    @Param2 nvarchar(255)
    )
    as
    begin
declare @Id int,@ExecResult NVARCHAR(MAX)

     insert into ProcParameters
    (
     ProcNm ,Param1 ,Param2
    )
    VALUES('MyProc',@Param1 ,@Param2)

select @id = @@identity
begin try
    /*

    Your Code goes here

    */
SET @ExecResult ='Success'

   end try
 BEGIN CATCH
select @ExecResult = ERROR_MESSAGE()
END CATCH

UPDATE ProcParameters
SET ExecResult = @ExecResult WHERE SeqNo = @id

    end

Now, each time the proc is being executed, the values will be stored in the table ProcParameters and you can get it from there. Also, the result of each execution can be found in the column ExecResult.

Upvotes: 1

thotwielder
thotwielder

Reputation: 1707

You can turn on sql server profiler and capture the queries being run againt the database when SSIS package is executed, there you can see what parameters being passed in.

Upvotes: 1

Related Questions