What does all that nerd lingo mean?
Basically I have some data I need to take out of the database and pass it to another server for processing in a service oriented architecture.
When this remote process is complete it will pass me back the results that I will save in the database.
The transport mechanism we will use is WCF or windows communication foundation. To use this on the client side, which happens to be a database process. We will create a proxy class.
SQL SSIS is SQL Server's new DTS on steroids performance enhanced DTS. SSIS allows scripting in VB.net only (don't ask), and to use outside compiled dll's you have to register them in the Global Assembly Cache with a strong name, this is just a fancy way of making sure your dll's are unique.
This will happen on a timed interval. Seams pretty simple huh?
In order to leverage windows communication foundation within SQL Server SSIS there are a number of things you have to do to make this happen.
Build your client proxy WCF service model bindings from WCF service.
- Build an assembly that will wrap the logic for your WCF call.
- Sign the assembly with a strong name. (sn.exe or VS IDE).
- GAC your new assembly on the SQL box by copying into C:\WINDOWS\assembly.
- Merge your proxy app settings into DtsDebugHost.exe.config and DTExec.exe.config.
- Welding in custom assemblies in SSIS with configuration variables.
Make sure your dll is GAC'ed and refernced in your SSIS script project.
Make sure your dll is added to your Package.dtsx.
