script task from to XML source Using XML from data variable

Question

script task from to XML source Using XML from data variable

azdeji

SSCrazy

Points: 2535
More actions
October 15, 2013 at 2:15 am

#149988

I’m using c# code from a SQLcentral article- Using XML from data variable option in SSIS (http://www.sqlservercentral.com/articles/SQL+Server/97947/) which works perfectly with the original webservice address but when I change the webservice to a google webservice it does not download the xml file into client.DownloadString(url); but it does download into a physical location on my harddrive use the code - client.DownloadFile(url, "c:\\xmlfromurl.xml");
Any help would be greatly appreciated.
C# code in script task
#region Namespaces
using System;
using System.Net; //Added
using Microsoft.SqlServer.Dts.Runtime; //Added
using System.Windows.Forms;
#endregion
namespace ST_5fd2f54a0c7f4813995b1208caacd6f4
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// Declare variable that will hold the xml document received by the API
string xmlDoc2 = String.Empty;
try
{
// Get the URI from the variable
string url = Dts.Variables["User::URI2"].Value.ToString();
MessageBox.Show(url);
//Create a Web Client
using (WebClient client = new WebClient())
{
//Download the xml document as a string
xmlDoc2 = client.DownloadString(url);
client.DownloadFile(url, "c:\\xmlfromurl.xml");
}
// Set the value of the xmlDocument to the string that was just downloaded
Dts.Variables["User::xmlDoc2"].Value = xmlDoc2;
MessageBox.Show(xmlDoc2);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246991 More actions · Answer 1

I’m using c# code from a SQLcentral article- Using XML from data variable option in SSIS (http://www.sqlservercentral.com/articles/SQL+Server/97947/) which works perfectly with the original webservice address but when I change the webservice to a google webservice it does not download the xml file into client.DownloadString(url); but it does download into a physical location on my harddrive use the code - client.DownloadFile(url, "c:\\xmlfromurl.xml");
Any help would be greatly appreciated.

What is your question, exactly?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

azdeji SSCrazy Points: 2535 More actions · Answer 2

Thanks for replying back, the xml data from the webservice is not being passed into the xml source file. The package execute without producing any errors but does not bring back any data from the webservice (webservice does contain xml formatted text).

Any ideas?

azdeji SSCrazy Points: 2535 More actions · Answer 3

turning out there was a problem with our webservice.