ssis 计划任务_SSIS Web服务任务

ssis 计划任务

SQL server Integration services provides us with SSIS Web Service Tasks which assist us to drive web services.

SQL Server集成服务向我们提供了SSIS Web服务任务，可帮助我们驱动Web服务。

This article will walk through how to configure the SSIS Web service task in Visual Studio 2017 (SSDT). In commence, it starts with the definition of Web service, and then dip drive of the configuration of web service task in SSIS including a salient example.

本文将逐步介绍如何在Visual Studio 2017（SSDT）中配置SSIS Web服务任务。 首先，它从Web服务的定义开始，然后是SSIS中Web服务任务配置的直接驱动，包括一个显着的示例。

In addition, I’ll discuss

另外，我会讨论

• How to create a new Http connection in web service task with or without proxy authentication
  如何在具有或不具有代理身份验证的Web服务任务中创建新的Http连接
• Then I will describe how to download WSDL (Web Service Definition Language) file in web service task
  然后，我将描述如何在Web服务任务中下载WSDL（Web服务定义语言）文件。
• Furthermore, I will give a demo of How to configure web service and drill down its the method which does not have a parameter
  此外，我将演示如何配置Web服务并向下钻取没有参数的方法的演示。
• Finally, I will also explain how to get the output of the Web Service in either form of File
  最后，我还将解释如何以两种文件形式获取Web服务的输出。

什么是Web服务？ (What is Web service?)

Web services are a kind of system where they can exchange XML-based information that can be used to throw over the Internet for interaction with one application to another application.

Web服务是一种系统，在该系统中，它们可以交换基于XML的信息，该信息可用于在Internet上与一个应用程序交互到另一个应用程序。

To be more specific, there are many operational applications in the worlds which work with create data, manipulate and sometimes generate a report. Each of these applications may be written with .NET, JAVA or any other languages. If you want to communicate with this application, you must write code in those languages. To solve this problem web service is introduced.

更具体地说，世界上有许多可操作的应用程序可用于创建数据，处理甚至生成报告。 这些应用程序中的每一个都可以用.NET，JAVA或任何其他语言编写。 如果要与此应用程序通信，则必须使用这些语言编写代码。 为了解决这个问题，引入了Web服务。

Webservice is well-known as a global way to communicate with the applications. You can send data to that application and receive data from that application. One of the rudimentary aspects of this is, web services are platform independent, and language independent for an instance java web service can be used in .net application or vice versa.

Webservice是众所周知的与应用程序进行通信的全局方式。 您可以将数据发送到该应用程序，并从该应用程序接收数据。 最基本的方面之一是，Web服务是平台无关的，并且实例Java Web服务的语言无关，可以在.net应用程序中使用，反之亦然。

The key for doing this is, web service is an XML base. As we already know that XML is the global way to keep data and standardize that data, web service function same, generate XML for returning data. On top of that, it can get variable and pass parameter as XML as well.

这样做的关键是，Web服务是基于XML的。 我们已经知道XML是保存数据和标准化数据的全球方法，Web服务功能相同，生成XML以返回数据。 最重要的是，它还可以获取变量并以XML形式传递参数。

SSIS中的Web服务 (Web service in SSIS )

In SQL Server 2005, the Web Service Task has been introduced in SQL Server Integration Services. With the help of this, we can able connect to a web service and it allows us to execute any one of the available web methods which can be exposed by the Web Service.

在SQL Server 2005中，SQL Server集成服务中引入了Web服务任务。 借助于此，我们可以连接到Web服务，它使我们能够执行Web服务可以公开的任何可用Web方法。

To be more specific, the web service task helps us to read data from the web service and also sent data to web service. Web service task work with the web service definition file (.WSDL). And expose its service, method, and list of the parameter of each method if they have.

更具体地说，Web服务任务可帮助我们从Web服务读取数据并将数据发送到Web服务。 Web服务任务与Web服务定义文件（.WSDL）一起使用。 并公开其服务，方法以及每个方法的参数列表（如果有）。

If I talk about parameter then, either parameter can be set from package variable or directly with static values. In that case, if you want to work dynamically then work with the variable. However, you can manage to work directly with a static value.

如果我谈论参数，那么可以从程序包变量设置参数，也可以直接使用静态值设置参数。 在这种情况下，如果要动态工作，请使用变量。 但是，您可以设法直接使用静态值。

Although, output result will be written in XML form only in file or variable.

虽然，输出结果将仅以XML形式写入文件或变量中。

Here I am going to give step by step guide to work with the web service task of SSIS

在这里，我将逐步指导如何使用SSIS的Web服务任务

Before starting it, let find web service, which is available free on the internet. Below is the URL where we can find the football world cup information online. https://footballpool.dataaccess.eu/info.wso?WSDL

在开始之前，请先找到网络服务，该服务可以从Internet上免费获得。 以下是可在网上找到足球世界杯信息的URL。 https://footballpool.dataaccess.eu/info.wso?WSDL

Now I’ll provide you a step by step approach to work with the web service task.

现在，我将为您提供逐步解决Web服务任务的方法。

1. The first step is to create a new package named “web_service_task”

   

   第一步是创建一个名为“ web_service_task”的新程序包。

2. Afterward, drag and drop web service task component from SSIS toolbox (common) to Control Flow

   

   然后，将Web服务任务组件从SSIS工具箱中拖放到“控制流”中

3. Third steps are double-clicking on web service task in General menu, set up HTTP connection where you need to click on <New Connection>

   

   第三步是在“常规”菜单中双击Web服务任务，在需要单击<新建连接>的位置设置HTTP连接。

4. When clicking on <New Connection> it automatically open HTTP Connection Manager Editor wherein server setting needs to specify a Server URL: https://footballpool.dataaccess.eu/info.wso?WSDL

   

   Furthermore, you may specify time-out and chunk size appropriately or leave it default

   Although, if u need to do proxy setting then specify proxy URL and authentication to connect to internet from your working environment LAN network, you can use credential or go to proxy and setting required things

   单击<新建连接>时，它将自动打开HTTP连接管理器编辑器，其中服务器设置需要指定服务器URL： https : //footballpool.dataaccess.eu/info.wso?WSDL

   此外，您可以适当地指定超时和块大小，或将其保留为默认值

   虽然，如果您需要进行代理设置，然后指定代理URL和身份验证以从您的工作环境LAN网络连接到Internet，您可以使用凭据或转到代理并设置所需的内容

5. click on Test Connection to verify your connection

   

   单击测试连接以验证您的连接

6. Now the next step required to choose WSDL file, for that first we need to create a blank file with WSDL extension. Here for demo purpose, I am creating one empty file a1.wsdl which is 0 kb means blank. I am storing it in WsdlFile folder for proper arrangement

   

   IE: \WsdlFile\a1.wsdl

   现在需要选择WSDL文件的下一步，为此首先我们需要创建一个具有WSDL扩展名的空白文件。 在这里出于演示目的，我正在创建一个空文件a1.wsdl，该文件为0 kb表示空白。 我将其存储在WsdlFile文件夹中以进行适当安排

   IE：\ WsdlFile \ a1.wsdl

7. Now I click on OverwriteWSDLFile = Ture, which simply means I am going to override a1.wsdl file, in case if WSDL file already exists

   现在，我单击OverwriteWSDLFile = Ture，这只是意味着如果WSDL文件已经存在，我将覆盖a1.wsdl文件。

8. What is next is hit Download WSDL button which means, download WSDL file from Http connection

   接下来是单击“下载WSDL”按钮，这意味着从Http连接下载WSDL文件。

9. Now check the location where you have created that a1.WSDL files, you may find the WSDL contain some XML code and you may check the size of the file it becomes 0 to 64 kb this clearly means that there are some codes in the file

   
   

   现在检查创建a1.WSDL文件的位置，您可能会发现WSDL包含一些XML代码，并且可能会检查文件的大小，使其变为0到64 kb，这显然意味着文件中有一些代码

   

10. In addition to the above, you can change the name and description of the web service task if you want

    除上述内容外，您还可以根据需要更改Web服务任务的名称和描述。

11. Now move on to the input task where on service, select “Info” which indicate the service name of particular WSDL. Afterward, the select method depends on your requirement. You may get method information when you select any method. Some method either requires input values or directly provides a result

    

    Here I am going to do a demo without using the input method, so we select “AllPlayersWithCards” method what is AllPlayersWithCards?

    • Note: You may find a detailed description of a particular method in “WebMethodDocumentation”.

    Hear this method “Returns an array with the names of all players that have a yellow or a red card. The number of yellow and red cards is returned.”

    现在转到输入任务，在服务中，选择“ Info”以指示特定WSDL的服务名称。 之后，选择方法取决于您的要求。 当您选择任何方法时，您可能会获得方法信息。 某些方法需要输入值或直接提供结果

    在这里，我将不使用输入方法进行演示，因此我们选择“ AllPlayersWithCards”方法是什么？

    • 注意： 您可以在“ WebMethodDocumentation”中找到特定方法的详细说明。

    听到此方法“返回一个数组，其中包含所有拥有黄牌或红牌的玩家的名字。 将返回黄卡和红卡的数量。”

12. Next step is going to Output tab where you have to specify output type: file connection/variable here I am choosing the file for demo purpose

    This file contains the result of that method we have mentioned above

    For that first click on <New Connection> then in file connection manager select usages type and file name. Here I am selecting “Create File” in usages type and browse the location of the file where WSDL file is store and gives name as output.xml. As we know that result of WSDL file stored in XML, create XML file out.xml for storing result. You may also first create “.xml” file and choose the existing file in file connection to manage “usages type”

    

    This is all that is needed to configure to set up web service task. Now we need to execute the package This package execution take few seconds as it takes data from the internet (it depend on the speed of internet and respond of the particular website.)

    

    下一步转到“输出”选项卡，您必须在其中指定输出类型：文件连接/变量在这里，我选择用于演示的文件

    该文件包含我们上面提到的方法的结果

    为此，首先单击<新建连接>，然后在文件连接管理器中选择用法类型和文件名。 在这里，我在用法类型中选择“创建文件”，并浏览存储WSDL文件并给出名称为output.xml的文件的位置。 众所周知，WSDL文件的结果存储在XML中，因此请创建XML文件out.xml来存储结果。 您也可以先创建“ .xml”文件，然后在文件连接中选择现有文件来管理“用法类型”

    

    这是配置设置Web服务任务所需的全部。 现在我们需要执行该程序包。该程序包执行过程需要几秒钟，因为它需要从Internet上获取数据（这取决于Internet的速度以及特定网站的响应。）

13. Now check the result file. We can see output.xml file contain names of all players that have a yellow or a red card in XML form. To be more specific, you can XML task in the data flow to get that XML data into the database or manipulate it

    

    现在检查结果文件。 我们可以看到output.xml文件包含XML格式的具有黄牌或红牌的所有播放器的名称。 更具体地说，您可以在数据流中执行XML任务以将XML数据放入数据库或对其进行操作

结论 (Conclusion)

A SSIS web service task is used to capture info from Web service over the internet and returning result into the XML file. Moreover, in my next articles, I will elaborate on the parameterized method of the Web Service task and manipulate return XML data into a database table.

SSIS Web服务任务用于通过Internet从Web服务捕获信息，并将结果返回到XML文件。 此外，在接下来的文章中，我将详细介绍Web Service任务的参数化方法，并将返回的XML数据处理到数据库表中。

翻译自: https://www.sqlshack.com/ssis-web-service-tasks/

ssis 计划任务