Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

looping through date range variable in c# Expand / Collapse
Posted Wednesday, October 16, 2013 2:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 5:29 AM
Points: 75, Visits: 657
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 -

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)

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;

namespace ST_5fd2f54a0c7f4813995b1208caacd6f4

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;

// Get the URI from the variable
string url = Dts.Variables["User::URI2"].Value.ToString();


//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;
Dts.TaskResult = (int)ScriptResults.Success;
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


Any advice would be greatly appreciated.
Post #1505099
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse