What is the best option for passing data from SSIS to WCF?

Question

We are trying to get data out of a SQL Server DB and submit it to a WCF web service. Since using a windows service is not a option in this case, I thought of using SSIS.

I have tried using the Web Service Task, but I'm having difficulties getting it to work. I also understand there are limitation to using Web Service Task. What is the best option for use to pass data from SSIS to WCF? Note: We are restricted in using CLR Integration in SQL.

Answer 1

worth checking out Accessing Web Services in SQL Server 2005

I am not sure if it is the best option but you could use OLE automation within stored proc to invoke the web service.

Call GeoNames Web Services from a SQL Stored Procedure

Answer 2

Another option is not use WCF and use traditional webservice. If the WCF service is not under your control. You could use a traditional webservice to call the wcf service.