A Preamble To JSON

2019-04-26 0 Comments

Dave Mason - SQL Server - JSON

I've had a few more opportunities to use the Data Migration Assistant. Along with that experience, I have some additional thoughts on the assessment report. If you've used the tool yourself (or you read my recent post about the DMA), you may remember there is an "Export report" option available after an assessment finishes. (See images here and here to jog your memory, if you like.)

More recently, the CSV export format has been a problem. Much of the data has line breaks in it and I got tired of fighting with Excel trying to get a clean tabular output. I even tried reading the data into SQL via OPENROWSET, but quickly concluded I wasn't getting anywhere. I decided to bite the bullet and give the JSON export format a try.

I spotted some DMA documentation that mentions using Power BI to analyze a JSON formatted assessment report. Dustin Ryan created a Power BI template that makes it fairly easy. Just double-click on the *.PBIT file, enter the path where the *.JSON report file is, and watch the Power BI magic. By all accounts, it's pretty cool. The end result is attractive, meaningful, and interactive--everything you'd expect from Power BI. But is it useful? I haven't come to a final conclusion, but for now, I'm saying no.

What I really need is an actionable list of compatibility issues in a tabular (there's that word again) format that I can pass along to development. It was at this point that I decided to try my hand at parsing JSON text with SQL Server, OPENJSON, and OPENROWSET. I'd seen a smattering of comments on social media from those that found it challenging and sometimes frustrating. So I tempered my expectations. The outcome for me was very positive, though. I found a solution that solved a real world problem for me. And I've added a few scripts to my T-SQL tool belt. I'll elaborate and share some code in my next post.