Updating SSIS 2012 Project files from deployed server version (project deployment)

• phelmer

  SSChasing Mays

  Points: 633

  More actions

  June 3, 2015 at 3:44 pm

  #304709

  We are working out some details of our SSIS development and deployment process and are wondering if there's an easier way to accomplish a "get latest from production" and incorporate that into our workflow.

  Let's say we have an SSIS project with 2 packages, a project-level parameter and 4 project-level connection managers. When we first created the project, we simply checked it into TFS and used our standard code management and release processes to get it into production.

  Now the time has come to make a change and deploy it. What we want to do first is sync TFS' copy with production to make sure we don't have any drift between TFS and production. Production is the truth that wins because it actually runs our business. 99.9% of the time, the 2 will match, but it is critical that we're aware of the 0.1%.

  With the package deployment model, the package is the top-level object (even when there's multiple). With project deploy, there are settings in the DTPROJ file (such as the project's ProtectionLevel) which are not contained in the ISPAC. We are also using values that are different from the SSDT default for some of those settings and we want to retain our values for those. So, what we need is to update the dtsx/conmgr/project.params from production while keeping the dtproj/sln settings from TFS.

  After looking into it, it appears that these are our only options:

  Option A

  1.) Create a new throwaway SSIS project (Integration Services Import Project Wizard) and refer to the server or an ISPAC exported from that server.

  2.) Copy/sync the dtsx/conmgr/project.params files from the "server" version over to the TFS working copy's location.

  Option B

  1.) Export the ISPAC from the server.

  2.) Rename the ISPAC to ZIP.

  3.) Copy/sync the dtsx/conmgr/project.params files from the "server" version over to the TFS working copy's location.

  Is there any other method where we can accomplish what happens in Option A (step 1), but with an existing project rather than a new one?

  Thank you.

  Phil Helmer
  Database Engineer