How to Use a Web Service in an SSIS Data Flow

SSIS Data Flow transfers data among sources and destinations. It provides users with the flexibility to change, clean and alter data as it is transferred. The movement of data can take many forms, which includes the utilization of Web services. SSIS has the functionality for Web services built into its features. This is a useful benefit to SSIS users because many people use electronic devices over a network to communicate, and this is a core function regarding Web services.

Instructions

1. Create a WCF (Windows Communication Foundation) Service for testing. Open the "New Project" window. Select the "Install Templates" option from the displayed list. Select the "Visual C#" option from the displayed list. Click the "WCF" option from the list. Select the "WCF Service Application" from the available list and then name the service. Click the "OK" button.

2. Press the "F5" key to run the WCF service. Record the URL of the .svc file.

3. Create a new SSIS Package. Open the "SQL server Business Intelligence Development studio" or the "Visual studio 2005." The wording you will see depends on the version of SSIS currently running on your system.

   Select the "Integration Services project" from the displayed list. Name the project. Click the "OK" button. This action will display the Package Designer window. Select and complete the options and information needed for your desired SSIS package.

4. Add a DataFlow and a Script Component configured to the Source. Right-click the "Data Source" and click the "New Data Source" provided on the Package Designer window. Name the data source. Add the required connection string needed for your situation. Click the "OK" button. Drag and drop a "Data Flow Task" from the toolbox onto the Control Flow section. Drag a "Script Component SSIS Data Flow" item to the Data Flow workspace. Double-click on the "Script Component" and select the option needed from the displayed list for your particular situation.

5. Add Output Columns to the script source to equal the data flowing away from the Web service. Double-click on the "Script Component" and select the "Input and Output" option. Expand the Output Column. Click the "Add Column" option.

6. Edit the Script for the Script Source and change the target .NET Framework from 2.0 to 3.5 to use WCF Service.

7. Add a Service Reference in the Script Project. Click the "Reference" option displayed in the Integration Services Script Component window. Select the "Add Service Reference" option from the displayed list.

8. Enter the URL of your Web service and rename the URL in the specified field(s) inside the Add Service Reference window.

9. Create a package variable to configure the service URL and provide the script source for read-only access regarding the variable. Open the "Script Component" and select the "Script" option. Enter the service URL and select the read-only option for the script source in the specified fields within the Script Transformation Editor window.

10. Edit the Script to configure the WCF client with code and forward the Package Variable concerning the URL. You can view the app.config placed in the script project by the Add Service Reference to determine the required binding.

11. Add a Data Flow destination. Double-click on "OLE DB Destination" under DataFlow Destinations in the Toolbox on the main window. Drag the green arrow from the selected data source to the OLE DB Destination.

12. Test the SSIS package to make sure that it produces the desired results.

13. Set the value of the Package Variable on the command line. Right-click in the "Control Flow" of the desired package and select "Package Configurations." Click the check box to enable package configurations. Click "Add." Click "Next." Select the type of configuration you wish to use and a location; then click "Next." Select the object to configure and then click "Next. Name the configuration and then click "Finish."

References

• Microsoft MSDN: Web Service Task