Share via


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 resultsPublishResult 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 resultsresult = 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!

Comments

  • Anonymous
    October 26, 2016
    Thanks for all the work, love the sprint schedule compared to days of old. On that note, is there anything I can do to get the Perspective/Hierarchy bug with 1103 models pushed up in the priorities. As is the SSDT for 2015 are useless except for 1200 models, due to the code loss that occurs.Thanks
    • Anonymous
      October 26, 2016
      Hi John - I am not familiar with the bug you are referring to. Do you have a connect link or more detail you can provide?
      • Anonymous
        October 26, 2016
        The comment has been removed
  • Anonymous
    October 26, 2016
    Hi,thanks for the new release. What happend to https://connect.microsoft.com/SQLServer/Feedback/Details/2779379 ? Is it fixed in this release as well?regards
    • Anonymous
      October 27, 2016
      It looks like this has been fixed now.Thanks Kevin
  • Anonymous
    October 27, 2016
    The SQLPackage and DacFx change are big improvements. Id the NuGet package Microsoft.Data.Tools.Msbuild going to be updated to include the changes any time soon?
    • Anonymous
      October 27, 2016
      Yes, we're working on getting the MSBuild nuget package updated. Don't have a firm ETA but either this week or early next week.Kevin
      • Anonymous
        October 28, 2016
        The MSBuild nuget update is now live.
  • Anonymous
    November 01, 2016
    It seems that using JSON_VALUE() and [jsonextract] syntax and other SQL 2016 features is not supported yet in SSDT. Any word on when they will be supported?We are trying to use all the new shiny features in SQL Server 2016 but SSDT doesn't support the syntax. It reports warnings and errors when we build our project on stuff that is working for us in the real database.Warren
    • Anonymous
      November 01, 2016
      Hi Warren, I quickly verified that in SSDT 16.5 JSON_VALUE() shows correctly as a built-in function and is handled. See snippet below. I cannot find an example for [jsonextract] - could you please clarify what you mean by this?GOCREATE VIEW v1ASSELECT JSON_VALUE(LocationName, '$.info.address.PostCode') as PostCodeFROM T1Regards,Kevin
  • Anonymous
    November 01, 2016
    Hi,I’ve posted a defect few months ago (https://connect.microsoft.com/SQLServer/feedback/details/2906200) and was told on the 15/09 that it was fixed and would be part of the next release.However, I’ve tried this one and STRING_SPLIT is still not recognised by SSDT.Anything you could help me with?Very much appreciatedThanks
    • Anonymous
      November 04, 2016
      Hi Fred, one of our team members is investigating this. We do not have a firm ETA for the fix, but we'll update the Connect bug once we know more. This is an interesting behavior for a built-in function as it essentially returns a column source, which is unusual so we need to take a bit more time than usual to figure out the correct solution.- Kevin
  • Anonymous
    November 04, 2016
    Please see this (very old) MS Connect feature request that has never been commented on or addressed –https://connect.microsoft.com/SQLServer/feedback/details/487106/allow-sub-folders-in-ssrs-projectsPlease address this in some fashion, at least acknowledge that it’s a requested feature from users. The workaround involving multiple projects in the solution requires duplicating shared data sources and shared data sets and is not a good practice.
  • Anonymous
    November 09, 2016
    Anyone else get the following error message when trying to install this? [0460:1C38][2016-11-09T16:15:35]: Acquiring package: VSTALS2015, payload: VSTALS2015, download from: https://go.microsoft.com/fwlink/?LinkId=832089&clcid=0x409[0460:1C38][2016-11-09T16:15:35]: Error 0x80072efd: Failed to send request to URL: https://go.microsoft.com/fwlink/?LinkId=832089&clcid=0x409I can hit the url manually from IE or chrome just fine.
    • Anonymous
      November 22, 2016
      YES. I am getting this issue to - this is so frustrating, I have been trying to get this fixed for a few days now. Help please Microsoft!
    • Anonymous
      January 11, 2017
      You can download the ISO version, it worked for me.
  • Anonymous
    November 15, 2016
    After installing the latest SSDT for 2013, I am no longer able to deploy a database via MSBUILD. For example, the following command worked fine prior to the update, now it does not:MSBUILD "C:\Users\XYZ\Desktop\temp\Testing\TestProject\TestProject.sqlproj" /t:build "/p:Platform=AnyCPU" /t:deploy "/p:TargetConnectionString=Data Source=localhost;IntegratedSecurity=True" /p:TargetDatabase=TestDeployDb /p:Configuration=Release /p:VisualStudioVersion=12.0
  • Anonymous
    December 01, 2016
    Thanks for thisFixed Connect bug 1608896: Move Multiple Integration Service Package Tasks and thisSSDT 14.0.60525.0 sequence containers causing designer window to scroll.Please pay more attention on SSIS, the tools needs an update and an improvement like you did it with Reporting Services :).
  • Anonymous
    December 16, 2016
    With this update, Analysis Services Tabular projects have become unusable for me! I cannot save a model, or bring up the properties page for the project. Every action leads to the error:Cannot evaluate the item metadata "%(FullPath). " The item metadata "%(Fullpath)" cannot be applied to the path "obj\Development|x86\MyAssemblyName.exe" Illegal characters in path. C:\Program Files (x86)\MSBuild\14.0\bin\Microsoft.Common.CurrentVersion.targetsI tried uninstalling and reinstalling VS2015 entirely, hoping it would reset whatever templates or MSBuild targets tabular projects use. But a fresh reinstall did not fix the problem. I suspect the uninstall did not remove everything.My development is at a halt. Please help!
  • Anonymous
    January 04, 2017
    Any chance of an update - this post is now over two months old and I've not heard anything further about the development of SSDT.
    • Anonymous
      January 30, 2017
      +1 team needs to explain what works with VS2017RC, and cleanup the dilemma between SSDT & SSDT-BI (what is baked-in and what needs extension).
  • Anonymous
    January 12, 2017
    We are getting constant crashes, will there be an update soon that will address stability?
  • Anonymous
    January 17, 2017
    Hey team! Any plans/word, even a hint if F.o.l.d.e.r.s. i.n. R.e.p.o.r.t. P.r.o.j.e.c.t.s. is planning to be addressed? Ever? Just some feedback, any feedback, would be valued by the community! cmon, waiting..... waiting... (7 years now (personally for 4 years) item has - 260 votes!)https://connect.microsoft.com/SQLServer/feedback/details/487106/allow-sub-folders-in-ssrs-projects
  • Anonymous
    January 26, 2017
    The comment has been removed
  • Anonymous
    February 03, 2017
    The comment has been removed
  • Anonymous
    February 03, 2017
    I am developing an application in ASP MVC Core. I cannot find a version of ReportViewer that works with Core. Is one available somewhere so I can present reports from SSRS in my application?
  • Anonymous
    February 06, 2017
    Post is now over three months old.
  • Anonymous
    February 06, 2017
    Is it possible to target a single database with multiple dacpacs without using a composite project?In our scenario, we are creating isolated databases for each customer having a schema defined by multiple fully independent projects.Though this appeared to be impossible (when documented in the past), is this still the case?Have there been changes that would allow my scenario to be supported?
  • Anonymous
    February 08, 2017
    Is there any way to look at the table column's source fieldname after renaming it?
  • Anonymous
    February 14, 2017
    Hii dont now what you guys doing, but ssdt tool for visual studio 2015 update 3 with every release run into more and more errors and defects, my teams Accused me for there problems, and in alm tools of microsoft this tool is the worst among them, for example1- in scmp files 20% of times when i want to change source or target to db in instance of sql server i get empty Database: ! and i must reset vs to dont see error for while.2- no check filter is gone and others like "ignore fill factor" does not work.3- i get somw wired errors in scmps files toothis tool you guys responsible is so vital for alm process, please check this problems and more others i dont mentionthank you
  • Anonymous
    February 18, 2017
    The comment has been removed
  • Anonymous
    February 21, 2017
    Post is now over four months old.
  • Anonymous
    February 28, 2017
    Is TargetServerVersion property available in VS2013?Thank you
  • Anonymous
    March 01, 2017
    Please could you address the 271 up-voted connect item https://connect.microsoft.com/SQLServer/Feedback/Details/487106Allow Sub Folders in SSRS using SSDT. I'm currently working for a FTSE 100 company and this is making the development and deployment experience more difficult than it should be. Also, please allow the properties of the folders to be changed in a SSDT.Thank you
  • Anonymous
    March 09, 2017
    It's now March 2017. Aren't you going to say anything about VS 2017 ??