Trace login and username when SSIS package modified

Question

Hi,

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

Thanks in advance.

Regards,

Kittu

Answer 1

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.

Answer 2

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