I've restored our msdb database from production to one of the QA environments
and ran this script thereafter
set originating_server = 'QA_SQL'
where originating_server = 'PROD_SQL'
The problem I'm experiencing is that the owner of the dts packages belongs to an account that only resides on the PROD_SQL server.
So in other words the dts packages are failing to execute due to login failure or access denied. In order for me to get the dts package working I would need to resave the package while being logged in with a sql service account thereby changing the dts owner to the service account. Keep in mind that these dts packages are not structured storage files
I would like to know if there is any script out there that would change the owner of all dts packages and jobs in SQL server to avoid me of manually doing it
Hope someone out there can help :-D