使用PowerShell将dacpac转换为数据库对象的文件夹结构

I'm working on integrating SQL Server databases into our in-house version control/deployment utility, which is built with powershell,and uses Github as a repository.

我正在努力将SQL Server数据库集成到我们的内部版本控制/部署实用程序中,该实用程序使用powershell构建,并使用Gi​​thub作为存储库。

Using the excellent sqlpackage.exe utility, I have been able to add a process whereby a developer can extract their current changes into a dacpac and store it in Github, then do the opposite in reverse when they want to get the latest version. However, because the .dacpac is a binary file, it's not possible to see differences in git. I have mitigated this somewhat by unzipping the dacpac before storing in in source control, so contained xml files are added instead. However, even though these files are text-based, they are still not easy to look through and find differences.

使用优秀的sqlpackage.exe实用程序,我已经能够添加一个进程,开发人员可以将其当前更改提取到dacpac并将其存储在Github中,然后在他们想要获取最新版本时反向执行相反的操作。但是,因为.dacpac是一个二进制文件,所以不可能看到git的差异。我通过在存储在源代码控制中之前解压缩dacpac来缓解这种情况,因此添加了包含的xml文件。但是,即使这些文件是基于文本的,它们仍然不容易查看并发现差异。

What I would like to do, is convert the dacpac into a folder structure similar to what would be seen in SSMS (with all the database objects such as triggers, sprocs etc in their respective folders), store that in Github, and then convert it back into a dacpac when a client checks out the code. However, there doesn't seem to be any function in sqlpackage.exe for this, and I can't find any documentation. Is there any command line tool I can use to this through Powershell?

我想做的是将dacpac转换为类似于SSMS中所见的文件夹结构(所有数据库对象,例如各自文件夹中的触发器,sprocs等),将其存储在Github中,然后将其转换当客户端检出代码时,回到dacpac。但是,sqlpackage.exe似乎没有任何功能,我找不到任何文档。我可以通过Powershell使用任何命令行工具吗?

2 个解决方案

#1

Using the public APIs for DacFx you can load the dacpac, iterate over all objects, and script each one out. If you're willing to write your own code you could write each one to its own file based on the object type. The basic process is covered in the model filtering samples in the DacExtensions Github project. Specifically you'll want to do something like the ModelFilterer code that loads a dacpac, queries all objects, scripts them out - see the CreateFilteredModel method. I've put a sample that should mostly work below. Once you have this, you can easily do compare on a per-object basis.

使用DacFx的公共API,您可以加载dacpac,遍历所有对象,并为每个对象编写脚本。如果您愿意编写自己的代码,可以根据对象类型将每个代码写入自己的文件中。 DacExtensions Github项目中的模型过滤示例中介绍了基本过程。具体来说,您需要执行类似于加载dacpac的ModelFilterer代码,查询所有对象,将其编写出来的内容 - 请参阅CreateFilteredModel方法。我已经放了一个应该在下面工作的样本。完成后,您可以轻松地按对象进行比较。

using (TSqlModel model = new TSqlModel(dacpacPath))
{
    IEnumerable<TSqlObject> allObjects = model.GetObjects(QueryScopes);
    foreach (TSqlObject tsqlObject allObjects)
    {
        string script;
        if (tsqlObject.TryGetScript(out script))
        {
            // Some objects such as the DatabaseOptions can't be scripted out.

            // Write to disk by object type
            string objectTypeName = tsqlObject.ObjectType.Name;
            // pseudo-code as I didn't bother writing.
            // basically just create the folder and write a file
            this.MkdirIfNotExists(objectTypeName);
            this.WriteToFile(objectTypeName, tsqlObject.Name + '.sql', script);
        }
    }
}

This can be converted into a powershell cmdlet fairly easily. The dacfx libraries are on nuget at https://www.nuget.org/packages/Microsoft.SqlServer.DacFx.x64/ so you should be able to install them in PS and then use the code without too much trouble.

这可以很容易地转换为powershell cmdlet。 dacfx库位于https://www.nuget.org/packages/Microsoft.SqlServer.DacFx.x64/上,因此您应该可以在PS中安装它们,然后使用代码而不会有太多麻烦。

#2

Based on the other post I was able to get a script working. Caveat is you'll have to try the types till you get what you want... The way it is no it trys to put the full http or https value for some of the objects.

基于另一篇文章,我得到了一个脚本工作。警告是你必须尝试这些类型,直到你得到你想要的东西......不管它是不是试图为某些对象提供完整的http或https值。

param($dacpacPath = 'c:\somepath' Debug', $dacpac = 'your.dacpac')
Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll'
add-type -path 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.Extensions.dll'
cd $dacpacPath
$dacPack = [Microsoft.SqlServer.Dac.DacPackage]::Load(((get-item ".\$dacpac").fullname))
$model =[Microsoft.SqlServer.Dac.Model.TSqlModel]::new(((get-item ".\$dacpac").fullname))
$queryScopes = [Microsoft.SqlServer.Dac.Model.DacQueryScopes]::All
$return = [System.Collections.Generic.IEnumerable[string]]
$returnObjects = $model.GetObjects([Microsoft.SqlServer.Dac.Model.DacQueryScopes]::All)
$s = ''
foreach($r in $returnObjects)
{
   if ($r.TryGetScript([ref]$s))
   {
    $objectTypeName = $r.ObjectType.Name;
    $d="c:\temp\db\$objectTypeName"
    if(!(test-path $d ))
    {
        new-item $d -ItemType Directory
    }
    $filename = "$d\$($r.Name.Parts).sql"

    if(! (test-path $filename))
    {new-item $filename -ItemType File}
    $s | out-file  $filename -Force
    write-output $filename
   }

}