Bitmask data insertions in SSDT Post-Deployment scripts

jamiet — Mon, 11 Jun 2012 10:11:23 GMT

On my current project we are using SQL Server Data Tools (SSDT) to manage our database schema and one of the tasks we need to do often is insert data into that schema once deployed; the typical method employed to do this is to leverage Post-Deployment scripts and that is exactly what we are doing.

Our requirement is a little different though, our data is split up into various buckets that we need to selectively deploy on a case-by-case basis. I was going to use a SQLCMD variable for each bucket (defaulted to some value other than “Yes”) to define whether it should be deployed or not so we could use something like this in our Post-Deployment script:

IF ($(DeployBucket1Flag) = 'Yes')
BEGIN
   :r .\Bucket1.data.sql
END
IF ($(DeployBucket2Flag) = 'Yes')
BEGIN
   :r .\Bucket2.data.sql
END
IF ($(DeployBucket3Flag) = 'Yes')
BEGIN
   :r .\Bucket3.data.sql
END

That works fine and is, I’m sure, a very common technique for doing this. It is however slightly ugly because we have to litter our deployment with various SQLCMD variables. My colleague James Rowland-Jones (whom I’m sure many of you know) suggested another technique – bitmasks. I won’t go into detail about how this works (James has already done that at Using a Bitmask - a practical example) but I’ll summarise by saying that you can deploy different combinations of the buckets simply by supplying a different numerical value for a single SQLCMD variable. Each bit of that value’s binary representation signifies whether a particular bucket should be deployed or not. This is better demonstrated using the following simple script (which can be easily leveraged inside your Post-Deployment scripts):

/* $(DeployData) is a SQLCMD variable that would, if you were using this in SSDT, be declared in the SQLCMD variables section of your 
project file. It should contain a numerical value, defaulted to 0.
 
In this example I have declared it using a :setvar statement. Test the affect of different values
by changing the :setvar statement accordingly.
 
Examples:
:setvar DeployData 1   will deploy bucket 1
:setvar DeployData 2   will deploy bucket 2 
:setvar DeployData 3   will deploy buckets 1 & 2
:setvar DeployData 6   will deploy buckets 2 & 3
:setvar DeployData 31  will deploy buckets 1, 2, 3, 4 & 5
*/
:setvar DeployData 0
DECLARE  @bitmask VARBINARY(MAX) = CONVERT(VARBINARY,$(DeployData));
IF (@bitmask & 1 = 1)
BEGIN     PRINT 'Bucket 1 insertions';
END
IF (@bitmask & 2 = 2)
BEGIN     PRINT 'Bucket 2 insertions';
END
IF (@bitmask & 4 = 4)
BEGIN     PRINT 'Bucket 3 insertions';
END
IF (@bitmask & 8 = 8)
BEGIN     PRINT 'Bucket 4 insertions';
END
IF (@bitmask & 16 = 16)
BEGIN     PRINT 'Bucket 5 insertions';
END

An example of running this using DeployData=6

The binary representation of 6 is 110. The second and third significant bits of that binary number are set to 1 and hence buckets 2 and 3 are “activated”.

Hope that makes sense and is useful to some of you!

@Jamiet

P.S. I used the awesome HTML Copy feature of Visual Studio’s Productivity Power Tools in order to format the T-SQL code above for this blog post.

Parent-Child Build Scripts with SQLCMD

Mike C — Sun, 10 Jan 2010 18:33:00 GMT

On the SQL Server public programming newsgroup someone recently posted a question about an SSMS error ("Cannot parse script. 'System.OutOfMemoryException' thrown.") I hadn’t encountered this error myself, but the workaround is to break up very large scripts (50+ MB) into smaller scripts. Adam Machanic posted a T-SQL Tuesday challenge to post a solution to a puzzling situation, so this actually gives me a good opportunity to share how I structure my own build scripts -- which avoids this issue entirely.

When I create database build scripts, I use the SQLCMD utility to run them from the command line instead of using SSMS or another tool. SQLCMD has its own commands, which it parses separately from SQL/T-SQL statements. These commands are not understood by SQL Server or other scripting tools like SSMS (exception: you can run SSMS in SQLCMD mode, but that’s another story). These special SQLCMD commands all start with a ":" at the front of the line.

The SQLCMD command that makes parent-child structured build-scripts possible is the ":r" or "run" command, which tells SQLCMD to run another script file from within the current script file. In the figure below I’ve set up a local directory structure with database object creation scripts in subdirectories:

The \Scripts directory contains a Create.All.Sql script. This script uses the SQLCMD run command to execute the Database\Create.Database.Sql script, the Create.All.Schemas.Sql script, and so on. The Create.All.Schemas.Sql script calls the Person.Schema.Sql and Sales.Schema.Sql scripts in turn. The other Create.All.* scripts each call the object creation scripts in their subdirectories as well. Here’s what my Create.All.Sql script looks like:

/*
    Create All Items
*/


:r Database\Create.Database.sql
:r Schemas\Create.All.Schemas.sql
:r Types\Create.All.Types.sql
:r Tables\Create.All.Tables.sql

Each :r command kicks off the next level of child packages in turn.

SQLCMD has another great feature known as scripting variables that you can use to create dynamic scripts. Essentially you define a scripting variable on the command line with SQLCMD's -v command line option. Now the way scripting variables work, they are replaced wholesale in your scripts with their replacement value. So if you define a scripting variable named environment you can replace it with a value like "Dev", "QA" or "Prod" anywhere it occurs in your script. This is great for making dynamic scripts that need to be built across multiple environments.

In the example I've used a scripting variable named database. You can set the value of the database variable from the command line with the -v option. In the example below I set the database variable to the value "Test".

The nice thing about the SQLCMD scripting variables is that once you declare them you can access them from the parent script you run (in this case Create.All.Sql) or from any child scripts that are run (like Create.Database.Sql, Create.All.Schemas.Sql, Person.Schema.Sql and Sales.Schema.Sql). Here’s the Create.Database.Sql script from the example:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE $(database);
GO

The scripting variable is accessed in the script with $(database). The scripting variable is replaced with its value by SQLCMD, so in the example SQL Server sees this:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE Test;
GO

One thing to keep in mind when you use this scripting pattern is that every script should end with the batch terminator (default is "GO"). If not you could end up with one script running into another and get some strange, not-very-helpful error messages.

Another thing you need to know is that scripting variables are replaced wholesale with their replacement text. This makes them very flexible, since you can replace text anywhere in the script with anything you want. It can also be dangerous if your script is run by someone with malicious intent. A malicious user can replace a scripting variable with T-SQL statements that could damage your data or database structure; so keep your scripting variable-enabled scripts out of the hands of potentially malicious users.

I've attached a sample ZIP file with the directory structure shown in the example above. The scripts build out a few database objects from the AdventureWorks sample database.

Search results matching tags 't-sql' and 'SQLCMD'

Bitmask data insertions in SSDT Post-Deployment scripts

Parent-Child Build Scripts with SQLCMD