BIML, you either love it or hate it - either you use it to totally generate hundreds of packages or a bucket load of data flow components all at the push of a button or you are perfectly happy using the SSIS editor, you can see how the package is made up and drag the little lines from one task to another and it is simple right, why bother using xml that is more complicated, you only need to create a single package with a few tasks on it, maybe a script component here or there.

I had the honour of talking at the excellent Sql Saturday Exeter this morning and wanted to share my slides and say thanks to the organisers, sponsors and everyone who came along to my session - I really enjoyed it and I hope you did to.

The slides are available:

https://the.agilesql.club/assets/talks/sqlsat15/SC-SSDT-CI.pptx

Enjoy!

It is sometimes useful to be able to use sqlpackage.exe to create a script that can be deployed manually or still automatically but at a later date plus you may not want to give the user or process that does this sysadmin permissions just so that it can generate a deploy script. The permissions you need in this case are:

SELECT permissions on dbo.__Refactorlog

VIEW DEFINITION at the database level

Ok so you have created an SSDT project and are currently congratulating yourself on how awesome and cool you are but then you realise that your project is like an empty void, completely devoid of any emotion, love or joy - there is no schema and code in the project and you have all that lovely T-SQL sitting in a database, now how the hell do you get it into SSDT?

UPDATE

I went and built this into my add-in for SSDT, more details https://the.agilesql.club/blog/Ed-Elliott/2015-11-17/Create-Stub-tSQLt-t… and https://the.agilesql.club/Projects/SSDT-Dev-Pack

Generating tSQLt tests from a dacpac

I use SSDT and tSQLt in my work and I am always interested in productivity tools to help speed up development and make it more streamlined, if there is something that can help us develop better or faster then we should use it.

I have been a great fan of the DacFx for a while and the sample Dacpac Explorer seems to get quite a lot of visits with one mention from ms devs internally using it as a learning reference!

I was reading a white paper from a Sql Server tools vendor about how to integrate their tools with SSDT and while it was very interesting there were a couple of inaccuracies and I wanted to clear one of them up and also to help people understand that although SSDT is integrated to Visual Studio it is not tied to it in any way to TFS. You can use SSDT with any source code provider such as TFS, Git, Mercurial, Svn or Source Safe etc although you really should not use source safe for anything as it is the least likely tool to keep your source safe!

Calling all London/Sussex Sql Developers does this sound like you?

• Would you like to set up Continuous Integration or Continuous Deployment
• Do you want to get your databases under source control
• Have a high code coverage of your app but zero tests for your database code?

Maybe you have thought how good it would be but you are a little lost at the options available, do you use SSDT and SqlPackage to deploy or SSMS with the Redgate source control addin, how do you deploy your code to different environments safely?

In part 3 of this series of blogs on “getting more agile” I am going to look at unit testing and why we need to do it.

My code is perfect, I need no stinking tests

Ah you are like me, my code is perfect as well, good point. blog end.

actually…your code may well be perfect but:

• a) It only got perfect by you writing the code and writing a test script to make sure it worked (you did at least test it once, didn’t you?)
• b) Everyone else in the world (basically everyone other that you and me who are the only people, dear reader, who write perfect code) write horrible code and do terrible things like change table and column definitions, the blighters
• c) Those other horrible developers and DBAs need examples of how to use the code
• d) They also need documentation on how it works, what it returns in all cases and what side effects it has as they are too simple to work it out for themselves, the welps

ok, I admit it, I am one of those welps - what is more, I make changes to code without examining every stored procedure, every function, every piece of application code to see if it will cause any issues - I am literally a renegade throwing caution to the wind plus I write very little documentation, I certainly never explain what I have done to a stored procedure, I don’t need to. I can do this because I write unit tests and make sure that every piece of code that can run is covered by a unit test.

What is it?

MergeUi is a addin (VSPackage) for Visual Studio / Sql Server Data Tools projects. It is a ui to create and edit T-Sql merge statements in pre and post deployment scripts.

In this screen shot you can see the merg statement that is generated using the data in the grid which is based on a table created using the metadata in the dacpac about the table:

In the second part of this series on getting more agile and bringing our Sql Server development practices up to date I am going to talk about why you should get your database under source control.

First a little rambling story, I was at the Redgate Sql in the city event in London last October and the speakers asked a few times who had their databases under source control, betwen 5 and 10 percent of the people there had their databases under source control. One speaker asked “who uses source control”, the person sitting next to me didn’t put his hand up and then the speaker asked “who has their databases under source control”, the gentleman then said he thought the first question was about databases, of course he had his code under source control, he would be stupid not to!