October 16, 2013 at 2:05 am
C# code to update a webservice link that loops through multiple dates (datefrom to dateto )using variable in for script task example if the date range was between 2013-01-01 -2013-01-04 it loop through the dates and supply the xml source the updated values -
http://XXXX&dateFrom=2013-01-01&dateTo=2013-01-01&reportType=
http://XXXX&dateFrom=2013-01-02&dateTo=2013-01-02&reportType=
http://XXXX&dateFrom=2013-01-03&dateTo=2013-01-03&reportType=
http://XXXX&dateFrom=2013-01-04&dateTo=2013-01-04&reportType=
The original VB code is below –
Dts.Variables("User::XMLPathDaily").Value = "http://XXXX&dateFrom="
Dts.Variables("User::XMLPathDaily").Value = Dts.Variables("User::XMLPathDaily").Value + Dts.Variables("User::XMLRunDay").Value
Dts.Variables("User::XMLPathDaily").Value = Dts.Variables("User::XMLPathDaily").Value + "&dateTo=" + Dts.Variables("User::XMLRunDay").Value + "&customField=1 "
'Sleep for 5 Seconds else cannot re-try connection
Dim sec As Double = 5 'Convert.ToDouble(Dts.Variables("intDelaySecs").Value)
Dim ms As Int32 = Convert.ToInt32(sec * 1000)
System.Threading.Thread.Sleep(ms)
This is the c# code I want to update –
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;
}
}
#region ScriptResults declaration
///
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
///
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Any advice would be greatly appreciated.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply