Web service versus directly sourcing database

[blom0344]

We have an ongoing debate about the use of webservices as a ETL-source. Our application development engineers propagate using only a webservice as a source for extracting data. They would like to prohibit direct database access.
We have experienced with SSIS and webservices and found it cumbersome and slow. Application development thinks that we are to stupid to correctly access tables, but we fear the overhead of having to go through a webservice to simply transfer data. The debate is as yet inconclusive.. Any thoughts?

Ties Blom

 

[RiverGuy]

It sounds like a turf war or something. I doubt they think you are "too stupid." Each side should approach this professionally.

One suggestion would be to request read-only access to their test server for this database. Write your ETLs against the test server. When you're finished, review your select queries with the source system owners so they know what's going on and can offer any suggestions if needed. After that, I bet they would be more comfortable with allowing you to run these queries against production.

 

[blom0344]

Their main argument is based on the 'fact' that some factors will not be stored in database tables , but can only be reproduced through the application logic, hence a webservice. However these are exceptions, since most data just sits there ready to be extracted. We have experimented with SSIS reading the output of a webservice, but have found it to be cumbersome and decidedly slow. It is also not a good way to extract large facttables, cause it would require a batch oriented type of extraction (webservices do not allow large amounts of data in one go..)

Ties Blom