Hi! I´m working as a developer and from time-to-time I do some DW and Cube work (Sql server, SSIS and SSAS). Until now I have been doing a couple of SSIS jobs which retrievs data from one (1) source database to a central DW via night batches). This source database is a database that was designed by me and other devolpers at my company. This means that we have a good control and knowledge about the source data. The company has know bought a new software which is completly built by another company. It has a SQL server database at the bottom (will be hosted by us) wish I, at this moment, don´t know the structure of. The structure of the datbase might change slighty over time. I want to get data from this system to the central data warehouse. What is the most common/nice way to complete this task. Should I try to get data directly from the there database into some staging tables in the DW via a SSIS job or maybe let the provider of the system do some stored procedures returning the data I want or maybe exchange data through xml-messages sent to the dw when things are created/changed or is there other better ways? Thanks in advance / Steve

Hi Steve, We do this in a number of different ways and I'm sure there's not one single answer. You could use SSIS direct from the source system but depending on the frequency with which you want to update the data warehouse it might be worth restoring a copy of the backup and using that to avoid additional load on the application server. You could then muck around with the backup as much as you like without modifying the application, restoring the backup will take some time though. You could look at getting some kind of extract from the source system using XML or flat files and then use SSIS to load that into staging tables and work from there. This could be implemented with some stored procedures and BCP. With another (Oracle) system we use SSIS directly from a replicated copy of the live database so we're not putting too much load on the applicatiuon servers but can query it however we like whenever we like. These are just a few ideas and I'm sure others will different suggestions. Matt

Hi Steve, We do this in a number of different ways and I'm sure there's not one single answer. You could use SSIS direct from the source system but depending on the frequency with which you want to update the data warehouse it might be worth restoring a copy of the backup and using that to avoid additional load on the application server. You could then muck around with the backup as much as you like without modifying the application, restoring the backup will take some time though. You could look at getting some kind of extract from the source system using XML or flat files and then use SSIS to load that into staging tables and work from there. This could be implemented with some stored procedures and BCP. With another (Oracle) system we use SSIS directly from a replicated copy of the live database so we're not putting too much load on the applicatiuon servers but can query it however we like whenever we like. These are just a few ideas and I'm sure others will different suggestions. Matt

Hello, You can request them to create a cvs file on daily base for you with changes(Delta) and load into your DW. Or You can extract by using some SP(Better to get Delta records(Changed)) , that will be fast instead of extracting everything from Source and write into staging and then write changed into DW. You might want to use the csv file as source, so third party will create this csv file for you on regular intervals and then you load it by using SSIS package. You might want to avoid using SP/Views as data is coming from thirty party and they can complain that your process is making their system slow.. Thankshttp://sqlage.blogspot.com/

Hello, You can request them to create a cvs file on daily base for you with changes(Delta) and load into your DW. Or You can extract by using some SP(Better to get Delta records(Changed)) , that will be fast instead of extracting everything from Source and write into staging and then write changed into DW. You might want to use the csv file as source, so third party will create this csv file for you on regular intervals and then you load it by using SSIS package. You might want to avoid using SP/Views as data is coming from thirty party and they can complain that your process is making their system slow.. Thankshttp://sqlage.blogspot.com/

Thanks for all good suggestions. I have started to think about having some kind of xml-extract (deltas) from the source system. An idea is to first take the data and store it (near realtime) in a standard relational database (by my design) and have timestamp on all table.rows. Then later update the DW with "my" relational database as the source. Does that sounds lika a good solution? /Steve