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
- The version number for GA in Visual Studio 2015 is 14.0.61021.0 (Direct link: https://msdn.microsoft.com/en-us/mt186501)
- The version number for GA in Visual Studio 2013 is 12.0.61021.0 (Direct link: https://msdn.microsoft.com/en-us/dn864412)
This release will be available through Visual Studio Extensions and Updates notification soon.
Download Data-Tier Application Framework 16.5
- The version number is 13.0.3485.1
- You can also install as a nuget package, e.g. for use in Powershell or your own app integration scenarios:
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!
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
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?
I created a connect item but I named it horribly and I can’t remember which account I created it on, so…
In VS 2012 or 2013, create a project that is in 1103 compatibility, add a perspective (we have 12 but only need 1), add a hierarchy to it, and expose it to the perspective.
Then open it up in VS 2015, (leave it in 1103 mode), open the perspective and remove the perspective, then add it back in. OR you can modify the hierarchy.
Connect to your workspace or deploy and you will see that your hierarchy is gone from all perspectives, and the field list is all jacked up with all fields in ‘more fields’.
Effectively, any change to a hierarchy or perspective pretty much causes code lose of the mapping of hierarchies to perspectives. The hierarchy still exists in the model but you can’t get it to appear with a perspective regardless of the tool.
The XML code fragment in each of the perspectives poofs if you are doing a file compare, there are other annoyances but this is the show stopper for using 2015 with anything other than 1200 mode models.
If you require you can reach out to me and I can do a desktop share to re-pro it, in addition the latest greatest 16.5 still has the bug as well.
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
It looks like this has been fixed now.
Thanks Kevin
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?
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
The MSBuild nuget update is now live.
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
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?
GO
CREATE VIEW v1
AS
SELECT JSON_VALUE(LocationName, ‘$.info.address.PostCode’) as PostCode
FROM T1
Regards,
Kevin
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 appreciated
Thanks
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
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-projects
Please 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.
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=0x409
I can hit the url manually from IE or chrome just fine.
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!
You can download the ISO version, it worked for me.
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
Thanks for this
Fixed Connect bug 1608896: Move Multiple Integration Service Package Tasks
and this
SSDT 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 :).
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.targets
I 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!
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.
+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).
We are getting constant crashes, will there be an update soon that will address stability?
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
Question on silent installs of SQL Server Data Tools 2015:
Is there a command line option to install the BI features (SSIS, SSRS, etc.) instead of having to check the boxes manually in the installation UI?
Hi,
Very happy the VS 2017 is slimming down somewhat but for me and my team the biggest remaining bug / performance issue is the one editing SSIS Scripts and round tripping between the the script task and debugging changes. It was bad enough in 2005 but it’s steadily got worse over the years and now with 2016 and VS 2015 it’s approaching the unusable state. It can take 30-40 seconds to load VS to edit a script. When you close the SSIS task it kills the VS instance. It’s truly a painful experience
It’s remained this way for so many years most people have just given up on it. Why can’t this experience be really improved? Or at the very least allow the code session to remain open while the SSIS component is written, debugged etc.
There are numerous post all over the Internet about this and various Connect Items mainly just ignored.
https://connect.microsoft.com/SQLServer/feedback/details/3107289
https://connect.microsoft.com/VisualStudio/feedback/details/789728
https://connect.microsoft.com/SQLServer/feedback/details/293750
I would be happy to demo this to a member of the team so you see how bad it is.
Thanks
Toby
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?
Post is now over three months old.
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?
Is there any way to look at the table column’s source fieldname after renaming it?
Hi
i 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 example
1- 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 too
this tool you guys responsible is so vital for alm process, please check this problems and more others i dont mention
thank you
I recently started using SSIS (via the SSDT 16.5 package) in an effort to ETL the “right” way as opposed to performing the transforms all in SQL. While I see advantages in this approach, and I will consider using it, you have a number of issues. Let me highlight these below:
– Poor Documentation. When you use the sort transform, you have a subset of the columns selected for sort and the remainder as pass through. Then you select the option to “remove rows with duplicate sort values,” the documentation does not state which winning row will be selected. This is important because the remainder of the metadata matters and I need to know which is a winner AND a way to control it.
– Lack of “Sugar.” All of the below steps could be handled when you compile the graph.
– The exterior of all nodes should have a built in Multicast. This saves 1 step, but most important, it would prevent type information being “trapped” inside the normal multicast nodes which is a pain in the neck when the source changes.
– You need a [Where Not Exists] Transform. Another common pattern to prevent insertion of rows that don’t already exists. If you want to perform this now, you need the following steps which are repetitive to define, error prone, slow, … You would be able to replace 5 operations with 1:
[Potential Insertions]->Sort
[Existing Items]->Sort
Merge Left Join (Left, Right)->Conditional Split->Union/Sort/… to remove the extra column
– You need a [Rank () over (Partition By X, Order By X); Choose Rank =1] Transform. This is a common pattern. If you want to perform this now, you need the following steps which are repetitive to define, error prone, slow… You would be able to replace 7 steps with 1:
Source->DerivedColumn(to Add a row#)->Sort(By Row#)->Multicast->
LeftBranch->Aggregate->Sort(By Row#)
MergeJoin(LeftBranch, Right Branch)
– You can’t have automatic type conversions? Like from varchar to nvarchar? You can apply the appropriate transforms under to the covers when compiling the graph.
– You need a [Wait For All Inbound Operations To Complete] node. If I’m normalizing an input source, I must insert into a parent table, then a child table. I may need 20 steps needed to get the data into a state required for insertion. Since I cannot simply piggy back and insert to the child table after populating the source table in the same data flow task, it means I must create a 2nd data flow task and redo most of my pre-processing.
– Lack of a 64 bit version that integrates with Visual Studio. Really? Disk swaps slow everything down to an utter crawl when you hit 3.5 GB. I have a 32 GB laptop and want to use that RAM. How hard is it to emit your causality token, a few messages, and come counter information and have it picked up by a 32 end point that is listening on a web port?
– I’m not even going to get into items like the lack of a Web File Source OR Unzipping which once again requires us to write code for what are normal every day operations.
– Sorting algorithms suck. From judging at the time it takes to sort a table with a zillion columns that is super wide, and given that you only use 32 bits, it’s clear that you’re attempting to sort the entire table in place. Your sort instead should generate an index which gives pointers back to the source locations that are … on disk apparently. Given the amount of sorting you require for the gobs of operations I’ve described earlier, this would be a huge speed win.
– Add a mode whereby if I have a number of script components in a single package, that I can share source files between them. No, I’m not going to compile a DLL and add it to the GAC to use as a way of sharing.
No, I’m not going to submit these ideas for up/down voting and wait a few years for implementation. I will trudge through and get my job done. But really, step up to the plate.
Post is now over four months old.
Is TargetServerVersion property available in VS2013?
Thank you
Please could you address the 271 up-voted connect item https://connect.microsoft.com/SQLServer/Feedback/Details/487106
Allow 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
It’s now March 2017. Aren’t you going to say anything about VS 2017 ??