In this post, we will see how to consume a webservice in SSIS using Web Service Task
Using Web Service Task
Web Service Task is used to execute Web Method. We can get the value returned from the web service, assign it to a variable and later use it in SSIS package. This can also be used to get a list of available values, store it into a file and use that file as a data source for the package.
This task is available as a control flow task; when dragged and dropped onto design area we need to specify following settings
General: We see that there are some straightforward settings like Name and Description for the task. With HttpConnection manager you can refer to URL where the web service is hosted. The connection manager displays the following settings:
We see that along with Server URL you can specify user credentials with the client certificate, time out and chunk size. With HttpConnection you can use anonymous or basic authentication. Windows authentication is not supported.
Example: http://<server name>/<Web service Project><Service name>.asmx?WSDL
We also have a WSDLFile option with which the local copy of WSDL file will be saved.
You have two ways of configuring settings. First one is to download WSDL file manually and save it in some location. Second one is to include the URL with ?WSDL as shown in the example and download WSDL with the command button.
You can select whether to overwrite the file or not.
Input: Once we have specified the options from General tab we can specify the web service method and give parameters if required
If the web method requires any parameters, the list will automatically be supplied to which you can send values as constants or with the help of variables declared in pakage.
Output: with this you option, can send the value returned from the web method to either a variable or save it in a file as shown below
With this we will see a demo of working with Web Service Task
I have used a web service with two web methods, one which takes input parameter as EmployeeID and returns data related to the employee by using tables HumanResources.Employee, HumanResources.EmployeeAddress and Person.Contact from AdventureWorks database and the second which is based on Person.Contact table in AdventureWorks database (which returns all the records from the table)
Following are the two web methods:
[WebMethod]
public string SendDetails(int EmpId)
{
try
{
System.Data.SqlClient.SqlConnection cn = new
System.Data.SqlClient.SqlConnection();
cn.ConnectionString = @"data source=<server name>;" +
"initial catalog=adventureworks;user id=<user name>;password=<password>";
cn.Open();
System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand("SELECT HumanResources.Employee.EmployeeID,"+
"Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.EmailAddress" +
" FROM HumanResources.Employee INNER JOIN HumanResources.EmployeeAddress " +
"ON HumanResources.Employee.EmployeeID = " +
"HumanResources.EmployeeAddress.EmployeeID INNER JOIN " +
"Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID" +
" WHERE HumanResources.Employee.EmployeeID = " + EmpId, cn);
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
return dr["FirstName"].ToString() + " " + dr["LastName"].ToString() +
" " + dr["EmailAddress"].ToString();
}
catch (Exception ex)
{
return ex.Message;
}
}
and
[WebMethod]
public string[] ContactInfo()
{
string[] contacts = null;
try
{
System.Data.SqlClient.SqlConnection cn = new
System.Data.SqlClient.SqlConnection();
//multiple active result set is set to true as there are two command objects
//associated witha single connection
//one which returns the result set and one which is used to get count of records
cn.ConnectionString = @"data source=<server name>;initial catalog=adventureworks;" +
"user id=<user name>;password=<password>;MultipleActiveResultSets=true";
cn.Open();
System.Data.SqlClient.SqlCommand cmdCount =
new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) From Person.Contact", cn);
Int16 count = 0;
System.Data.SqlClient.SqlDataReader drcnt = cmdCount.ExecuteReader();
drcnt.Read();
count = Int16.Parse(drcnt[0].ToString());
contacts = new string[count];
System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand("SELECT FirstName, LastName, EmailAddress " +
" From Person.Contact", cn);
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
Int16 i = 0;
while (dr.Read())
{
contacts[i] = dr[0].ToString() + " " + dr[1].ToString() + "," + dr[2].ToString();
i += 1;
}
return contacts;
}
catch (Exception ex)
{
contacts[0] = ex.Message;
return contacts;
}
}
Let us see how to consume these web methods in Web service task:
1. Create a new Integration Services Project and change the default name for the package to ConsumeWebService.dtsx
2. Drag a sequence container on the design area (each sequence container will have a web service task so that we can execute only one task at a time and need not execute whole package)
3. Create a package level variable named WebServiceValue of string type
4. Drag and drop Web Service Task onto the design area
5. Configure the HttpConnection Manager (from General tab), Input tab to specify Web method name as SendDetails specify the input parameter is value (not a variable) and enter value 1. For output tab select variable and specify the user defined variable created in step 3
Alternatively we can create one more variable of integer type and set it as the input parameter to the web method.
6. We can verify the value returned from web service is correct by adding a script task and displaying the value returned. We will have to specify the ReadOnlyVariable for the script task to the variable in which the value is obtained.
We can use VB.NET or C# language for script when working with SQL Server 2008.
MessageBox.Show(Dts.Variables["User::WebServiceValue"].Value.ToString());
7. Execute the sequence container.
8. Add another sequence container and drag and drop web service task in it.
9. Configure the HttpConnection, select another web method ContactInfo this time.
10. In ‘output’ select File Connection and direct the output returned to a newly created XML file.
11. Execute the sequence container; verify that the file gets created with FirstName and last name with a space in between followed by the email address with a comma for all the records in the table.
That’s it and you can know use the WebService!
No comments:
Post a Comment