5

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?

  • What happens if you set you connection dynamically, either via getting the connection prior to run time or setting it with a Config file? Also how are you calling these packages? – Zane Jan 13 '16 at 18:06
  • What type of connection manager are you using for your destination? If I recall there are some destinations that would require the metadata to be updated if the connection was changed, and some that don't (OLE DB maybe). – Shawn Melton Jan 14 '16 at 21:50
  • 2
    I've never encountered this issue on SSIS 2012. As long as the metadata is refreshed where the connection manager is looking at a database with the updated schema I can then deploy and set the connection manager to a different connection with the same updated schema without problems, are there other differences in the tables? – Martin Smith Jan 17 '16 at 15:39
  • Have you tried the option "Delay Validation" turned to True? Which would delay the validation of the connection to the run time. – Zinx Jan 18 '16 at 23:29
8
+75

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)

3

I had similar issue before with my SQL Server 2012 Enterprise Edition production box. Even though I would make sure everything was refreshed under SSIS packages before I deployed to the production box, when it executed on the production box it would just not work, saying object doesn't exists. I also had all the validation turned on, but that didn't help.

So, to try it out, I dropped the whole project from the production box (we use SVN for our changes so I can get it back to that revision) and re-deployed the project all over again. Since I did that, whenever SSIS is updated, it's working each and every time and is looking at the changes correctly.

I think there might be a bug or something inside the SSISDB causing this or internally metadata for project itself is not getting refreshed, but removing and readding worked for me, hopefully it will work for you.

0

Open the package in Data tools open data flow, open all async controls and click on ok and close the package.

Re-build a solution and deploy

  • 2
    Some more explanation would be helpful to build confidence in exploring your answer. – RLF Jan 12 '15 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.