Advanced ETL processor vs SSIS = Development Efficiency against wasting time
One of our Insurance customers has contacted us recently and asked for immediate assistance. They were trying to implement XML files processing for a number of weeks using SSIS.
Our consultant was able to deliver a working solution within one day using Advanced ETL Processor.
Here is what he did:
The XML files show up every day. The client places them on FTP server (which means that the file is locally found on our network via a UNC path. )
Each nested XML tag should be a new “related” table.
Main XML tag is "Contracts", Other relevant tags are Billing, Coverage, Person, Risk and Underwriting Question.
There are a couple of data issues as bad dates from the customer. They should be ignored and inserted into the table as a Null.
The process should be scheduled to check a UNC folder on the network at least 3 times per day.
If one or more XML files exist, they need to be loaded into the server.
Once loaded an Email should be sent to me indicating success, failure or nothing to do.
There are some duplicated data due to the relationships of the data.
For example, each contract has Agency information. So any contract MAY use a previously inserted Agency In this case Update would be appropriate.
Updating data, if the same contract is loaded the second time all relevant data must be deleted first
For every nested XML tag, a relevant data transformation was created.
One of the main problems was that XML format was not stable, some of the files have missing tags or additional tags.
That was addressed by using custom XSLT transformations
Then the package was created to combine all those transformations together
We have been fighting this one with SSIS for about 2 weeks and got really behind schedule.
Then I remembered we own Advanced ETL Processor so I thought we should give it a shot. We are just so far behind that we need this ASAP and I did not want to mess it up. The result was far beyond our expectations. They were able to do more in one day than the entire team in two weeks.
If you are struggling with SSIS or any other tools and have a very tight deadline contact us and we will do our best to help you today.
In order to speed up the process please provide as much information as you can.
- Several data examples
- Table creation scripts
- Remote access details
- Have someone immediately available for questions