Reckoned Force

Full Service Business Data Solutions

Consume web service using proxy class

By

If you need to easily communicate with SOAP messages and map parameters to XML elements and then send SOAP messages over a network, Proxy classes will be a way to go.
Step 1. Add a proxy class to your project. I am not going to discuss how you could create your proxy class but if you are interested you may read this article on Microsoft site. http://technet.microsoft.com/en-us/library/ms155134.aspx
One thing I am going to add here is, in the article specified above on step one of using wsdl.exe to generate proxy class it simply say “From a command prompt, use Wsdl.exe” instead of using windows cmd.exe if you use visual studio’s command prompt you will make your life much easier. For example you sample statement could look like this
Wsdl /language:CS /n:ProxyClass /out:”C:\destinationfolder\ProxyClass.cs” https://url.com/address.svc?wsdl
Step 2: After you generate you proxy class create SSIS package and drag and drop a Script Task to your control flow
consume web service using proxy class
Step 3: The next thing will be to include your class to your project to do that. To do that right click on your project select add-> existing item from the dialog box find your proxy class and click on add then your package should look similar to this one.
consume web service using proxy class
Step 4: Write your script. I personally prefer C# but you could use VB.net as well.
Double click on script task and select your language preference if you are passing variable or writing on variables you should set up here properly
consume web service using proxy class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

#region Namespaces
using System;
using System.Data;
using System.IO;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
//using ProxyClass.ServiceName;
#endregion
namespace ST_c9bca45c2dc54166c24666
{
///
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
///
 
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
 
///
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
///
 
public void Main()
{
// List of variables
String userID = "testuser";
String password = "testpassword";
ProxyClass.ServiceName stuService = null;
String reportName = null;
String xmlString = null;
String fileName = null;
ProxyClass.ReportParameters repParams = null;
try
{
//intialize your report
repParams = new ProxyClass.ReportParameters();
stuService = new ProxyClass.ServiceName();
reportName = Dts.Variables["User::reportName"].Value.ToString(); //This is a variable that we passed earlier
repParams.AwardYear = 2013;
repParams.StartDate = DateTime.Parse("08/06/2013");
 
//Store the result set to xml file later xml source could be used//
//in data flow to retrieve the data. You could also store the result set on variable.//
xmlString = stuService.GetReportXml(userID, password, reportName, repParams);
fileName = string.Format(@"C:\address\source.xml", reportName);
System.IO.File.WriteAllText(fileName, xmlString);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
Dts.TaskResult = (int)ScriptResults.Success;
}
 
#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
 
}
}


Step 5: Build your project and look for possible errors.
If you get error saying “The type or namespace name ‘Services’ does not exist in the name space ‘System.Web’ (are you missing an assembly reference)

consume web service using proxy class

You may need to include few more libraries like this one
consume web service using proxy class