SSRS 2016 - Treemap and Sunburst - New Shape Chart types

In SSRS 2016, two new Shape Chart types are included
i)  Treemap 
ii) Sunburst 

Tree Map and sunburst visualizations are great for visually representing hierarchal data.

We can add this new chart types like same way we add other charts.
Design -> Right-click on report body -> Insert -> Chart -> Shape -> Tree map / Sunburst.


Microsoft Azure - Move data from another computer to SQL Server instance on VM using SSIS

In this article, let us see on how to move data from another computer over internet to SQL Server instance on virtual machine using SSIS.

If you have no idea in creating and connecting to Azure SQL Server VM, Please refer below links :




In this post, let us also see on how to import JSON file on local computer to SQL Server on VM through this example.


SQL Server Import (Bulk load) JSON file into table

In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

In this article, let us see on how to import JSON data into SQL Server table.

Using OPENROWSET is one of the option to bulk load JSON data into SQL Server table.

SQL Server JSON - Examples - JSON_QUERY, JSON_VALUE

In this post, let us see some examples on handling JSON data in SQL Server using JSON_QUERY, JSON_VALUE

Consider below sample JSON data, let us check whether it is valid :

{"MSSQL":
      {  
    "SQL Server":{  
   "Version":"6.0",
   "CompatibilityLevel":60,
   "CodeName":"SQL95" },
   "YearofRelease":"1995"   
      }
   

}

SQL Server - Convert JSON data into relational data (rows & columns) format

In my previous post, I shared some examples on Converting relational data to JSON data in SQL Server.


In this post, let us see examples for converting JSON data into relational data format.

For Example: Consider below JSON data :

Microsoft Azure - Getting started with SSRS on VM

In this article, let us see steps to get started with SQL Server Reporting Services (Report Builder 2016) on Microsoft Azure SQL Server Virtual machine.

To create & connect to SQL Server VM, refer my previous post.


Microsoft Azure - Connect to SQL Server on VM over Internet

In this article, let us see steps to connect to SQL Server on VM over Internet.

Below are the three steps to connect to SQL Server on VM over Internet :

1) In Azure portal - Under VIRTUAL MACHINES - Create an ENDPOINT
2) In VM - Create SQL Server login and enable SQL Server Authentication mode
3) In VM - Windows Firewall with Advanced Security - Add a New Inbound rule to enable Port


SSIS - Loop through recordset and load into multiple file dynamically

In this article let us see an example on
*) how to loop through records in a table in SSIS
*) how to load into multiple (.sql) file dynamically

Consider the requirement is to generate index script for tables that belong to particular schemas and export as separate .sql files.

Long back I wrote an article on How to Generate Index Creation Scripts for all Tables in a Database using T-SQL

Going to create this script as Stored procedure:



SQL Server - Compare Execution Plan

In SQL Server 2016, one of the SSMS enhancement is option to compare query execution plan.

This is how we can compare execution plan in SQL Server Management Studio 2016:

SQL Server 2016 - Drop Objects IF EXISTS

In SQL Server 2016 many T-SQL enhancements were introduced. In this article, I am going to share one among them.

In SQL Server 2016 there is a new way to drop objects with simplified syntax.

In previous versions syntax will be as shown below: (this will also work in SQL Server 2016)

IF OBJECT_ID (N'dbo.Test_tbl', N'U') IS NOT NULL
DROP TABLE dbo.Test_tbl;
GO


Below are some examples:


Microsoft Azure - How to create SQL Server Virtual machine and connect to SQL Server

In this post, let us see step by step instructions on how to create SQL Server Virtual machine and connect to SQL Server.

Steps to create SQL Server VM:


Login into azure portal and goto VIRTUAL MACHINES




SQL Server - Split CSV into separate row and other splitting techniques using OPENJSON

In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

In this article, let us see some examples on splitting comma separated string into separate rows using OPENJSON


 --Split comma separated numeric
SELECT value FROM OPENJSON('[1,2,3]')
-- Split comma separated alphabets
SELECT value FROM OPENJSON('["A","B","C"]')
--Ignore null while splitting

SQL Server - Convert relational data to JSON data


In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

In this article, let us see examples on how to convert relational data (data in rows & columns format) to JSON format

SSIS - Export XML - XML destination

In this post, I will share with you on how I have exported xml returned from a Stored procedure as xml file in a folder using SSIS.

Below is my Stored procedure:

Import and analyze your facebook data with PowerBI

Last year when I was trying to learn PowerBI, I noticed that there is an option to import and analyze facebook data with PowerBI

I have shared in my facebook group  (SQL Server Network) on how to import facebook data using POWER QUERY.

POWER QUERY - > From Other Sources

SQL Server 2014 Certifications

In this article, I am going to share some information on SQL Server 2014 certifications.

Here is the SQL Server Certification path overview - http://www.microsoft.com/en-in/learning/certification-overview.aspx

One of the FAQ related to SQL Server 2014 Certifications:


Microsoft Azure - Creating a database copy to same or different server

We can take a copy of database in 3 ways:

1) using copy option in Azure portal
2) using T-SQL
3) using powershell command

Microsoft Azure - SQL Auditing & dynamic data masking

To look into my previous posts on Microsoft Azure.

Azure - SQL Auditing


We can enable SQL Auditing on Azure portal in Database level as well as in Server level.

Before enabling SQL Auditing, we need to create Storage for Audit logs.
Click on STORAGE and try like as shown in below images:

Microsoft Azure - Create SQL objects

To look into my previous post on connecting to Azure SQL database.


USE is not supported to switch between Azure SQL databases:



How to connect Azure SQL database through SSMS

To look into my previous posts on Microsoft Azure

In this post, let us see about connecting to Azure SQL database through SSMS.

To get server details, click on database name on your azure portal and refer as highlighted in below image:



Microsoft Azure - Create SQL Server database & Server

In my previous post, I wrote about Getting started with Microsoft Azure

In this post, I am going to share about how I created SQL database and Server in Azure portal.

Login into azure portal - >  SQL DATABASES ->  CREATE A SQL DATABASE as shown in below image:

Getting Started with Microsoft Azure

Microsoft Azure is a cloud computing platform and infrastructure, created by Microsoft, for building, deploying and managing applications and services through a global network of Microsoft-managed and Microsoft partner hosted datacenters.

Below are some of the popular solutions provided by Microsoft Azure: