SSIS - Pull the data from Web service (REST API)

I got opportunity to pull the data from web service and load into SQL database. SSIS 2014 have one web service task but that was not suitable for REST API.


In order to achieve this, we need to use script component in the data flow task.





double click on the script component and can provide required variables.











now go to the editor and add reference to web extensions.
Project --> add reference --> browse and choose system.web.extensions.dll.













now balance scripting is very easy even if you are not aware of C# coding.




include all the required name spaces as displayed below.

#region Namespaces

using System;

using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Web;
using System.Xml;
using System.Collections.Generic;
using System.Data.SqlClient;

#endregion

public class ScriptMain : UserComponent

{

    #region

    public string Url()

    {

        String url;

        url = this.Variables.VMODCNSTRNG.ToString();

        return url;

    }

    #region

    public string DBConn()

    {

        String DBConn;

        DBConn = this.Variables.VTGTCONNSTRING.ToString();

        return DBConn;

    }

    #endregion

      

    #region

    public string Taskid()

    {

        String Taskid;

        Taskid = this.Variables.VTASKID.ToString();

        return Taskid;

    }

    #endregion

     

    public override void PreExecute()

    {
        base.PreExecute();

     }

        public override void PostExecute()

    {

        base.PostExecute();

     }

    public override void CreateNewOutputRows()

    {

                

         string wUrl = Url();

         try
         {
           //Call getWebServiceResult to return our WorkGroupMetric array
            GetWebServiceResult(wUrl);

        }

        catch (Exception e)

        {
            FailComponent(e.ToString());

        }

    }

     private void GetWebServiceResult(string wUrl)

     {

        HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);

        HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();

            try

            {

                  //Test the connection

                    if (httpWResp.StatusCode == HttpStatusCode.OK)

                    {

                       Stream responseStream = httpWResp.GetResponseStream();

                       string jsonString = null;

                         //Set jsonString using a stream reader

                        using (StreamReader reader = new StreamReader(responseStream))
                        {
                            jsonString = reader.ReadToEnd().Replace("\\", "");
                            reader.Close();

                        }

                        XmlDataDocument xmldoc = new XmlDataDocument();

                        xmldoc.LoadXml(jsonString);

                       LoadRegionDistrict_DRIVEIN(xmldoc);

                    }

                    //Output connection error message

                    else

                    {
                        FailComponent(httpWResp.StatusCode.ToString());

                    }

                }

                //Output JSON parsing error

                catch (Exception e)
                {
                    FailComponent(e.ToString());

                }

        }

        private void FailComponent(string errorMsg)

        {

            bool fail = false;

            IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
            compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);

        }

private Boolean LoadRegionDistrict_DRIVEIN(XmlDataDocument xmlRRDD)

    {

        int index = 0;

        //Create a datatable that matches the temp table exactly. (WARNING: order of columns must match the order in the table)

        DataTable table = new DataTable();

        table.Columns.Add(new DataColumn("ID", typeof(string)));

        table.Columns.Add(new DataColumn("CnyCd", typeof(string)));

        table.Columns.Add(new DataColumn("OgzNr", typeof(string)));

        table.Columns.Add(new DataColumn("OgzAbrNa", typeof(string)));

        table.Columns.Add(new DataColumn("OgzNa", typeof(string)));

      

        //Parse XML

        XmlNodeList xmlnode;

        //xmlnode = xmlRRDD.ChildNodes;

        xmlnode = xmlRRDD.GetElementsByTagName("Ogz");

        try

        {

           

            //Add RRDD in our list to our DataTable

            foreach (XmlNode rrddNode in xmlnode)

            {

                index += 1;

                DataRow row = table.NewRow();

                row["ID"] = "";

                row["CnyCd"] = CheckNullToString(rrddNode.Attributes["CnyCd"].Value);

                row["OgzNr"] = CheckNullToString(rrddNode.Attributes["OgzNr"].Value);

                row["OgzAbrNa"] = CheckNullToString(rrddNode.Attributes["OgzAbrNa"].Value);

                row["OgzNa"] = CheckNullToString(rrddNode.Attributes["OgzNa"].Value);

              

                //Console.Write("");

                table.Rows.Add(row);

            }

            //row_suc = index;

        }

        catch (Exception e)

        {

            MessageBox.Show(e.Message);

        }

        string conString = DBConn();

        //using (SqlConnection con = new SqlConnection(conString))

        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString))

        {

            con.Open();

            //BulkCopy the data in the DataTable to the temp table

            using (SqlBulkCopy bulk = new SqlBulkCopy(con))

            {

                bulk.DestinationTableName = "ORG";

                bulk.WriteToServer(table);

            }

            con.Close();

            con.Dispose();

        }

        return true;

    }

         private string CheckNullToString(string Values)

        {

                    if (string.IsNullOrEmpty(Values))

                    {

                        return "";

                    }

                    else

                    {

                        return Values.ToString();

                    }

        }

}

#endregion