Dir2Dac and github2dacpac - create dacpacs from script folders or the internet
I like writing go (golang not the batch separator) and one of the things that I like is that if you want to use something from github you literally type “go get https://github.com/user/repo” and the code is downloaded and installed ready to be used.
I also like write t-sql using ssdt and sometimes I want to reference other things like tSQLt or maybe a set of admin scripts or something but unlike go, to reference them what I need to do is:
- 1. find the script
- 2. download the script
- 3. depending on how the script is written, either:
- a. run the script against a database and import that into ssdt
- a. add the script to an ssdt project
- 4. if I don’t want the script in my project, I create a separate project, build a dacpac then throw away the project
- 5. add the reference in my actual ssdt project
This really is a pain in the proverbial. So I have written a tool that will take the scripts in a directory and build a dacpac from it. When you build the dacpac you specify the version and any references which is really useful as most admin scripts reference the master database. The full list of things you can do using Dir2Dac is:
- 1. Put all the scripts matching a name filter in one or more folders into a dacpac
- 2. The scripts are put through the TSqlModel and so validated for correctness (no sneaking broken scripts in here)
- 3. Specify most of the TSqlModelOptions (https://technet.microsoft.com/en-us/library/microsoft.sqlserver.dac.mode…(v=sql.110).aspx)
- 4. Add references to other dacpacs or system databases including adding any required SqlCmdVars
- 5. Where the scripts are not SSDT friendly such as pure create scripts using /fix will attempt to remove any guff that goes in upgrade scripts (if exists checks etc)
- 6. Let you add pre and or post deploy scripts
Sounds cool right?
This is to allow situations where you have scripts that are not written in SSDT but you want to use in SSDT projects, perhaps you have one developer who refuses to join the 21st century and put down SSMS and now they can check code into a a folder and that folder can be turned into a dacpac and then added into the project with everyone else.
Maybe you have an admin database and every time there is a new sp_blitz or sp_whoisactive you need to deploy it manually. What you could do is download the scripts you want (the authors want people to download them rather than allowing shareable links to download the content directly so please don’t try to automate downloading the scripts) and then use dir2dac to package add them into your admin database or whatever.
ok with go (golang) you get to download directly from github etc, I want that. I have a second repo github2dacpac that has some powershell scripts to demo how to download from github and other sites and then reference dir2dac to generate scripts, where they support different versions of SQL Server I spit out a dacpac for each version (90–>120) including the correct version of master.dacpac where appropriate.
There are three sample scripts to get you started, they are:
generate-sql-merge.ps1 - this does a git clone on https://github.com/readyroll/generate-sql-merge (for this script to run you will need the windows console version of git installed - basically if you can do git clone from powershell then this will work)
MichelleUfford.ps1 - this also goes a git clone on https://github.com/MichelleUfford/sql-scripts
If you don’t have git installed or the scripts aren’t hosted on git then you can download each file individually, as an example I have added Random.ps1 which downloads two scripts that I found, these are the original urls:
The powershell scripts can definitely be improved they are just to give an indication of what you could do :). If I feel the urge i’ll make a cmdlet with a download / clone and then a generate package but don’t expect it any time soon :)
To use these I am hoping you can just grab one of the scripts from:
and run them - they will download the dir2dac tool which *should* have the right references and also be able to download the references to master.dacpac etc.