How to import a JSON-file into a SQL Server table?

Many of you are, hopefully, starting up projects to upgrade your current SQL Server to the shiny SQL Server 2017. One of the first things to do is run the Database Migration Assistant (DMA) (https://www.microsoft.com/en-us/download/details.aspx?id=53595)

What does it do? Nicely said on the MS website:

Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server.

Basically, it gives you a list of issues to tackle before you can go ahead with your migration. Not all of these issues are blocking, most might be warnings and won’t prevent you to upgrade to SQL Server 2017. Yet if you’re going to start with an innocent new (SQL) server, why not clean-up that dirty stuff along the way huh.

This article isn’t about how you use the tool, there’s plenty of information on that to be found online, but how you can make the results more readable.

After DMA has finished the assessment you’ll get an overview of all the issues found per compatibility. You also have the option to export this report as, which is good to know, DMA doesn’t save the results for later itself. Once you close DMA the results are gone.

Exporting the report can be done in two formats, as a .JSON file or as a .CSV file.

I dare you to export into a CSV file. I tried this, which resulted in a 6MB file with 58.000 lines of ‘data’. #Unreadable.

The other option provided is to export it as a .JSON file.

When you open the file, in notepad, you get something like this:

  "Name": "DMATest",
  "Databases": [
      "ServerName": "JANVDP-SQL\\TEST",
      "Name": "DMA_Test",
      "CompatibilityLevel": "CompatLevel90",
      "SizeMB": 242420.0,
      "Status": "Completed",
      "ServerVersion": "10.50.6529.0",
      "AssessmentRecommendations": [
          "CompatibilityLevel": "CompatLevel140",
          "Category": "Compatibility",
          "Severity": "Error",
          "ChangeCategory": "BreakingChange",

Still, for the same assessment as mentioned earlier this generated a 2MB file with almost 19.000 lines of the above syntax. Not very readable either.

I decided to import the JSON file into a SQL Server table.

First I created a database, DMA, and the following table:




CREATE TABLE [dbo].[ReportData](
	[ImportDate] [datetime] NULL,
	[ServerName] [nvarchar](255) NULL,
	[DatabaseName] [nvarchar](255) NULL,
	[CurrentCompatibilityLevel] [nvarchar](30) NULL,
	[ServerVersion] [nvarchar](255) NULL,
	[TargetCompatibilityLevel] [nvarchar](30) NULL,
	[Severity] [nvarchar](15) NULL,
	[ChangeCategory] [nvarchar](50) NULL,
	[Title] [nvarchar](max) NULL,
	[Impact] [nvarchar](max) NULL,
	[Recommendation] [nvarchar](max) NULL,
	[MoreInfo] [nvarchar](max) NULL,
	[ImpactedObjectName] [nvarchar](255) NULL,
	[ImpactedObjectType] [nvarchar](50) NULL,
	[ImpactDetail] [nvarchar](max) NULL

To import your JSON file into the table, put the file in a location accessible for your SQL Server.

Use the following script to import the data; don’t forget to alter the location\name of your file:

DECLARE @OpenJSON varchar(max)

SELECT @OpenJSON=BulkColumn

INSERT INTO DMA.dbo.ReportData
SELECT GETDATE() as ImportDate, ServerName, DatabaseName, CurrentCompatibilityLevel, ServerVersion, TargetCompatibilityLevel, Severity, ChangeCategory, Title, Impact, Recommendation, MoreInfo, ImpactedObjectName, ImpactedObjectType, ImpactDetail
FROM OPENJSON(@OpenJSON, '$.Databases')
		ServerName nvarchar(255) '$.ServerName',
		DatabaseName nvarchar(255) '$.Name',
		CurrentCompatibilityLevel nvarchar(30) '$.CompatibilityLevel',
		SizeMB nvarchar(255) '$.SizeMB',
		Status nvarchar(255) '$.Status',
		ServerVersion nvarchar(255) '$.ServerVersion',
		AssessmentRecommendations nvarchar(max) '$.AssessmentRecommendations' AS JSON
CROSS APPLY OPENJSON (AssessmentRecommendations) 
		TargetCompatibilityLevel nvarchar(30) '$.CompatibilityLevel',
		Category nvarchar(255) '$.Category',
		Severity nvarchar(15) '$.Severity',
		ChangeCategory nvarchar(50) '$.ChangeCategory',
		RuleId nvarchar(255) '$.RuleId',
		Title nvarchar(max) '$.Title',
		Impact nvarchar(max) '$.Impact',
		Recommendation nvarchar(max) '$.Recommendation',
		MoreInfo nvarchar(max) '$.MoreInfo',
		ImpactedObjects nvarchar(max) '$.ImpactedObjects' AS JSON
		ImpactedObjectName nvarchar(255) '$.Name',
		ImpactedObjectType nvarchar(50) '$.ObjectType',
		ImpactDetail nvarchar(max) '$.ImpactDetail'

There you go, simple as that.

The only “problem” now is that DMA generates duplicates for a lot of issues, based on the target compatibility level. For example, if your current compatibility level of your database is 90 (SQL Server 2005) it will report 5 times the same issue, once for target compatibility level 100, 110, 120, 130 and 140.

To retrieve only the latest version you can use this statement:

;WITH cte AS 
  SELECT [ImportDate]
	, rn = ROW_NUMBER() OVER (PARTITION BY [ServerName], [DatabaseName], [ImpactedObjectName] 
      ORDER BY [ServerName], [DatabaseName], [ImpactedObjectName], [TargetCompatibilityLevel] DESC)
  FROM [DMA].[dbo].[ReportData]
FROM cte
WHERE rn = 1
ORDER BY [ServerName], [DatabaseName], [ImpactedObjectName], [TargetCompatibilityLevel]; 

You can use the same CTE to remove the different versions if that’s what you like. Or you can keep the duplicates in order to show your manager how much work there’s to do to get some more days for your project.

The results now are 547 records, coming from 58.000 in excel and 19.000 in the json file.

Happy new SQL Server upgrade!

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature
If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before....
Creating maps with R and Power BI
The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you are creative and have knowledge about the...
Reading notifications
Sending monitoring alerts through Telegram
What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the...
Send mails with Azure Elastic Database Jobs
The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want similar functionality in Azure SQL...
Sorting matrices in Power BI
Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour...
The world of data is evolving
The data landscape has changed dramatically over recent years, the world of data is evolving. In the past, we mainly heard that we needed to...