6

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?

Improve this question
4
  • 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, 2016 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).
    – user507
    Jan 14, 2016 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, 2016 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, 2016 at 23:29

3 Answers 3

Reset to default
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)

Improve this answer
0
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.

Improve this answer
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

Improve this answer
1
  • 2
    Some more explanation would be helpful to build confidence in exploring your answer.
    – RLF
    Jan 12, 2015 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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