Download JSON file from Azure Storage and Read it by SSIS – Part 2

Introduction

This article is the continuation of my previous article named “Download JSON file from Azure Storage and Read it by SSIS”.

You can find this article:
http://sqlknowledgebank.blogspot.in/2018/04/download-json-file-from-azure-storage.html

In this article we are going to read the JSON file and store it in Relational Database Table object. For that we are using Script Component. Hope it will be interesting.

JSON File

The sample of the example JSON file

Data Flow Task

Script Component Settings

Connection Manager with JSON File

Input Output Columns

Edit Script

References needed Syste.Web.Entry

Name Spaces needed:

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Web.Script.Serialization;

C# Code:

#region Help:  Introduction to the Script Component

/* The Script Component allows you to perform virtually any operation that can be accomplished in

 * a .Net application within the context of an Integration Services data flow.

 *

 * Expand the other regions which have "Help" prefixes for examples of specific ways to use

 * Integration Services features within this script component. */

#endregion

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Web.Script.Serialization;

#endregion

/// <summary>

/// This is the class to which to add your code.  Do not change the name, attributes, or parent

/// of this class.

/// </summary>

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

   

    IDTSConnectionManager100 connMgr;

    /// <summary>

    /// This method is called once, before rows begin to be processed in the data flow.

    ///

    /// You can remove this method if you don't need to do anything here.

    /// </summary>

    public override void PreExecute()

    {

        base.PreExecute();

        /*

         * Add your code here

         */

        connMgr = this.Connections.MyFlatFileConnectionMgr;

    }

    /// <summary>

    /// This method is called after all the rows have passed through this component.

    ///

    /// You can delete this method if you don't need to do anything here.

    /// </summary>

    public override void PostExecute()

    {

        base.PostExecute();

        /*

         * Add your code here

         */

    }

    public override void CreateNewOutputRows()

    {

        /*

          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".

          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".

        */

        JavaScriptSerializer js = new JavaScriptSerializer();

        byte[] jsonbyte = System.IO.File.ReadAllBytes(connMgr.ConnectionString);

        string reviewConverted = System.Text.Encoding.ASCII.GetString(jsonbyte);

        // deserialize the string

        jData jdata = js.Deserialize<jData>(reviewConverted);

        foreach (files f in jdata.rows)

        {

            Output0Buffer.AddRow();

           

            Output0Buffer.id = string.IsNullOrEmpty(f.id) ? "" : f.id.ToString();

            Output0Buffer.dis = string.IsNullOrEmpty(f.dis) ? "" : f.dis.ToString();

            Output0Buffer.siteRef = string.IsNullOrEmpty(f.siteRef) ? "" : f.siteRef.ToString();

            Output0Buffer.assetGmars = string.IsNullOrEmpty(f.assetGmars) ? "" : f.assetGmars.ToString();

            Output0Buffer.assetVfa = string.IsNullOrEmpty(f.assetVfa) ? "" : f.assetVfa.ToString();

            Output0Buffer.equipRef = string.IsNullOrEmpty(f.equipRef) ? "" : f.equipRef.ToString();

        }

    }

}

internal class jData

{

    public files[] rows { get; set; }

}

internal class files

{

    public string id { get; set; }

    public string dis { get; set; }

    public string siteRef { get; set; }

    public string assetGmars { get; set; }

    public string assetVfa { get; set; }

    public string equipRef { get; set; }

}

Hope you like it.

Posted By: MR. JOYDEEP DAS