Now that SQL Server 2016 has been released, it is time to start creating new SSIS and SSRS projects for it. Since SQL Server 2014, SSIS has migrated to Visual Studio. The latest version, Visual Studio 2015, has a free Community edition, and can be found here. If you have it installed and try to create a new Reporting or Integration Services Project, you will notice that there are no templates listed which will allow you to create one of these projects.
Making SSIS and SSRS Projects for SQL Server 2016
To create SSIS projects in Visual Studio, you will need to click on this link to download the SQL Server Data Tools [SSDT] in the language of your choice. Visual Studio must not be running during the install. After about 5 minutes, when the install completes you will have a new application installed, SQL Server Data Tools 2015. You will still have the Visual Studio 2015 application as well, providing two methods for creating new packages. which means that you can click on this icon instead of opening up Visual studio. SSDT also contains the templates for database projects, so you can now start using Visual Studio.
Creating SSIS and SSRS Packages for Different Versions of SQL Server
In this version of SQL Server Data Tools, Microsoft has finally addressed the common problem of needing to maintain multiple versions of SSIS packages for the different server versions. No longer do you need three different applications to maintain code for SQL Server 2012, 2014 and now 2016. All of these versions are supported with SSDT for Visual Studio 2015. SQL Server will detect which version the code was last saved in so that you don’t have to worry about accidently migrating code. You also have the ability to create an SSIS package in 2012, 2014 or 2016. To select the version you want, right click on the project and select Properties. Under Configuration Properties->General as shown in the picture, the TargetServerVersion, which defaults to SQL Server 2016, has a dropdown box making it possible to create a new package in Visual Studio 2015 for whatever version you need to support. Supporting the ability to write for different versions, is a great new feature and one which I am really happy is included in SSDT for Visual Studio 2015.
Data aficionado et SQL Raconteur
Where is Power View in Excel 2016?
Last week Microsoft announced their reporting roadmap at PASS Summit. They have been talking about pieces about it for a while, starting with the release of Excel 2016 on September 22, 2015. In the latest version of Excel, you have to add Power
Excel 2016 Group
View and Pivot Tables to the toolbar to be able to use them as these features are considered to be an integral part of reporting outside of Excel. Right click on the blank part of the toolbar, and select Customize the Ribbon. On the bottom right side of the screen there is a button called New Group. You will need to add a group, and then add icons to the Group. I called my group OldExcel, as these items are all considered depreciated features by Microsoft. Once the group has been created, change the dropdown box on the left to All Commands and then you can add Pivot Tables, Power View, Pivot Charts or any other features on the list on the left. The reason you have to add these items is they are no longer part of the reporting roadmap. According to the roadmap, if you want to do interactive reporting you can use Power BI or Datazen, which if you read my earlier blog will be part of SSRS. One thing to note in the reporting roadmap, Excel is not listed, which must mean is not meant to be part of the reporting of the future. This may also why the Power Pivot Gallery is not going to be part of SharePoint 2016.
SQL Server Reporting Services Compatibility with Power BI
Part of the upgrades to SQL Server Reporting Services [SSRS] allow for interoperability with Power BI. Starting with SQL Server 2016 CTP 3, reports created in SSRS can be integrated with reports on Power BI for a single location for reports. Reports created in SSRS can be pinned to Power BI to any existing dashboard via an icon within report manager. For Power BI to integrate directly with SSRS, the SSRS Reporting Services Configuration Manager needs to registers a Power BI account.
Power BI and SSRS Integration Button
Once the account exists, you can pin your SSRS report to a Power BI Dashboard the same way that previously you printed it. If you look at SSRS, it looks a lot like Power BI, since it now uses the same black color scheme, and includes visualization of the various new chart types in Power BI like that are now included like Tree Maps and Sunburst charts. For those of us who have written a lot of reports in the past, one of the really nice features is the ability to configure the parameters any way you want them. With the updates to SSRS, it appears that Microsoft’s slide showing support for on premise applications is more than just a slide, but a commitment which is reflected in the many updates to their on premise reporting solution SSRS.
Data aficionado et SQL Raconteur
There were a number of presentations where the next versions of software were discussed at PASS Summit. Among the more interesting was the conversions about the Future of DataZen. For those of you who are unfamiliar with the product, Microsoft bought Datazen on April 14, 2015, as their on-premise Mobile Solution. For more information on Datazen, please check out my previous posts on it here. In July of this year when I talked to someone from Microsoft about how Datazen fit into the product offering, he told me that it was going to be the non-cloud application to provide visualizations to mobile phones. Now with the latest series of product announcements, I fully understand what he meant. Datazen is going to be merged into SQL Server Reporting Services [SSRS]. The Datazen server is going to be combined into SSRS. SSRS is the new Datazen.
SSRS Updates Include Adding Datazen
For anyone who has looked at the product releases notes of the last three versions of SQL Server, you won’t find much about SSRS. The picture perfect reporting system was left alone. I was not surprised to hear from one of the Microsoft Employee’s at their booth at PASS Summit that they were told not even to mention SSRS as a reporting solution a few years ago. Everything was all about Power BI. Power BI is a neat visualization tool, but it doesn’t fit the needs of all users, primarily because it is a cloud based application, and let’s face it. Some people are just not going to go to the cloud.
On Premise Solutions Are No Longer Forgotten
Anyone who has perused by Microsoft’s product offerings in the past few years saw lots of things about the cloud.
Microsoft shows support for cloud and on premise software
Applications like Power BI and Azure ML are only available there. It seemed like Microsoft was abandoning people who wanted to maintain their own servers. They are looking to change this impression. I have included the graphic that Microsoft included in a number of their presentations. They are trying to show love for the box, which is the representation of on premises software.
Datazen SSRS Integration
Datazen is going to maintain the client features that people like, including designing using the grid view and picking the form factor. The server which manages Datazen is no longer going to be a separate server but will be included in the SSRS Server in SQL Server 2016. The idea is that Datazen will inherit some of the features people like about SSRS, such as the subscription model. The details regarding exactly how all of this will work were not made exactly clear, but I imagine that in the very near future more details of the Datazen/SSRS integration will be available soon. One of the things that I wondered is if the name Datazen was going to go away entirely and the application was going to be called SSRS mobile? I heard differing answers to this question, so I am not sure it has been determined yet. What I was assured of is the features that made Datazen a very good mobile solution are not going away. Microsoft hopes that having the ability to publish from SSRS to the phone may be a big reasons people decide to upgrade to SQL Server 2016 next year when this functionality is released.
Data aficionado et SQL Raconteur