SQL Server – How to Find SSIS Package Calling Stored Procedure

loggingsql serversql-server-2016ssisstored-procedures

We are trying to catch errors into an Error Log Table, similar to below.
We have many Master SSIS packages, calling many stored procedures.

Following code will log into the Error Log Table. Is there a SQL reserved keyword to find the SSIS package which executed the stored procedure?

insert into dbo.ErrorLogData
(
    ErrorNumber 
    ,ErrorDescription 
    ,ErrorProcedure 
    ,ErrorState 
    ,ErrorSeverity 
    ,ErrorLine 
    ,ErrorTime 
    ,SSISPackage --???
)
VALUES
(
    ERROR_NUMBER()
    ,ERROR_MESSAGE()
    ,ERROR_PROCEDURE()
    ,ERROR_STATE()
    ,ERROR_SEVERITY()
    ,ERROR_LINE()
    ,GETDATE()  
    -- what is reserved keyword for SSIS package?
);

SQL Server: Error Logging and Reporting within a Stored Procedure

Best Answer

As far as I know, there is not a built-in function that provides the package name. I think the closest you are going to get is to use the APP_NAME function. This does depend on parameters being passed as part of the connection string to specify the name - which means that it could be changed at anytime, but on the positive side it also means it can be as flexible as you need it to be.

However, because it is set on the client-side the documentation does come with this warning:

The client provides the application name, and APP_NAME does not verify the application name value in any way. Do not use APP_NAME as part of a security check.

If the client did set the value in the connection though, this is the result you would get (example using Sql Server Management Studio from the link above):

USE AdventureWorks2012;  
GO  
IF APP_NAME() = 'Microsoft SQL Server Management Studio - Query'  
PRINT 'This process was started by ' + APP_NAME() + 
      '. The date is ' + CONVERT ( varchar(100) , GETDATE(), 101) + '.';  
ELSE   
PRINT 'This process was started by ' + APP_NAME() + 
      '. The date is ' + CONVERT ( varchar(100) , GETDATE(), 102) + '.';  
GO
Related Question