I was working on SQL SSIS and hit upon a limitation with the web service task in the SSIS package. The web service SSIS package has known limitations to call WCF web service.
To overcome this limitation and to call a WCF service from SQL I thought upon the alternatives available. I came across the SQL CLR functionality.
I found that to invoke a web service from SQL (through SQL statements and stored procedures), understanding the capabilities of SQL CLR and exposing a .NET method as a CLR Stored Procedure is very essential.
SQL Server 2005 integration with CLR
Microsoft SQL Server now features the integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. For SQL Server users and application developers, CLR integration means that developers can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.
Creating a WCF client
To consume a WCF service through SQL, we need to create a client proxy for the WCF service. The steps involved in creating a client are
The above steps are sufficient in order to call a WCF service from a .NET managed application. However to invoke the WCF from a SQL statement or Stored Procedure the following additional steps are required
[SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read)]
Exposing a WCF client as a SQL CLR Function
To load the WCF client assembly into SQL, the below steps need to be followed
'clr enabled', 1
DATABASE <database name>
FROM <Fully Qualified Path to the assembly file>
FUNCTION <CLR Function Name>(<parameters to the WCF client function)
<return values with data type>
Introducing SQL Server 2005's CLR Integration - http://www.devsource.com/print_article2/0,1217,a=151093,00.asp
CLR Integration Programming Model Restrictions - http://msdn2.microsoft.com/en-us/library/ms403273.aspx
What is Windows Communication Foundation - http://msdn2.microsoft.com/en-us/library/ms731082.aspx