SSIS and versioning

This post will describe the big challenges that SSIS has when it comes to versioning solutions in SSIS when working as a team on the same SSIS-solution.

These experiences are based on using SubVersion (SVN) and Microsoft Team Foundation Server (TFS) for versioning solutions in SSIS. I have no experience with Git. But I will assume that the same problem occurs.

An example could be that John and Allan is working together on a SSIS-solution. Let’s call it ‘Staging DW’. They start out with local (their own) identical working copy of the SSIS-solution.

John creates a new SSIS-package in the SSIS-solution ‘Staging DW’. Let us call the SSIS-package ‘Stage Company’.
Allan creates a new SSIS-package in the SSIS-solution ‘Staging DW’. Let us call the SSIS-package ‘Stage Sales’.

John updates his working copy of the SSIS-solution ‘Staging DW’ and commits his changes to ‘Staging DW’ containing the SSIS-package ‘Stage Company’. Everything goes well.

Now Allan wants to commit his changes to the SSIS-solution ‘Staging DW’. He updates his working copy of the SSIS-solution ‘Staging DW’ – but he gets a conflict in the file ‘Staging DW.dtproj’ (an XML-file containing information about the SSIS-solution ‘Staging DW’ and its SSIS-packages).

The problem is that John’s new SSIS-package ‘Stage Company’ is a new package in the SSIS-solution ‘Stage DW’ and Allans SSIS-package ‘Stage Sales’ is a new package n the SSIS-solution ’Stage DW’. In the XML-file ‘Staging DW.dtproj’ information about the SSIS-package ‘Stage Company’ created by John occupies the same line as information about Allan new SSIS-package ‘Stage Sales’ in the XML-file ‘Staging DW.dtproj’.

The tools for solving conflicts in SVN and TFS isn’t capable of adding to the file. They can only overwrite.

One solution could be that Allan overwrites his ‘Staging DW.dtproj’ file. This means that his SSIS-solution ‘Staging DW’ looses it’s knowledge of his new SSIS-package ‘Stage Sales’. But he doesn’t lose his SSIS-package ‘Staging Sales’ it will still in the directory of the SSIS-solution ‘Stage DW’. He can now add this SSIS-package ‘Staging Sales’ to the SSIS-solution ‘Staing DW’ now containing John’s new SSIS-package ‘Staging Company’ and commit the SSIS-solution ‘Staging DW’ containing both John’s new SSIS-package ‘Stage Company’ and Allan’s new SSIS-package ‘Stage Sales’ without any problems.

Another solution is good old communication between team members. John could have told Allan that he was going to create the SSIS-package ‘Stage Company’ and that Allan should wait a bit with creating his SSIS-package ‘Stage Sales’ until John had committet the SSIS-solution ‘Stage DW’ containing his SSIS-package ‘Stage Company’. Then Allan could have updated his local copy and got the changes John made. Then Allan could start making his SSIS-package ‘Stage Sales’.

Or they could upfront have made all the SSIS-packages (without content) in the SSIS-solution. They could also choose to make SSIS-solution that was so specific or small that they did have to work together on them.

As of medio 2017 there is no real solution to this challenge.

SAS and SVN

Below is shown how you can extract the current SVN revision number for a given path into SAS and use it in your SAS program.

/* The path for your SAS-program(s) that is versioned in SVN. */
%let sysin=F:\MySASPrograms\TestProgram;
/* The path for the SVN program SubWCRev.exe. This is the default path, your path could be different. */
%let SubWCRev=C:\Program Files\TortoiseSVN\bin\SubWCRev.exe;
%put &sysin;

filename svnver pipe "call ""&SubWCRev"" ""&sysin"" ";

data _null_;
infile svnver;
input;
/* The output is in three different lines. */
put _n_ _infile_;
/* Puts the three different output lines in three diffent macrovariables called svnver1, svnver2 and svnver3. */
call symput('svnver' !! put(_n_,1.), _infile_);
run;

You can now use the three diffent macrovariables called svnver1, svnver2 and svnver3 in your program.

You might also want to have a look at this blogpost regarding TFS.