Shared Database Code

If you use SQL Server Data Tools and deploy your database code using the SQL Server Database Project in Visual Studio, then read on.

If you need to deploy a database to multiple instances and have some objects only deploy to certain instances, read on.

If you didn't know that you can reference a database project within another database project and have the reference database schema included as part of the database, read on.

If you didn't know that you need to specifically configure your deployment to include composite objects when you have a 'composite' database project, you do now :-)

Later dudes!

Getting a DACPAC from a misbehaving database

We use SQL dacpacs for database references in our SQL Data Tools projects. Sometimes the database we need to reference is a vendor database with loads of issues in it, so if we try and extract a dacpac using management studio, it just bombs out with an ugly exception. I have seen all kinds of issues when doing this.

Your first though may be to re-create the database using scripts and only include the objects that you need. That works but it's a bit of a pain to look after. What we really need is way to extract the dacpac, warts and all, and the only way I know how to do that is by using the SqlPackage.exe command line tool.

Use the following to extract a simple dacpac for a reference:

C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe /Action:Extract /SourceServerName:SQLCLUSTER3 /SourceDatabaseName:$(DbName) /TargetFile:"C:\Temp\$(DbName) .dacpac"

Simples dudes!

ODBC, DSNs, SSIS Code Pages, metadata and BIML

The scenario:

GIVEN a SQL 2000 data source
AND a SQL 2016 destination
AND a metadata driven, BIML generated SSIS package to move data from source to destination
WHEN you try build the SSIS package using an OLEDB Connection
THEN SSIS says it can't connect because SQL 2000 is not supported

So, what do you do? Well obviously ODBC comes to mind and so you try that avenue (ala ODBC Driver for SQL Server) only to find you're presented with another unfriendly message:

Now what? Well, simply use the SQL Server provider and that will work. SSIS is able to use it with an ODBC connection, so all is good, until you execute your package and get the most excellent of exceptions, the dreaded VS_NEEDSNEWMETADATA! Or, in my words "AAAAHHHHGGGRRRRHHH!!!" What just happened? My package built without any issues and didn't throw any warnings so why does this happen when I run it? Weird ODBC behavior I guess.

Turns out that the ODBC connection defaults to UNICODE (whereas the OLEDB defaults to ANSI). Now in the yesterday world of hand-cranking your SSIS packages you would just set the BindCharColumnAs property to ANSI and everything would just work. BIML doesn't give you the option to set this so you need to find another way to use the connection if you want to automate the generation of the package.

Linked Servers my friend! Yes, by simply setting up a linked server from the target to the source SQL Server instance, we are able to access the source table using the linked server reference and need only to make a slight tweak to the BIML. Job's a good'n!

Later dudes!