October 1, 2008 at 6:40 am
Hi,
I'm new to SSIS and to this community. I'd appreciate if someone can guide/help with my question.
I need to transfer data periodically from a OLTP database to a Reporting Database every month. The data in the OLTP gets updated often and there may be new data as well.
how should i go on this? What spl features / techniques should i use.
As a general requirement, if any one of the data transfer fails, i should rollback all the data moved and restore old data. Also i'd need a log that gives information on data transfer for each table.
Please help with suggestions, similar article reference. If there's any sample, that would be great.
thanks,
BR.
October 1, 2008 at 7:00 am
It is going to depend on your specific requirements.
Do you need all of the data from your OLTP database, or just a portion of the data? Is the structure of your reporting database different than your OLTP database, or is it simply a copy of the data? Are you able to determine which records have changed, or do you actually have to compare each record to determine if something has been modified? Does your reporting database need to retain change history? How much data do you have? Are your OLTP and reporting servers physically near each other, or connected by a fractional T1 or DSL line?
It may be that you need to do some kind of replication or log shipping, or you may want to use SSIS to transfer and transform the data.
October 2, 2008 at 11:08 am
Hi,
Thanks for your reply. Here are the answer for your questions. I appreciate your response.
Do you need all of the data from your OLTP database, or just a portion of the data?
- Not all the data.
Is the structure of your reporting database different than your OLTP database, or is it simply a copy of the data?
- I have 2 different OLTP Database, i have a staging server where i get flat file from one of the DB and map the data and have a new table structure created. And then i move the data to the Reporting database.
Are you able to determine which records have changed, or do you actually have to compare each record to determine if something has been modified?
- Yes, i have to compare each data. It could be dates, $ amount and few other critical information.
Does your reporting database need to retain change history?
- No not necessary to maintain change history. Only would need a general log on successful transfer of each table to make sure everything is done properly.
How much data do you have?
Data is just few hundreds to few thousands within 10000 records in a table.
Are your OLTP and reporting servers physically near each other, or connected by a fractional T1 or DSL line?
Yes, they are in the same network, next to each other.
If i use SSIS, is there any specific feature i need to use.
Please advise, guide me.
thanks,
Bergin.
October 2, 2008 at 11:30 am
SSIS will probably handle what you want. You will probably want to manage pieces of the load differently.
For very small tables (< 1000) records, there is a Slowly Changing Dimension data flow component. Really what this component does is start a wizard to build a few components that will essentially compare your data flow to a table and then insert new records and update existing records - you can then adjust the resulting components if you need to.
For medium tables (1000 to 10,000 records) there is a component here: http://www.sqlbi.com/%5B/url%5D that works pretty well. It is designed to take sorted inputs, compare the records, and output to data flows that you can use to insert, update, and delete. This is really the same thing as using a MERGE JOIN and a CONDITIONAL SPLIT out of the default components, but it gives a few extra features and puts it all into one component. It is free and there is some documentation that is pretty good.
For large tables you will really want to stage the data and use T-SQL to figure out what has changed.
The big difference is that SSIS is always row-by-row processing. It is pretty good at it (better than T-SQL loops will be) but it is pretty slow compared to a set-based operation within the MS SQL database engine.
If you are using SSIS to do comparisons, here is a tip. SSIS is accent-sensitive, case-sensitive, trailing space-sensitive, and very type-sensitive. A MERGE JOIN is not like a T-SQL join, the data types must be exactly the same and you cannot have trailing spaces on one side of the join and not the other. This gets people a lot when using comparison operations and lookup components.
October 2, 2008 at 12:01 pm
Thanks for the quick reply. Let me try this.
- Bergin
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply