Version Control with Integration Services

Introduction

I had seen a poll on the SQLServerCentral homepage with regards to the version control system used for storing DB scripts and the results show that there are a considerable number of votes to the option “We don’t use source control for database scripts”. While there are a few options like schema comparison feature (not available in Visual studio professional edition) and the Red Gate SQL Source Control, which makes source control for database objects easier, these features aren’t available always at the workplace. Most times developers are more comfortable working directly in SSMS rather than using Visual Studio to create database projects and make changes from Visual Studio IDE. Working with SSMS, there is always a chance that one might forget to check in the changes back to the source control system. This article focuses on using SSIS to automatically check in the changes that the developers missed for the day into the version control system.

Prerequisites:

You will need the below installations:

AdventureworksLT sample database.

Permissions to the SQL server instance where the AdventureworksLT sample database resides.

VisualSVNServer installed with permissions to access the repository.

TortoiseSVN client on the m/c from which the SSIS package will be run.

The screen shot of the Adventure Works database on my computer is shown below. I have also installed a VisualSVN server on my machine for demonstration. Usually you will need to setup Visual SVN server on a separate server where you would like to store your source code.

You can open the VisualSVN server manager to check for the Server URL. You can also create or manage users, repositories and logging options. I have created one user as below.

Creating a new Repository:

A new repository needs to be created using the Visual SVN server manager before we can store our scripts. Enter the name of the repository (AdventureWorksLT) and select the check box. It will create subfolders trunk, branches and tags under the AdventureWorksLT folder.

Once you create the repository, you will be able to see the structure like below within the VisualSVN server manager.

If you right click on the AdventureWorksLT repository and click browse you should be seeing the AdventureWorksLT folder in a browser. Once you click on the folder you will see the subfolders as shown in the picture below. Based on how the subversion authentication is setup, you might be asked for the credentials before you see the page. Once the scripts are stored, you will also be able to browse the script file from here.

SSIS package:

Let us first review the SSIS package before actually running it. The SSIS package:

Gets the list of objects that have changed since the last check in date.

Checks out the latest copy of the database scripts from SVN to a working folder.

Creates the folder structure if required.

For each of the objects obtained in step 1, it compares the script from the database to that from the working folder.

Updates the script in the working folder if changes are identified.

Checks in the modified code and deletes the working copy.

The list of variables used in the package is shown below. Please change the values of the SVNRepository, WorkingDirectory, LastAutoCheckInDate based on the environment setup you have. If you don’t have any code within sourcesafe yet or if you are running the package to set up the initial version, you can choose LastAutoCheckInDate such that all the objects for the database are selected.

Execute Process Task 4 “SVN add Folders”

In this Execute Process Task, we execute an svn add on each of the folders created in the step above. If you skip this step and checkin your changes, these folders or their contents will not be added in the new revision. I have created a batch file with the below code. This will check for all the folders that we created earlier and add each one using a loop.

Foreach loop “For Each modified SQL object”

Script Task 5 “GetDefinition”

In this script task, using SMO the scripts for the objects of concern are created and stored in the user variable User::ObjectDefinition. This will need some modification based on the objects you need to consider.

Data Flow Task 7 “Compare Scripts”

Add a precedence constraint from Task 6 to the next Data Flow Task 7 “Compare Scripts”.

This data flow task reads the file from the working directory and compares with the object definition obtained from the database. It compares the code line by line using a Merge Join. You can even design a better way of comparing line by line by writing a script transformation to perform the comparision where you can ignore empty lines in the code or in the file.

The script below is used in the step “Read Script From ObjectDefinition” to read the definition from the database.

Data is sorted on both the columns on the inputs before the Merge Join.

The Merge join tranformation and Conditional split transformation are used to check if rows from either source do not match the rows from the second one. The variables User::case1 and User::case2 are set using a rowcount transformation used after the Conditional split transform. See the below 3 screenshots.

Data Flow Task 8 “Update script”

If either of the conditions, ”Script wasn’t in the working folder” or “Script was different from the database script” is met, Data Flow Task “Update script” runs

In this data flow task, same script component that is used in the earlier dataflow is used to read the script from the database. The only part that is different within this script component is that there is no RowNumber output column. This Data Flow task overwrites the script file in the Working folder

If the script file didn’t exist before, we will need to perform an svn add once the file is in the working directory, to make sure it is checked in within the last step.

The expression shown below is used for the arguments.

Execute Process Task 10 “SVN chekin”

In this task, the changes the checked in to the SVN server.

File System Task 11 “Delete Working Folder”

The working folder is deleted if everything is successful.

You can browse through each of the folders to see that your code is checked in.

Conclusion:

Schedule the package to run on a daily basis by storing the last successful extecution date and using this date for the LastAutoCheckInDate ssis variable.

There are few other ways you could implement the same process based on what tools are available to you and also what programming language you prefer working in. If you have Redgate SQL Compare tool, you can replace the “For Each Modified SQL Object” with Execute Process tasks that run the required SQL Compare command line instructions. You will not need to loop through each object at that point. You can send the list of names in xml file as input to the command line. You could also use a console application to build the same process. But whatever be the method, I think it will be worth the while as it makes it easier to version control your database code. Please make sure you test these packages before you use them. This package has been developed using Visual Studio 2005. You will need to customize them to suit your environment