Jonathon

Jonathon Eve-O'Connor's Blog

Stop Automatic checkout of SSIS Packages in Visual Studio

Every once in a while I work on a project affected by this issue. What happens is that upon loading a solution the project(s) are immediately checked out when no actual changes have been made to them. The issue is very irritating and can hinder collaborative work. I think it is hard to produce a definitive list of all the potential causes of this issue, however I have recently found 2 sets of circumstances which cause this issue, when I happen upon another I will post it here along with any solutions I have found to the issue.

Database File Not In Source Control

I found the changes occurring were in one of the xml files for the SSIS project – each SSIS project in a solution has a .dtproj file. According to the Microsoft website the .dtproj file “contains information about project configurations and items such as packages”. On investigation I can see that one of the main things this file does is to keep a record of all the files required for the project. I found that in projects where these issues were occurring the part of the file that references the .database file was changing:

<Database> <Name>ETL CheckOutIssues 11.database</Name> <FullPath>ETL CheckOutIssues 11.database</FullPath> </Database>

Projects which did not have the issue had xml that looked like this (no number):

<Database> <Name>ETL CheckOutIssues.database</Name> <FullPath>ETL CheckOutIssues.database</FullPath> </Database>

I looked a bit further and I found that the .database file was not included in the Source Control for the solution. However if VS cannot find the file it will create a new one and change the reference in the .dtproj file appending a number which increments each time it fails to find the file.

Furthermore new .database files are not included in source control upon creation by default so if a project is renamed and a developer gets the project source for the first time it will create a new .database file with a new naming format <newprojectname>.database. With many subsequent check-ins from different users each one changing the name of the database we can end up with a situation where the number will increment further and cause a check out each time a developer opens the solution. To resolve this we need to standardise the names and make sure that the dtproj file and the .database file names match.

 

The solution

Fortunately the solution to the issue is quite straightforward. Right click the project in Visual Studio and unload it.

image

1. Open the folder Find the .database file and rename it to the original name eg:  ‘ETL CheckOutIssues.database’ (you may need to first delete a file to do this).

2. Open the .dtproj xml file edit the references so that they correctly reference the renamed file and the names of the .database and .dtproj files match. eg:

<Database> <Name>ETL CheckOutIssues.database</Name> <FullPath>ETL CheckOutIssues.database</FullPath> </Database>

 

3. Right click the file and add it to source control

image

4. Navigate to the folder above in Windows Explorer and check in the changes

image

5. Reload the solution – you should see the SSIS project is not checked out automatically

image

6. Ensure all developers working on the project do a get latest on the SSIS project and overwrite the existing files

image

image

To change the behaviour of TFS so that .database files will be included in source control as standard you need to make the following changes:

1. In visual Studio Navigate to Team > Team Project Collection Settings > Source Control

image

2. Under File types add a new type with extension *.database

image

Different Versions Of Data Tools

I recently found another issue which causes this where several people working on a project have slightly different versions of ‘Business Intelligence Templates for Visual Studio’ (Data Tools). I found that the copy on my machine (whilst there were no available updates when checking through VS) had an older version number which it includes in the .dtproj file. Whilst this doesn't impact the ability to develop packages on different machines running the different version, it does serve to confuse TFS and force a version checkout as the below comparison shows.

image

To resolve you need to ensure everyone has the same version of Data Tools on their machine. The latest version for VS2013 is freely available for download from here http://www.microsoft.com/en-gb/download/details.aspx?id=42313. Once the file is downloaded you need to select the oddly titled option to ‘Perform a new Installation of SQL Server’.

image

Following this you must make sure that the box for the BI Templates is ticked (it may not be if you already have a slightly earlier version installed).

image

Once the installation completed the issue the version number stayed the same meaning the .dtproj file was no longer checked out each time different users opened an IS project.

Loading