This video we will talk about web service tasks in SSIS. Now, before we get into web service task First, let's understand a little bit about web service. Now, as an SSIS developer, you are not required to understand in detail how to create web surveys and what are the parts of webseries etc. You are only required to know three things about web service. One, what is the basic concept of web service? Second, what is Ws, GL?

And third, what is soap? That will be enough. Web Service is nothing but it's a platform independent way of exposing business data and business functionality to the outside world. In this definition, outside world means world which is not part of our existing application. For example, let's say we have application one, which is a dotnet application. This application want to expose some of its data and witness functionality to other applications, which are not part of this application.

Those applications may be created using dotnet technology, or may be created using Java technology or maybe created using some other technology. Application one is completely unaware about the technologies which is used for creating those other applications. In the same time, application, one is completely unaware about the locations where those other applications are installed. When these kind of scenario comes, web service comes to picture because web service is a standard in the industry. All technologies know about web service in all technologies some provision is given to access the web services Every technology know how to access web service. Every technology know how to invoke methods in the web service, every technology know how to access data in the web service and how to work with it.

So that's why, when application one exposes its business data and business functionality via a web service, it becomes universally accessible. Definitely, every technology has to follow some security policies. But at the end of the day, they are able to access the data and functionality exposed by application one in a platform independent way. Now, in dotnet, world, services can be created either as an SMS services, or as a Wcm. Service exposing basic HTTP binding, all Ws HTTP binding, you're not required to worry about or you're not required to understand the difference between those two for our lab, I have created One WC of service with a customer service, which exposes a get customer method, which will return simple customer data. Now to test this service, we'll simply press F fi, and it will launch a special client called as WTF test client.

WC of tests client is nothing but it's simply a tool given by Visual Studio for testing WCS services. Here you can see this get customer web method, simply double click it and click on invoke it will invoke the service right now we don't have any parameter for get customer method either if we'd had, we would have provided it here, but we don't have that. So I will simply click on invoke. And here as you can see, we get the response, address, age and customer name. It's very simple at the time of request, you're not sending anything and at the time of response, you're getting something but what you see here is not the real thing. Just a formatted thing by WC of test line.

The real thing is here in XML tab. As you can see, when you click that invoke method, when WTF client made a request to service, it actually created this XML and sent as an input. And when Wcm sent back the response responses this one both are XML. Now, request and response is sent and received as XML. Because I don't know who is going to be the client, client, maybe Java client, maybe dotnet. So we cannot send a data of particular data type.

Xml is a standard, everybody know about it. Whenever any client wants to invoke the service, they will create XML like this and they will send it as an input and they will always get responses. This now this is not a simple XML. If you closely see it contains some special tag like x cool an envelope You will see the same thing here is called an envelope, then you have something called as header, then you have something called as body in body you will see the method name right. In the same case here in the body you will see something called as get customer response. So, here tags are fixed, they always have same name, envelope header, body method name response method name.

So, it is a specialized version of XML. It is XML but something specialized. So, in simple words, anyone who wants to invoke service, they will create these kinds of soap XML and send it as input and they will get these kinds of XML as a response. As an SSIS developer, you are not required to worry much about this XML stuffs here. You're not required to worry about XML so make cetera okay. You just have to know from a knowledge point of view that whenever anyone invoke service, they have to send XML as an entity.

Sorry soap XML as input and they will get soap XML output that will be enough. Now the next question is how Wcm test client created this soap XML. In simple words, how it comes to know that get customer is the method name. On the other side, when response is received, how WTF comes to know that address is of type string, it is of type int, customer name is of type string. If you go to the formatter tab you will see that there is a section called as type we showed data type of all the values how Wcm test client understood the data type of all these return values. Now answer for this question is W s DL.

Usually, once a service is created, a document will be created using a special language called as Ws GL. ws dl is nothing but it's an abbreviation for web service Definition Language And it is simply a specialized version of XML. Hence, the document created using Ws GL is again going to be an XML document. Now the speciality about this document is it will contain the complete information about the service, like what all methods are exposed by the service, what each method expects parameter, what it returns, etc. This WTF test line created this XML soap XML based on a WC l document. And when response came back, it understood the response and created this formatted output based on that WTO document only.

Now you may you may ask me a question, Where is the WTO document. Now, when it comes to dotnet, we are not required to create that document manually. It gets automatically created as soon as we create a service and you can access that service by In a query string parameter, Ws GL here, that means this URL of the service question man Ws GL, and it will give you the WSDL document. And that Ws GL document will be required when it comes to SSIS. So let's do the lab now. first task in the control flow tab, go to SSIS Toolbox and simply drag web service task.

Now let's configure it, simply double click the task and go to HTTP connection property, click on this down arrow, and click on new condition and put the URL of Ws GL document. That is URL of service followed by W SDF. If you want you can provide the credentials as well. But right now all WTO services not productive. So just let's just keep it as it is. Click on next test connection.

Yes, Test Connection succeeded. Okay. Okay. Next, we have to specify a valid path here where we want to download Load rW GL document. So simply go to File Explorer, copy the path, and simply put it here slash my w SDL dot w s DL. Now, next is go and click this download wl button but unfortunately it is disabled, simply click anywhere and it will get enabled, click on this button, say okay, go to the folder and you can see that wl file is here, simply open this file with some editor.

And you will see that it's nothing but it's simply an XML file, close the file, go back to our integration service project. And next is input tab. Now guys, before we proceed any further, make sure that your WC f test client is running this if you close this dialog box, your WC of service will close and you will start getting error. So make sure that this WTF test client is running all the time. In the input tab, we will select the method and the service which we want to be executed. First we will select service that is customer service, and then we will select method that is get customer.

Next we'll get into output tab. And here we will decide what to do with result result of WC of service method. We have two choice one, store the result in file, or store result in variable. Let's store it in file for now. And then we have to select a file connection. I will say create new file, browse.

I will say result dot txt. I will say open I will say okay, I will say ok and done. Next, let's execute it. And execution done. Let's take a look at result dot txt file is generated. As you can see, it's simply a customer XML data.

The strange thing is when we executed ourselves With the help of WC of test client, the response was so flexible. Whereas now, it's simple XML. The reason for that is web service does web service tasks, do two things, one, invoke the service and get soap XML data. Second, extract the actual data from soap XML data and create simple XML. And that's why the rich result of web service task is simple XML data. So are we done?

No, we have one more thing to do. Now, we will read these individual values with the help of one more SSIS control flow task called XML tasks. So let's get with it. Let's go to integration service project. And just let's stop this project and go to SSIS Toolbox. Here you will find something called as XML task.

And with the help of precedence constraints, let's connect these two Next, just double click this XML task to configure it. First set operation type to export because we are interested in fetching individual values from XML, then set source type to file connection and set source to result dot txt, which is nothing but it's a file created because of web service task. Next, set save operation result to true because this time we are interested to save the result. And this time we will store the final result in a variable click on destination and click on new variable name variable as customer name say okay, all right destination set it to true second operand type and second operand. Now this is important. Here we will write some XPath query but unfortunately, normal XPath queries will not work here.

Because or result dot txt file contain some namespaces defined these namespaces are generated because of web service task and RWC of service and these because of these XML namespaces our normal exports queries will not work properly. So because of that, we have to write something special here, we have to write something like this slash star slash star square bracket. And inside it we have to write name equal to, we have to say, customer name. say okay, yeah, before that, we have to set this export operation to values because we are interested in values as the values. Okay, done. Now in order to test the output, what I'll do is I'll simply put a breakpoint here.

I would say right click, edit breakpoints, break when container receives on post executive and this Went will execute after my XML task completes the execution say OK, then I will say API web service task completed execution. And here what I will do is I'll simply go and put customer name which is nothing but our variable name Enter. And as you can see sukesh Marla is here. Now similarly, we will create two more XML tags, which will get the value of address and AES simultaneously. Stop the execution. Then let's remove that breakpoint.

See, okay, simply copy this task two more times. Double click it, change this customer name to address and in destination, simply create one more variable and store the data or the result in address variable. Okay, do again here simply Make it age, prediction result, destination, new variable, make it each say okay, okay done. connect these using precedence constraint. Now double click sorry, right click Edit breakpoint, put a breakpoint here, say okay, and execute the application again. It's done, done done, everything is done, simply go to watch window, and let me write all three of them customer name.

It's here you can see it here. Next one is address, enter, and final one is age. As you can see, all three variables are updated with the values in the XML or in simple words, these three variables represents the value which is coming from web service. You can use this variable and store values in Either database or txt file or even if you want, you can send me It's up to you. So this completes our web service task demonstration. Hope you enjoyed it.

If you have any question, please feel free to write to questpond questpond.com. Thank you.