SQL Server Data Tools 16.5 Release

The SQL Server Data Tools team is pleased to announce an update for SQL Server Data Tools (SSDT) is now available. The SSDT 16.5 release adds a number of improvements to the connection experience, SqlPackage and DacFx API support for generating a deployment report and scripts during publish, and as usual a number of bug fixes across the tool.

Get it here:

Download SSDT GA 16.5 for Visual Studio 2015 and Visual Studio 2013

This release will be available through Visual Studio Extensions and Updates notification soon.

Download Data-Tier Application Framework 16.5

What’s new in SSDT?

Database Tools

Connection Improvements

Managing your server and database connections is even easier than before.

  • The new search box in the Browse tab helps you filter your Local servers, Network servers and Azure SQL Databases. This is very useful if (like us) you have a large number of servers or databases appearing in these lists.
  • The History tab has right-click menu options to pin / unpin favorites, and a new option to remove connections from the History.

SqlPackage and DacFx API Improvements

Using SqlPackage.exe and the DacFx APIs you can now generate a deployment report, deployment script, and publish to a database all in one action. This is a timesaver for anyone who likes to keep a report of what was published during a deployment. Another benefit is that for Azure scenarios, separate scripts for the master database and the deploy target database are created. Up to now a single script was created which was not useful for repeated deployments.

For SqlPackage’s Publish and Script actions, two new arguments have been added.

  • DeployScriptPath (shortname: dsp). This is an optional path to write the deployment script to. For Azure deployment, if there were TSQL commands to create of modify the DB a master script will be written to the same path but with “Filename_Master.sql” as the output file name.
  • DeployReportPath (shortname: drp). This is an optional path to write the deployment report to.

Note that for the Script action, either the existing Output Path arguments or the new script/report-specific arguments should be used, but not both.

Sample usage:

Publish Action Sqlpackage.exe /a:Publish /tsn:(localdb)\ProjectsV13 /tdn:MyDatabase /deployscriptpath:”My\DeployScript.sql” /deployreportpath:”My\DeployReport.xml”
Script Action Sqlpackage.exe /a:Script /tsn:(localdb)\ProjectsV13 /tdn:MyDatabase /deployscriptpath:”My\DeployScript.sql” /deployreportpath:”My\DeployReport.xml”

In DacFx, two new APIs have been added: DacServices.Publish() and DacServices.Script(). These also support performing publish + script + report actions in a single operation. Sample usage:

DacServices service = new DacServices(connectionString);
using(DacPackage package = DacPackage.Load(@"C:\My\db.dacpac")) {
var options = new PublishOptions() {
    GenerateDeploymentScript = true// Should a deployment script be created?
    GenerateDeploymentReport = true// Should an xml deploy report be created?
    DatabaseScriptPath = @"C:\My\OutputScript.sql"// optional path to save script to
    MasterDbScriptPath = @"C:\My\OutputScript_Master.sql"// optional path to save master script to
    DeployOptions = new DacDeployOptions()
};

// Call publish and receive deployment script & report in the results
PublishResult result = service.Publish(package, "TargetDb", options);
Console.WriteLine(result.DatabaseScript);
Console.WriteLine(result.MasterDbScript);
Console.WriteLine(result.DeploymentReport);

// Call script and receive deployment script & report in results
result = service.Script(package, "TargetDb", options);
Console.WriteLine(result.DatabaseScript);
Console.WriteLine(result.MasterDbScript);
Console.WriteLine(result.DeploymentReport);

Analysis Services & Reporting Services

SSAS tabular designer DAX parser has improved performance when working with large DAX expressions.

For more information, please read this Analysis Services blog post.

Fixed / Improved this month

Database Tools

Connect bug 3055711 – Columns cannot be selected from CROSS APPLY OPENJSON with explicit schema
Fixed – issue with Auto-generated History table indexes, where DacFx dropped index on redeployment
Fixed – issue with DacFx batch parser not parsing escaped bracket ‘]’ characters, which caused publish to fail
Improved – SqlPackage now includes descriptions for each action in the help output
Fixed – The “Remember Password” option in the connection dialog was not being preserved when editing Advanced options and when editing a connection string saved in Publish, Schema Compare and other files
Fixed – For connections show in the History tab with IntegratedAuthentication=true, the Authentication field in connection properties was left blank. This now shows “Windows Authentication” as expected
Fixed – Changes to the SQL Server Tools Intellisense settings under Tools -> Options -> Text Editor were not being preserved
Improved – the Pin/Unpin button in the connection dialog History tab is now more compact, reducing the likelihood of a scrollbar appearing
Fixed – several accessibility issues in the connection dialog were fixed.

Analysis Services & Reporting Services

Fixed an issue in SSDT AS tabular designer where clicking the scrollbar thumb in data grid crashed in certain situations
Fixed an issue where option to impersonate connection as current user in SSDT AS tabular wasn’t available
Fixed an issue in SSDT AS tabular designer where expanding the formula bar too far could make the project unable to re-open
Fixed a crash in SSDT AS tabular designer that would occur on key down if table tab was selected
Fixed an issue in SSDT AS projects where Analyze in Excel would not connect to down-level AS server versions

Integration Service

Fixed Connect bug 1608896: Move Multiple Integration Service Package Tasks

­­­Contact us:

If you have any question or feedback, please ping @sqldatatools on twitter, visit our forum and for bugs please create bug reports on our Microsoft Connect page. We are fully committed to improve the SSDT experience and look forward to hearing from you!