locked
Trace login and username when SSIS package modified RRS feed

  • Question

  • Hi,

      Is it possible to trace who modified ssis package and when it was modified? 

    Thanks in advance.

    Regards,

    Kittu

    Tuesday, October 27, 2015 12:09 PM

Answers

  • Hi Kittu,

    When you store the package in File System, we can only get the modified date on the package file via Windows Explorer. To trance the user that modify the package, we can apply Auditing Policy Settings for the file. For more details about how to apply or modify Auditing Policy Settings for a Local File or Folder, please refer to the following document:
    https://technet.microsoft.com/en-us/library/cc771070.aspx

    When you store the package in SQL Server, the sysssispackages table only store the create date but not the modified date. When we import a new package that overwrite a package that is stored in SQL Server, the value in createdate column doesn’t update. And we cannot find the login and username who modified the package. To achieve your requirement, we can use SQL Server Profiler to monitor it. Please select the RPC:Starting event, SP:Starting event and other needed events in the SQL Server Profile, then we can see the time, login and username from StartTime, LoginName and NTUserName columns after running the trance when executing the statement like below:
    exec dbo.[sp_ssis_putpackage] N'package_name','2766D4BD-AC11-4780-8A1D-CE240C83FF58',N'SSIS Package Description','2015-04-27 13:51:29','00000000-0000-0000-0000-000000000000',0x3C…

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, October 28, 2015 7:58 AM

All replies

  • If the SSIS Packages are stored in SQL Server then you could try something like this

    --SQL Server 2005

    SELECT *
     FROM [msdb].[dbo].[sysdtspackages90]

    There is also VersionBuild in the package properties.


    Please click "Mark As Answer" if my post helped. Tony C.


    Tuesday, October 27, 2015 12:26 PM
  • Hi Kittu,

    When you store the package in File System, we can only get the modified date on the package file via Windows Explorer. To trance the user that modify the package, we can apply Auditing Policy Settings for the file. For more details about how to apply or modify Auditing Policy Settings for a Local File or Folder, please refer to the following document:
    https://technet.microsoft.com/en-us/library/cc771070.aspx

    When you store the package in SQL Server, the sysssispackages table only store the create date but not the modified date. When we import a new package that overwrite a package that is stored in SQL Server, the value in createdate column doesn’t update. And we cannot find the login and username who modified the package. To achieve your requirement, we can use SQL Server Profiler to monitor it. Please select the RPC:Starting event, SP:Starting event and other needed events in the SQL Server Profile, then we can see the time, login and username from StartTime, LoginName and NTUserName columns after running the trance when executing the statement like below:
    exec dbo.[sp_ssis_putpackage] N'package_name','2766D4BD-AC11-4780-8A1D-CE240C83FF58',N'SSIS Package Description','2015-04-27 13:51:29','00000000-0000-0000-0000-000000000000',0x3C…

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, October 28, 2015 7:58 AM