Sql-server – can SSIS connect to another sql server

Does your SSIS package contain a DateTime type variable?

Read this Microsoft support article:

You receive an error message when you try to load an SSIS package that contains a DateTime type variable in SQL Server 2005

This problem occurs because the value of the DateTime variable is saved in string format when you save the SSIS package. However, the string representation of the DateTime variable is based on the date format that is specified in the Windows locale. Therefore, when you try to load the SSIS package on a computer that has an incompatible locale date format, the operation fails.

Solutions:

Manually update the DateTime type string in the SSIS package to use the slash (/) character as the date separator

or

Update the date separator in the Windows locale setting to be identical to the date separator of variables in the SSIS package file.

If the packages are stored in MSDB then no, the only date/time stamp is the creation date. Modified date is not tracked. You would need to delete the package prior to redeploying in order to track a modified date, which is essentially just forcing the create date to be your redeploy date.

In SQL 2005, the metadata for packages stored in MSDB is stored in msdb.dbo.sysdtspackages and msdb.dbo.sysdtspackages90 so you can run a SELECT on one of those tables to see your packages. For SQL 2008 and beyond, the table name is sysssispackages.

SELECT * from msdb.dbo.sysdtspackages90 WHERE name='mypackagename'

If your package location on the server is File System then you can get the modified date on the package file via Windows Explorer (or whatever file system tool you want to use). The path for SQL 2005 is [install drive]:\Program Files\Microsoft SQL Server\90\DTS\Packages\. Replace the 90 with 100 for SQL 2008, or 110 for SQL 2012.

Otherwise, I think your best bet would be to make sure security on your production server is adequate such that only those who should be deploying packages are, and then to track your publishing dates in your dev environment (source control, whatever). Doesn't really do much to ensure that production isn't changing without knowing about it, though....