Geeks 4 Data

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!

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!

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 (whe...