Geek Answers Handbook

Calling a secure web service in SSIS using a script task

I am working on an SSIS package where we need to call or use a web service in SSIS using a Script task. I went through so many links, but I can not find the exact solution. I get so many links, although I cannot hack it.

My requirement: I need to call the web service URL using a Script task that has a client certificate. After calling this URL, we get the WSDL file from the web service. We need to use this WSDL file, and we need to define the methods inside this WSDL and we need to write the data available in this WSDL to the database tables. I have no idea how we can call this web service URL (with certificate) through Script tas, how we can read the WSDL file, and how we can load data into a DB table.

+2
source share
2 answers

Add the Service link to the ServiceReferences folder, add System.ServiceModel to the help folder (this will use the EndPointAddress class in the script)

In the main method, use the following script (high level) to start with ...

 var endPointAddress = new EndpointAddress('http://Server/ServiceName.svc');
 //Put your end point address 
 var basicBinding = new BasicHttpBinding();
 basicBinding.Name = "BasicHttpBinding_IService";
 //this is the port name, you can find it in the WSDL
 ClassServiceClient pay = new ClassServiceClient (basicBinding, endPointAddress);
 //this is the class in which the method exists you want to make a service call 
 IService = pay.YourMethodName();
 XMLDocument xmlOut = new XmlDocument();
 //This is to store return value from your method 
 xmlOut.LoadXml(IService);
 //Load the xmlOut with the return value
 XmlNode xmlNode = xmlOut.SelectSingleNode("ParentElement/ChildElement");
 //Search for your element name where you want to get the value
 string strValue = xmlNode.InnerText;
 //this gives the element value

Then, using the DataTable class, load strValue, creating new lines

DataTable dt = new DataTable();
DataRow dr = dt.NewRow();
dr["ValueToInsertIntoDb"] = strValue;
dr.Rows.Add(dr);

After that, assign dt to the Object variable.

Dts.Variables["User::Values"].Value = dt;

Then use another data flow task inside which the script component is used and select the variable in ReadOnlyVariables. Inside the script component, you need to go through the DataTable dataset. Here is the code that should look like

 DataTable dt = (DataTable)Variables.Values
   foreach (DataRow dr in dt.Rows)
   {
     ScriptComponentOutputBuffer.AddRow()
     ScriptComponentOutputBuffer.Column1 = dr["ValueToInsertIntoDb"].ToString();
   }
   //ScriptComponentOutputBuffer.Column1 --You need to manually add this column on output columns of your scriptcomponent

script OLEDB OLE DB Destination .

+2

More articles:


All Articles