web servise 調用ssis

後來我有找到答案.. 就只要一行 code 即可
myPackage.Variables["WebExeUserName"].Value = UserInfo.UserID;

然後在SSIS 那邊新增一個 變數 WebExeUserName
就完成我要的結果了... ^_^

不過還是感謝 Phil 大的熱心... 謝謝...

PS 我的 SSIS 沒有做成 WebService... 而只是在本機... 透過 Web 給別人 call

Creating the service

[ Back To Top ]

A web service project Change was created with the folder structure as shown in Figure 3.

 

 

 

Figure 3

Listing 1 describes the method(s) provided by the service as coded in the code-behind of the Service.asmx file. The function, change, defines the method. It takes a string (testStrg) as an argument and returns the encoded string (StrChange). This is a pretty simple web service.

Listing 1: Service.vb file

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
<WebService (Namespace:="http://tempuri.org/")> _
<WebServiceBinding (ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated ()> _
Public Class Service
     Inherits System.Web.Services.WebService
    <WebMethod ()> _
Public Function <span class=Bold>change</span> (ByVal testStrg As String) As String
        Dim Strchange As String = Convert.ToBase64String (System.Text. _
        ASCIIEncoding.ASCII.GetBytes (testStrg))
        Return Strchange
    End Function
End Class

In Visual Studio 2005 this service can be tested in-situ just by displaying the Service.asmx file which is rendered as shown in Figure 4 (only part of it shown).

Figure 4

 

 

The hyperlink change can be used to test the service. Clicking on the hyperlink reveals the page shown in Figure 5 (again only relevant portion is shown).

Figure 5

This service may be tested by inserting a text ("This is a test string") such as shown in Figure 5 and clicking on the button marked, Invoke.  Figure 6 shows the result returned by the web service.

Figure 6

 

The Service Description hyperlink in Figure 4, when clicked, reveals what is called the WSDL file, which contains the complete description of this service in XML as shown in Figure 7. The various nodes can be expanded to drill into more details.

Figure 7

The URL that is displayed in the browser when the Service Description hyperlink is clicked provides the web reference to this service. For this particular service the URL reference is shown in the next paragraph.

http://localhost/Change/Service.asmx?WSDL 

This is just the URL of the project with Service.asmx? WSDL appended to it.

Creating a Web Service Task in the Visual Studio IDE

An Integration Services Project can be created by following the trail, FileàNew Project…. This would open up a New Project window where the Integration Services Project can be chosen from the Visual Studio installed templates. A project created this way can contain a number of packages created by the user/developer as shown in Figure 8. The description of creating a SSIS project is described in minute detail in an earlier article, Building a SQL Server 2005 Integration Services Package Using Visual Studio 2005.

Figure 8

In the above figure a Package2.dtsx was added for this tutorial. This provides an empty canvas with four tabs, Control Flow; Data Flow; Event Handlers; and Package Explorer.

Figure 9

Adding a Web Service Task

The Web Service Task is a new task item added to the data transformation software, SSIS, formerly called Data Transformation Services. This task can be found in the Toolbox.

Figure 10

 

This can be dragged from the Toolbox and dropped on the design pane of the Control Flow.

Figure 11

In the Control Flow design pane, the Web Service Task control is shown. On the right of this figure the Properties window of this control is shown. Additionally, two hyperlinks at the bottom, Show Editor and Edit Breakpoints, are also shown. The bulk of the package design consists of editing this control.

Configuring the Web Service Task Control

The editor for this control, Web Service Task Editor, can be invoked either by clicking the hyperlink mentioned previously (Show Editor) or by right clicking the control in the design area and choosing the Edit... option. This brings up the Web Service Task Editor window as shown in Figure 12.

Figure 12

 

The Editor wizard comes up with its initial page set to the tab, General. Here in the Connection information and some General Information consisting of Name and Description may be supplied. As discussed earlier, the HttpConnection and the WSDLFile are sufficient for the package design. In the above window a Name (Encoder Service) and Description (Method to return a base64 encoded string) have been inserted.

Clicking on an empty area (shown with a red dot) in the HTTPConnection box results in a drop-down control. This will show connection information, if it exists or a hyperlink, to make a new connection. Clicking on the <New Connection…> link in the drop-down, results in opening up the HTTP Connection Manager Editor window shown in Figure 13.

Figure 13

When the above window loads, it comes up with empty information to be filled in. Enter http://localhost in the Server URL: text box as shown and click on the Test Connection button. The result of a successful connection comes up as a Microsoft Visual Studio message alert which is also shown superposed in Figure 13. The text "HTTPConnection Manager" gets added to the Web Service Task Editor window's General page. Additionally, the property window of the Web Service Task changes as well as a control HTTP Connection manager is added to the try below the design pane as shown in Figure 14. In the present tutorial we are not using the proxy tab of this window as we have direct access to the Server.

 

 

 

 

 

 

 

Figure 14

 

In addition to the HttpConnection, the WSDLFile location (this should be a file with the wsdl extension on the machine) should also be specified (refer to Figure 12). It may be noted that the WSDLFile location will not accept a URL Reference. This is the location of the Service.wsdl file generated in a consuming client application. When a client consumes the service it also generates a Service.wsdl file that gets saved in a Web References folder of the web service consumer application project. The method to create a client (a windows, web or console client) is described in numerous tutorials at this link as well as on the internet.  The Project folder of the client which consumes the service used in this tutorial using the methods discussed from an article available at the above link.

Figure 15

The properties window of the localhost, which is the URL used in the Web Service Task Editor's Server URL (Figure 13), can now use the Service.wsdl associated with this client.

The general page of the task editor can be completed by browsing to the WSDL file location shown in Figure 15. For this windows client, this location was:

C:\Documents and Settings\Jay\My Documents\Visual Studio 2005\Projects\ChangeConsumer\ChangeConsumer\Web References\localhost\Service.wsdl

The Completed Web Service Task Editor for the General page is shown in Figure 16.

Figure 16

 

Now change the navigation to input which brings you to the Web Service Task Editor's next window shown in Figure 17.

 

 

 

 

 

 

 

 

 

 

 

 

Figure 17

Clicking on the empty space along the textbox indicated by the label, Service shows up as a drop-down from which Service was accepted. Clicking an empty area in the Method textbox converts it to a drop down with a single method change as shown. When this drop-down choice is accepted (the web service change had just one method and it could have been created with more than one method), the Web Service Task Editor window changes to the one shown in Figure 18.

 

 

 

 

 

 

 

 

 

Figure 18

In this window the Method is more fully described as to its input parameters. It just accepts a string whose value when it shows up is empty. For this web service task "Hello Web Service" has been entered.

Clicking on the Output page link in the Web Service Task Editor opens the next window shown in Figure 19.

 

 

 

 

 

 

 

 

 

 

Figure 19

Accepting the OutputType's default File Connection, a File has to be defined. This is the file which will contain the result of running this task. Clicking on an empty area in the textbox labeled File will bring up a link for creating a new connection, <New Connection…>. When clicked, this link will open the File Connection Manager Editor window shown in Figure 20.

 

Figure 20

Using this Editor it is possible to create a folder or a file. It is also possible to use an existing File or folder. Here, a new file, ChangeTask, will be created in a location to be defined next by using the Browse… button, [C:\Inetpub\wwwroot\Change\ChangeTask] shown in Figure 21. When the task is run, a file with the results of the task should be created at that location. This location can be arbitrary, but must be an easily recognizable file location.

Figure 21

This completes the output page which now appears as shown in Figure 22.

Figure 22

The third page, Expressions is not relevant for this tutorial and it is skipped.

Clicking on the OK button, the ChangeTask task will be created.

Figure 23

This package has neither Data Flow components nor any Event Handlers. The Package Explorer window with the essentials expanded is shown in Figure 24.

Figure 24

The ChangeTask may now be executed as shown in Figure 25 by right clicking the Encoder Service control in the Control Flow's design view pane. The task can be executed by other means as well, as described in an earlier tutorial on SSIS at this site by the author.

Figure 25

When the Execute Task is clicked, the screen may go blank for while and when the screen returns to normalcy, it may show the Encoder Service Control with a yellow background. When the task succeeds it turns to green as shown in Figure 26. In case it fails, it will turn red.

Figure 26

Now, if you browse to the root directory of the IIS you will find the ChangeTask file.

Figure 27

 

This file was not given an extension in the Web Service Task Editor, but can be opened with a text editor such as Word or Notepad which displays the following shown in Figure 28. This is the encoded string provided by the web service.

Figure 28

Summary

[ Back To Top ]

The Web Service task can be created quite easily provided the proper definition of the service, its methods, the input/output variables associated with the methods and the related WSDL file location are available. While the web URL reference of the WSDL is easily created in the VS Studio 2005, the WSDLFile creation in the Web Service Task Editor was not straight forward using the available tools on this wizard (Download WSDL in Figure 16). However, creating a web service consuming client would easily produce a WSDL file that can be persisted to the hard drive which is accepted by the Web Service Task Editor interface. The creation of the consuming client was not described in this tutorial, but numerous examples are available at the suggested resource. The client project folder is available on request by sending an e-mail to the author.