looping through date range variable in c#

• azdeji

  SSCrazy

  Points: 2535

  More actions

  October 16, 2013 at 2:05 am

  #281351

  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.