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
Author
Message
Posted Wednesday, October 16, 2013 2:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:46 AM
Points: 67, Visits: 554
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.
Post #1505099
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse