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
Obtain the service contract, binding, and address information for a service endpoint.
Create a WCF client using that information.
Close the WCF client object.
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
Expose a public static method which in turn calls the WCF service
Decorate the static method with SqlFuntion attribute, as below
[SqlFunction(FillRowMethodName = “FillRow”, DataAccess = DataAccessKind.Read)]
Provide a FillRow method. The FillRow method acts as a helper method which fills in a row of the result set returned by the table valued function. FillRow method is the place to handle Null values and gives the developer a chance to pass an appropriate value in case the actual result returned by the client is null.
At the command prompt CD to the output directory and execute the sgen command. This creates the <wcf_client_assembly_name>.XmlSerializers.dll library necessary to avoid dynamically generating the serializers used during the invocation of the web service.
Exposing a WCF client as a SQL CLR Function
To load the WCF client assembly into SQL, the below steps need to be followed
Enable CLR on SQL by executing the following statement
‘clr enabled’, 1
Set the database Trustworthy to ON
DATABASE <database name>
Create the assembly in SQL by using the CREATE Assembly statement as below. Note the use of PERMISSION_SET = UNSAFE. This is required since the CLR based stored procedure bas to cross the database boundary and access external resources.
FROM <Fully Qualified Path to the assembly file>
Expose the static method in the assembly (uploaded in the previous step) as a SQL Function, like below
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