March 21, 2017 at 3:18 pm
Hi all,
I'm developing SSIS packages for SQL 2012, and using Visual Studio to deploy the solution to our dev/test/etc servers. I'm not entirely familiar with the project deployment methodology, and I want to be able to compare the versions of various packages deployed on the servers against what we have in source control. I'm trying to answer questions like:
- is package X identical between server Y and server Z?
- does package X on server Y match package X in source control?
The problem I'm finding is that the deployment process itself seems to alter the packages. If I deploy a solution from VS to an SSIS Catalog somewhere, then Export that solution from the SSIS Catalog to an .ispac file and unzip it, the extracted files don't match the files in the solution. Almost always, the VersionGUID is different; sometimes ThreadHints, LastModifiedProductVersions, or other properties have changed; variables and connections and such are often re-ordered. The deployment alterations might not make a functional difference (hopefully), but it makes version control more difficult, and I don't like deploying changes I haven't tested. Interestingly, these changes are usually made without updating the VersionBuild property (which suggests the VersionGUID might be the best way to compare package versions).
Has anyone tackled this? I know there are 3rd party tools out there, but I'd like to stick with native SQL/VS/TFS tools. Is there a way to ensure that the package versions I've approved for deployment are the actual versions deployed? Or are there better workflows for managing this stuff? Thanks!
March 22, 2017 at 7:23 am
BowlOfCereal - Tuesday, March 21, 2017 3:18 PMHi all,I'm developing SSIS packages for SQL 2012, and using Visual Studio to deploy the solution to our dev/test/etc servers. I'm not entirely familiar with the project deployment methodology, and I want to be able to compare the versions of various packages deployed on the servers against what we have in source control. I'm trying to answer questions like:
- is package X identical between server Y and server Z?
- does package X on server Y match package X in source control?The problem I'm finding is that the deployment process itself seems to alter the packages. If I deploy a solution from VS to an SSIS Catalog somewhere, then Export that solution from the SSIS Catalog to an .ispac file and unzip it, the extracted files don't match the files in the solution. Almost always, the VersionGUID is different; sometimes ThreadHints, LastModifiedProductVersions, or other properties have changed; variables and connections and such are often re-ordered. The deployment alterations might not make a functional difference (hopefully), but it makes version control more difficult, and I don't like deploying changes I haven't tested. Interestingly, these changes are usually made without updating the VersionBuild property (which suggests the VersionGUID might be the best way to compare package versions).
Has anyone tackled this? I know there are 3rd party tools out there, but I'd like to stick with native SQL/VS/TFS tools. Is there a way to ensure that the package versions I've approved for deployment are the actual versions deployed? Or are there better workflows for managing this stuff? Thanks!
In my opinion, you should ensure (and therefore assume) that the relevant branch in your source control system reflects exactly what is deployed to your servers. We use a CI tool to automate this process. If the deployment from the CI tool is successful, you know for sure that the versions are in step.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 22, 2017 at 10:09 am
Exactly! We're trying to implement a CI process, and what you stated is spot on -- I want to ensure that the branch in source control matches exactly what's deployed. As I explained, that doesn't seem to be the case using the native SQL/VS/TFS deployment process. Am I correct, or am I missing something? Have others solved this problem with the native tools? There are so many 3rd party tools out there -- is that because the native tools are really not up to the job?
March 22, 2017 at 11:08 am
BowlOfCereal - Wednesday, March 22, 2017 10:09 AMExactly! We're trying to implement a CI process, and what you stated is spot on -- I want to ensure that the branch in source control matches exactly what's deployed. As I explained, that doesn't seem to be the case using the native SQL/VS/TFS deployment process. Am I correct, or am I missing something? Have others solved this problem with the native tools? There are so many 3rd party tools out there -- is that because the native tools are really not up to the job?
You need another tool for CI, in my opinion, though I have not tried to make VSTS (which is close to a 'native tool') work.
I use TeamCity to automate the build/deploy process (it has many other uses – we give users secured access to it to run their own jobs, for example), though there are many other ways of skinning this cat.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply