SSIS web service task input parameters

Question

There is not a way to pass parameters to input of Web Service tasks.  I heard this problem is fixed with SQL2K5 SP1 and even the online doc says that one can choose either "value" or "variable" when specifying input for web service tasks, but after I installed what-I-think-is SP1, there is still no way to do this.

If one can only specify values (hard-coded) as input to web service tasks, then this would be a very severe limitation.  I hope I'm wrong, so could someone please give a pointer.  Thanks

Kevin Le

Answer 1

Kevin,

As books online states you are able to use variables with the Web Service Task with SP1.  On the inputs page of the editor after you select a method there will be a new column titled Variable.  That column contains checkboxes that when checked make a dropdown variable selector visible in the Value column.  Let me know if you are not seeing the variable column and we can go from there.

Thanks,
Patrik

Answer 2

Thanks for replying, Patrik.  This is the online doc that I saw and probably what you refer to, as well:
http://msdn2.microsoft.com/en-us/library/ms187617.aspx

This is the SQL2005 SP1 that I downloaded and installed
http://www.microsoft.com/downloads/details.aspx?familyid=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&displaylang=en

but it still does not work, i.e. the input page do not offer passing variables to input.  I just took a closer look, and I think the downloadable above is only for Vista, so apparently I didn't install the correct SP1.  Do you know the correct SP1 to be installed?  Thanks

Answer 3

SP1 can be found at:  http://www.microsoft.com/sql/sp1.mspx.  Which will take you to the path that you mentioned.  If you're unable to view the variables option after successfully installing SP1 on a newly created Web Service Task please file an issue on http://connect.microsoft.com.

Thanks,
Patrik

Answer 4

Patrik,

The URL you gave will eventually lead to the one that I posted earlier.  I got the problem resolved now. 

For those who are interested, when installing SP1, the installer gave options to install partially.  That's what I did by just selecting the SSIS choice.  That's why I still did not get the variables input to web service tasks.   You would  be better off installing SP1 entirely.

One other note, turn off all running services such as SSIS, SSRS, SSAS, etc, but leave the SQL2005 service running.  If SQL2005 service is not running, the installer will fail when trying to authenticate.

Answer 5

Kevin,

I'm glad you are able to add variables to your Web Service Task now.

For future reference, the service pack download page includes a link to a readme document which describes all the installation choices and fixes included in the service pack.  For SP1 the readme file can be found here at:  http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQL2005SP1.htm#_3462_install_sql_server_2005_sp1_cuy1.

By selecting just the SSIS option you updated the SSIS service, but not the tools components.

Thank you for sharing your findings with the forum!

Thanks again,
Patrik