Category: SQL Server Data Tools

Ginger Grant shows how to create a new SSIS or SSRS project for SQL Server 2016:

In this version of SQL Server Data Tools, Microsoft has finally addressed the common problem of needing to maintain multiple versions of SSIS packages for the different server versions. No longer do you need three different applications to maintain code for SQL Server 2012, 2014 and now 2016. All of these versions are supported with SSDT for Visual Studio 2015. SQL Server will detect which version the code was last saved in so that you don’t have to worry about accidently migrating code. You also have the ability to create an SSIS package in 2012, 2014 or 2016. To select the version you want, right click on the project and select Properties. Under Configuration Properties->General as shown in the picture, the TargetServerVersion, which defaults to SQL Server 2016, has a dropdown box making it possible to create a new package in Visual Studio 2015 for whatever version you need to support. Supporting the ability to write for different versions, is a great new feature and one which I am really happy is included in SSDT for Visual Studio 2015.

I’m also glad that Microsoft has made this move.  It is no fun having two or three different versions of Visual Studio installed because some component requires an older version.

Matt Smith ran into an issue when trying to compare two databases using SQL Server Data Tools:

This appeared to work but didn’t display any results. When you look at the bottom status bar it reads

Comparison complete. No differences detected. Restricted comparison. See Error List for details.

I knew there we at least some differences. I then click on the Error List table below it which revealed

The reverse engineering operation cannot continue because you do not have View Definition permission on the ‘Warehouse’

The answer is pretty simple, so read on.

Ed Elliott has a three-part series on database projects in SQL Server Data Tools.

Part 1:  What is SSDT?

The SSOX or SQL Server Object Explorer is a cool utility that lets you connect to a live database and do things to it like debug stored procedures or update individual objects. It also lets you see a view of you projects after all references have been resolved so if you use “Same Database” references you can see how your end project will end up – really useful.

Part 2:  Deploying projects

Using the DacServices via whatever method you want (schema compare, sqlpackage, powershell, something else?) really makes it simple to spend your time writing code and tests rather than manual migration steps. It constantly amazes me who well rounded the deployment side of things is. Every time I use something obscure, something other than a table or procedure I half expect the deployment to fail but it just always works.

Over the last couple of years I must have created hundreds if not thousands of builds all with their own release scripts across tens of databases in different environments and I haven’t yet been able to break the ssdt deployment bits without it acyually being my fault or something stupid like a merge that goes haywire (that’s one reason to have tests).

Part 3:  the .Net APIs

The ScriptDom has two ways to use it, the first is to pass it some T-SQL (be it DDL or DML) and it will return a representation of the T-SQL in objects which you can examine and do things to.

The second way it can be used is to take objects and create T-SQL.

I know what you are thinking, why would I bother? It seems pretty pointless to me. Let me assure you that it is not pointless, the first time I used it for an actual issue was where I had a deployment script with about 70 tables in. For various reasons we couldn’t guarantee that the tables existed (some tables were moved into another database) the answer would have been to either split the tables into 2 files or manually wrap if exists around each table’s deploy script. Neither of these options were particularly appealing at the particular point in the project with the time we had to deliver.

This is a great series with a lot of informative links.

Ed Elliott has another update to SSDTDevPak:

How do I get it?

i have now pushed it to the visual studio gallery and will use this for all future updates so feel free to grab it from:

https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5b8a05dc7906.

Also check out T-SQL Clippy.

Ed Elliott has just added another SSDT Dev Pack feature:

Open a document with a table valued function or procedure, click tools–> SSDT Dev Pack –> QuickDeploy or use the keyboard options in SSDT to make Tools.QuickDeploy to a key combination (I use Ctrl+Q, Ctrl+D but it is a little awkward) and your code will be deployed, any messages are added to the standard output window.

If the active document has anything other than stored procedures or table valued functions then they will not be deployed and also if you have more than one procedure of function, all of them in the active document will be deployed.

Ed is quickly becoming one of my favorite bloggers.  It seems like every other day, he has a new tool available for us.