Add the Service reference under ServiceReferences folder, add the System.ServiceModel under Reference folder (this is to use the EndPointAddress class in the script)
In the Main method, use the following script (high level) to get 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
Next, using DataTable class, load the strValue by creating new rows
DataTable dt = new DataTable();
DataRow dr = dt.NewRow();
dr["ValueToInsertIntoDb"] = strValue;
dr.Rows.Add(dr);
After that assign the dt to an Object Variable.
Dts.Variables["User::Values"].Value = dt;
Next, use another Data flow task, inside that use a Script component and select the variable in ReadOnlyVariables. Inside the script component, you have to loop through the DataTable dataset. Here's 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
Next, connect the script component to a OLEDB Command or OLE DB Destination and insert the values into the database.
