CODEWITHSUNDEEP
c#sql-servervariablesssiswebclient
Rosscoasks
Rosscoasks

Reputation: 107

C# script task in SSIS to concatane two SSIS variables, pass the result to Webclient class, and then write the output (JSON) to a third SSIS variable

I am trying to take two SSIS variables, concatenate them, pass them to the Webclient class, and write the result to a third SSIS variable. This third variable is then written to the database.

When debugging the script, the concatenated string I create returns the proper output when I paste it into it into a web browser (it creates JSON), but it simply returns a generic error message when I run this as part of the SSIS package.

As you can probably tell, I am very new to writing C#, but I making a dedicated effort to learning it.

Any help from you guys would be greatly appreciated.

Here's what I have:

public void Main()
{
    var url = Dts.Variables["User::URL"].Value.ToString();
    var inputJSON = Dts.Variables["User::inputJSON"].Value.ToString();
    var fullurl = (url + inputJSON);


    using (var webClient = new System.Net.WebClient())
    {
        var json = webClient.DownloadString(fullurl);


        Dts.Variables["User::outputJSON"].Value = json;



        Dts.TaskResult = (int)ScriptResults.Success;
    }

}

#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
/// 
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

}

Upvotes: 0

Views: 1747

Answers (1)

EzLo
EzLo

Reputation: 14189

Seems that your web service validates the user agent from the request, try adding one like the following (simulating a Mozilla agent, for example). The user agent needs to be included in the header of the request.

using (var webClient = new System.Net.WebClient())
{
    webClient.Headers.Add ("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)");

    var json = webClient.DownloadString(fullurl);
    Dts.Variables["User::outputJSON"].Value = json;
    Dts.TaskResult = (int)ScriptResults.Success;
}

More info on user agents with WebClient here: https://msdn.microsoft.com/en-us/library/system.net.webclient.aspx

Upvotes: 1

Related Questions