SQL Server 2005: Using Web Services in SSIS Data Flow

Here is a good trick.I have used this to teach people how they need to ensure that they know what limitations there are in SSIS. Basically, if you are only using what SSIS, or any system for that matter, gives you out of the box, then you are missing out on large opportunities that can be had by playing the angles.
So everyone knows that the web services control in SSIS is kind of impractical from the aspect that

#1. It is in the control flow
#2. It is in the control flow

and that the VSA scripting platform on which the custom script component is built upon is fairly scaled down so as to prevent you from adding many things like custom managed assemblies and web references to your code. So you would normally want to assume that you cannot use web services within your script component, Right?
Wrong! By knowing that the limitation exists you can change direction ….. by knowing that SSIS will allow you to add additional class files to your component you now have an out. If you think about it the web reference GUI does nothing more than create and interface file for the web service. So with that in mind do the following…..

1. Open a regular web project within Visual Studio.
2. Add your web reference to the project to so that it create the interface file.
3. Now save your project.
4. Now open your SSIS project and go to edit the script component.
5. Now right click on the Script component top most project node and select ‘Add’->’Add Existing Item’
6. Now select the interface class file that has been created by your web project.
7. This is now added to your project and you only have 2 things left to do.
8. In the class file you just added there will be references to the configuration file which would normally store the value for the location of the web service. Merely change the 2 entries that reference this.
9. Now you are good to go and should be able to use the web service class like any other.

Later, I will be posting about some of the newer features within SSIS 2008 which will include the new VSTA engine that allows all of this stuff natively. However, I have the feeling that a lot of customers out there have just made the switch to 2005 or are waiting it out until the customary SP1 release. So for those of you here is your solution.

Cheers!
AJ

Powered by ScribeFire.