Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

SSIS Web Service Task

by suresh

The SSIS Web Service Task is used to execute the web service methods. For instance, if you want to store the weather report as an SQL extra column, then consume the Web service provided by the Yahoo or MSN weather, and store the output in a variable so that you can pass the variable to Data Flow Task.

In this article, we show you how to consume the web service methods using the SSIS Web Service Task with example. Before we start configuring the Web Serve Task in SSIS, let us see the list of the available method in our service.

NOTE: The SSIS Web Service Task uses the HTTP Connection Manager to connect with the Web service and to send or receive Files.

The below screenshot shows you the Methods available in this SSIS web service Task. Our task is to consume the GetCityForecastByZip method available in this web service. Please visit https://wsf.cdyne.com/WeatherWS/Weather.asmx website to use the same service.

SSIS Web Service Task 1

SSIS Web Service Task Example

Drag and drop the Web Service Task into the Control Flow region and rename it as the SSIS Web Service Task

SSIS Web Service Task 2

Double click on it will open the Web Service Task Editor to configure it.

  • Name: Please provide the Unique Name for this web service task
  • Description: Briefly describe the SSIS Web Service Task Functionality. It is always a good practice to provide a valid description.
SSIS Web Service Task 3

HttpConnection: An HTTP Connection enables this SSIS package to access web services. It used the HTTP to send and receive the files. By clicking on the drop-down arrow, will show you the already created HTTP Connections (If any), or please click on the <New Connection..> option to create one. Let us see what happen when click on the <New Connection..> option

SSIS Web Service Task 4

Once you click on the <New Connection..> option, HTTP Connection Manager Editor will be opened to configure it.

  • Server URL: Please specify the URL of the web service. If you are planning to download the WSDL file using the Download WSDL button, type the URL of the WSDL file; otherwise, choose the service URL.
  • Use Credentials: If you want the HTTP Connection Manager to use the security credentials of a user, Please check mark this option.
  • User name: Please specify the user name to access the Service.
  • Password: Please specify the password to access the Service.
  • Domain: Please specify the domain here.
  • Use client Certificate: Please specify whether you want to use the client certificate as the authentication or not.
  • Time-out (in seconds): Please specify the web service connection time out in seconds. If the connection takes more than this time, then the connection will fail.
  • Chunk size(in KB): Please specify the chunk size for the writing data.
  • Test Connection: This button will help us to check whether we successfully established the connection to web service or not. Based on the result, we can alter the connection settings.
SSIS Web Service Task 5

In this example, we are selecting the Web service from a free source. So, we don’t have to configure the credentials and proxy settings. From the below screenshot, you can observe that we are providing the Service URL: https://wsf.cdyne.com/WeatherWS/Weather.asmx?WSDL because we want to download the WSDL file.

SSIS Web Service Task 6

Click OK to close the HTTP Connection Manager.

WSDL File: If you already downloaded the WSDL file, then we have to select the .wsdl file from your local file system. Otherwise, create an empty file with WSDL extension in your local hard drive and then click on the …. button beside the WSDLFile option.

Once you click on the …. option, a new window will open. Using this, we can create a New WSDL file in your local file system or select the Existing File from the file system. From the below screenshot you can observe that we are choosing the existing file called web.wsdl

SSIS Web Service Task 7

OverwriteWSDLFile: This SSIS Web Service Task property has two options: True and False. If we set this property to true, the Web Service Task will overwrite the existing WSDL files in the local file system.

SSIS Web Service Task 8

Next, click on the Download WSDL button to download the WSDL file, and if it exists, it will overwrite the existing file.

SSIS Web Service Task 9

Next, click on the input tab to Specify the service Service and methods.

  • Service: Please select the required service from the list of available web services.
  • Method: Here, it displays the list of available Methods in above-selected web services. So, please select the required method
SSIS Web Service Task 10

SSIS Web Service Task Service: By clicking on the drop-down arrow will show you the available services. So, please select the required one. Here we have only one service, and we are choosing the same.

SSIS Web Service Task 11

Method: By clicking on the drop-down arrow will show you the list of available methods. From the below screenshot, you can observe that we are selecting the GetCityForcastByZip method. This method accepts the Zip code as a parameter and displays the weather forecast.

SSIS Web Service Task 12

Since this method accepts the Zip code as a parameter, we are providing the Zip code of California. Here we have an option to choose the variable also.

SSIS Web Service Task 13

Next, click on the SSIS Web Service Task Output tab to configure the Output data.

If we set the OutputType to File connection, we have to configure the Output Connection as File Connection Manager. It means the output XML data stored in a local file system.

SSIS Web Service Task 14

If we set the OutputType to Variable, then we have to configure the Output Connection as Variable. It means the output XML data stored in a variable. This option is handy because we can use this XML data in other tasks or transformations.

SSIS Web Service Task 15

Once you click on the <New Connection..> option, a new window called File Connection Manager Editor opened. Using this, we have to configure the Output Connection.

SSIS Web Service Task 16

Click on the Browse button to create a New XML file available in your local file system or select the Existing File from the file system. Please refer to the File Connection Manager article to understand the configuration.

SSIS Web Service Task 17

From the above screenshot, you can observe that we are creating a new file called WeatherForecast.xml file.

SSIS Web Service Task 18

Click OK to finish configuring and closing SSIS Web Service Task Editor. Let us run the package to check whether we consumed the web service or not.

SSIS Web Service Task 19

Let us open the WeatherForecast.xml file and see.

SSIS Web Service Task 20

From the above screenshot, you can see that we achieved the result.

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy