Site icon TheWindowsUpdate.com

Lesson Learned #181: Reading a JSON compressed file from OPENROWSET function from Azure SQL Database

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Today, I got a question from a customer with the following scenario: 

 

Unfortunately, there is not possible to read compressed file using OPENROWSET, but, adding some customized code we have an alternative.

 

The first thing, I developed this small piece of code in C# that receives 6 parameters:

 

 

The bUnZip function in the C# source code will perform of the following operations:

 

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO.Compression;
using System.IO;
using System.Threading.Tasks;
using System.Configuration;

namespace DotNetExample
{
    class ClsZip
    {
        public bool bUnZip(string ZipPath, string UncompressFile, string File, bool overwrite, string sTableName, string sAzureBlobFile = "")
        {
            bool bExecuted = false;
            string sFinalFile = "";
            try
            {

                using (ZipArchive archive = ZipFile.OpenRead(ZipPath))
                {
                    foreach (ZipArchiveEntry entry in archive.Entries)
                    {
                        if (entry.FullName == File)
                        {
                            sFinalFile = System.IO.Path.GetFullPath(Path.Combine(UncompressFile, File));
                            if (overwrite)
                            {
                                DeleteFile(sFinalFile);
                            }
                            ZipFile.ExtractToDirectory(ZipPath, UncompressFile);
                            if (bImportData(sAzureBlobFile, sTableName))
                            {
                                bExecuted = true;
                            } 
                        }
                    }
                }
            }
            catch   {}
            return bExecuted;
        }

        private void DeleteFile(string File)
        {
            try
            {
             System.IO.File.Delete(File);
            }
            catch
            { }
        }

        public bool bImportData(string sFile, string sTable)
        {
            ClsRetryLogic oClsRetry = new ClsRetryLogic();
            bool bReturn = false;
            string sSQL = "";

                sSQL = "INSERT INTO " + sTable + " (Id, Value) " +
                          "SELECT Valuesfile.* FROM " +
                          "OPENROWSET(BULK N'" + sFile + "', SINGLE_CLOB) AS json " +
                          "CROSS APPLY OPENJSON(BulkColumn) " +
                          "WITH(Id varchar(200), Value nvarchar(100)) AS Valuesfile";

            try
            {
              if (oClsRetry.RunQueryWithRetriesNonQuery(GetConnectionStringTimeout(true),
                                                        sSQL, 5, 5)) { bReturn = true; }
            }
            catch (Exception e)
            {
                Console.WriteLine("Ups!! " + e.Message);
            }
            return bReturn;
        }

        private static string GetConnectionStringTimeout(bool bPooling)
        {
            return ConfigurationManager.ConnectionStrings["AzureSQLDB"].ToString());
        }

    }
}

 

 

Based on this URL you could see how to read JSON file using OpenRowset.

 

Enjoy!

Exit mobile version