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























Comments

  1. Visit to know about LG G7 release date
    Set alert for LG G7 expected price in India. Know about expected launch date, specs and news. You can also ask questions about the device.

    ReplyDelete
  2. SSIS (SQL Server Integration Services) excels at fetching data from RESTful APIs. How Play Game It streamlines data extraction, transformation, and loading processes, making it seamless to connect with web services.

    ReplyDelete

Post a Comment

Popular posts from this blog

Microsoft BI Implementation - Cube back up and restore using XMLA command

Databricks - incorrect header check