I have three systems: DEV, UAT, PROD, all running SQL 2012 Enterprise. I have existing packages that hit objects on these systems. Every time I have to make a change that affects metadata, it's not enough that I update the objects on the target system, then update the SSIS package that points to the system, and then re-deploy the package on the system. Even though the package will have been updated prior to re-deploy to handle the new metadata, I still have to go onto each system and update the package AGAIN before it will recognize the new metadata.
This behavior is new to me. In BIDS 2008, a refresh of metadata on one system would persist to any system it was deployed to. Now, if I change the connection manager prior to deploying it, I have to go back and re-do the metadata refresh before I can send it up. It appears that the metadata per connection setting is retained and is not updated when the package is updated.
An example so I can make this clear: I add a column to a destination table on all three systems. I update the package while it's pointing to DEV to reflect the change, and then deploy. So far, so good. Then I update the connection manager to point to UAT, and deploy the changed package there. At this point I'd expect that there would not be a required refresh of the metadata, as the schema of the destination table in UAT matches the one in DEV, to which the package was pointed when it was last refreshed. However, the package acts as if it was not updated, and needs its metadata refreshed while explicitly pointing to that system in order to work.
To try to address this problem, I added an expression for the ServerName property on the Connection Manager to be determined by System::MachineName, thinking that the problem was that changing the Connection Manager on the machine I'm developing on immediately triggered a check of the objects there, even with Delay Validation turned on. No such luck; it still shows the same behavior.
I can't find much info about this online, to determine if it's expected behavior with SSDT 2012 or something I'm doing incorrectly or a genuine bug.
Anyone have any insight into this issue?
Best Answer
Honestly, you shouldn't be editing the package to point to another environment before deploying it. Please look into package configurations to learn about methods to deploy your packages without editing the package itself.
Likely your connection strings and other settings should just be a variable read from a configuration file.
Since you are using SQL 2012 you should probably be using environments (thanks @MartinSmith)