Using WCF Adapters with SQL Server Integration Services(SSIS)
There are two ways the adapters belonging to Adapter Pack can be used by SQL Service Integration Services:
1. Using ADO layer built on top of base adapters. This option is available with SAP and Siebel adapters.
2. Writing custom SSIS task which directly invokes adapter to communicate with LOB system.
Option 1 covers all scenarios where data need to be moved from LOB system into SQL server. The reason being that ADO layer exposes only Query and Execute interfaces. Using these ADO interfaces, we can only pull out data from LOB system.
If usage scenario is such that data has to be loaded into LOB system, then option 1 doesn't help. But that is the not end of story. We can write custom SSIS task. This custom task essentially wraps the adapter as destination component. Once adapter is exposed as SSIS component, it is available for use in any SSIS scenario.
I am attaching a sample for inserting data from flat file into Siebel. The flat file can easily be replaced by any other SSIS source component.
The sample attached here is modeled on SSIS sample "DatasetDestination Component" distributed as part of SQL SDK.
This sample contains two projects:
1. Custom SSIS component implementation, and
2. SSIS project. This reads data from flatfile source and pushes it to custom SSIS destination component. This custom component internally invokes Siebel adapter to send data to Siebel server.
There is instruction.txt which describes how to use above sample. Since this sample is just a proof of concept there are few assumptions made, which are:
1. We want to insert data into "Contact" business component in Siebel.
2. The name of the fields for which we want to specify value, must be specified in flatfile. These field names should match the field names exposed on Contact proxy object which in turn are derived from Siebel Contact business component's actual field names.
We can easily extend this sample to allow users to decide which back-end object he/she is interested in. The custom task can generate proxy for selected object on demand. Also the fields user is interested can be specified while component is being configured.