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. IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. A IEEE Domain project Final Year Projects for CSE system development life cycle is essentially a phased project model that defines the organizational constraints of a large-scale systems project.

    IT Company Employess Productivity usually increases when a company implements corporate training courses on latest technologies.
    corporate training in chennai
    It Companies need of Corporate training programme arises due to improvement in technology, need for getting better performance or as part of professional development. corporate training companies in chennai Corporate Training refers to a system of professional development activities provided to educate employees.
    corporate training companies in india

    ReplyDelete

Post a Comment

Popular posts from this blog

Hadoop - Hive - Load data from csv/xls files

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