THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Folders in SQL Server Data Tools

Recently I have begun a new project in which I am using SQL Server Data Tools (SSDT) and SQL Server Integration Services (SSIS) 2012. Although I have been using SSDT & SSIS fairly extensively while SQL Server 2012 was in the beta phase I usually find that you don’t learn about the capabilities and quirks of new products until you use them on a real project, hence I am hoping I’m going to have a lot of experiences to share on my blog over the coming few weeks. In this first such blog post I want to talk about file and folder organisation in SSDT.

The predecessor to SSDT is Visual Studio Database Projects. When one created a new Visual Studio Database Project a folder structure was provided with “Schema Objects” and “Scripts” in the root and a series of subfolders for each schema:

image

Apparently a few customers were not too happy with the tool arbitrarily creating lots of folders in Solution Explorer and hence SSDT has gone in completely the opposite direction; now no folders are created and new objects will get created in the root – it is at your discretion where they get moved to:

image

After using SSDT for a few weeks I can safely say that I preferred the older way because I never used Solution Explorer to navigate my schema objects anyway so it didn’t bother me how many folders it created. Having said that the thought of a single long list of files in Solution Explorer without any folders makes me shudder so on this project I have been manually creating folders in which to organise files and I have tried to mimic the old way as much as possible by creating two folders in the root, one for all schema objects and another for Pre/Post deployment scripts:

image

This works fine until different developers start to build their own different subfolder structures; if you are OCD-inclined like me this is going to grate on you eventually and hence you are going to want to move stuff around so that you have consistent folder structures for each schema and (if you have multiple databases) each project. Moreover new files get created with a filename of the object name + “.sql” and often people like to have an extra identifier in the filename to indicate the object type:

image

The overall point is this – files and folders in your solution are going to change. Some version control systems (VCSs) don’t take kindly to files being moved around or renamed because they recognise the renamed/moved file simply as a new file and when they do that you lose the revision history which, to my mind, is one of the key benefits of using a VCS in the first place. On this project we have been using Team Foundation Server (TFS) and while it pains me to say it (as I am no great fan of TFS’s version control system) it has proved invaluable when dealing with the SSDT problems that I outlined above because it is integrated right into the Visual Studio IDE. Thus the advice from this blog post is:

If you are using SSDT consider using an Visual-Studio-integrated VCS that can easily handle file renames and file moves

I suspect that fans of other VCSs will counter by saying that their VCS weapon of choice can handle renames/file moves quite satisfactorily and if that’s the case…great…let me know about them in the comments. This blog post is not an attempt to make people use one particular VCS, only to make people aware of this issue that might rise when using SSDT.

More to come in the coming few weeks!

@jamiet

Published Sunday, April 15, 2012 4:35 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter Schott said:

I never really had a problem with the folder structure created by DB Projects after getting a little used to it. I use the Project view regularly and know where my files will be without needing to drill in to the wrong place. Looks like it's time for an add-on or something to help organize those files. If they're at least together in the same structure, I don't care as much about .table.sql or .proc.sql naming.

Besides still getting crashing errors on deploy of VS2010 DB Projects, I don't think we're ready to go to SSDT at this point. We also lost the whole EF integration as part of the roadmap and that would have been a huge selling point to our dev team. :( Guess we're sticking with DB Projects for the time being.

April 18, 2012 6:31 PM
 

Peter Schott said:

We're using GitHub for our versioning system now. It handles moves/renames relatively well, though they sometimes show as delete/create instead. I can see that possibly being a problem at some point if we need decent logging of files/folders that were renamed or moved, but it's worked so far.

April 18, 2012 6:33 PM
 

SSIS Junkie said:

I have been using SQL Server Data Tools (SSDT) both at work and on some hobby projects for quite a few

May 8, 2012 6:07 PM
 

SQLSophist said:

This caught me out as well; searching for this is how I came across this post.

I observe that when you create a project by importing from an existing database, it will create the "old" folder structure, but only as much of it as is necessary - I just imported a database that contains one table, and I now have a "\dbo\Tables\" folder, but no "\dbo\Views\".

Think I might have to create a template of some kind to use around here.

June 12, 2012 11:20 AM
 

jamiet said:

I've just discovered this post which might be useful:

How to create the old database project folders for SQL Server Data Tools.

(http://zogamorph.blogspot.co.uk/2012/06/how-to-create-old-database-project.html)

June 12, 2012 11:58 AM
 

Michael Ball said:

Jamie,

I've read your blog for years and have taken quite a bit away from it but now I can return the favor a bit.  There is another workaround that doesn't involve injecting XML blocks into your project directly.

Here's the steps for another workaround:

1. Create your new database project as a DBPROJ file inside VS2010 or SSDT.  This creates the folder structure.

2. In the Solution Explorer, right-click over the project and select "Convert to SQL Server Database Project..." and walk through the short wizard.

3. When the wizard completes the conversion, you should see the old folder structure in the new project type.

Hope this helps someone out there.

Michael

August 10, 2012 12:48 PM
 

Peter Schott said:

Michael,

 That does convert the folder structure, but the next time you import anything into your project, it will be created in the new structure and you'll need to move files around.

August 10, 2012 2:34 PM
 

Michael Ball said:

Agreed, Peter.  At least you have the structure built out for you at the start of your project.  It's not a perfect solution but it helps a bit.

I have seen a post from Microsoft addressing this issue and they mentioned that they'd look into adding this (back) into the project at some point in the future.  I guess enough people complained.

August 14, 2012 10:51 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement