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.
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;
base.PreExecute();
public override void PostExecute()
{
//Call getWebServiceResult to return our WorkGroupMetric array
GetWebServiceResult(wUrl);
FailComponent(e.ToString());
{
jsonString = reader.ReadToEnd().Replace("\\", "");
reader.Close();
FailComponent(httpWResp.StatusCode.ToString());
{
FailComponent(e.ToString());
compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);
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
Visit to know about LG G7 release date
ReplyDeleteSet alert for LG G7 expected price in India. Know about expected launch date, specs and news. You can also ask questions about the device.
Thanks for sharing informative information.
ReplyDeleteSQL Server Load Soap Api
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