April 20, 2010 at 11:08 pm
Hi All
I'm migrating data from Flat files to SQL server 2008.
The data in flat files are huge. There are about 100 flat files with 40 years of data in one flat file which needs to be exported to SQL 2008 table.
1 flat file contains data for 1 centre for 1 year
Likewise this needs to be done for 7 tables.
SSIS is being used for this migration with some data manipulation involved.
Now, the question, migrating data from 1 flat file to 1 SQL table taken about 60 mins. Hence if we imagine the time for all the flat files it would take months.
We can't have production stop for months.
So what should be approach followed in this case.
What are your opinions?
Thanks
Khushbu
April 21, 2010 at 2:24 am
Hi,
i would set up a second database and create a db link to access the old data.
maybe it would be better to import in more then one database parallel
regards
April 21, 2010 at 5:59 am
You say the data is "huge" but what does that mean in real terms? It sounds like you may have tuning opportunities in the SSIS data load.
Regardless, one way around this would be to look into partitioning. You can load a partition independent of the rest of the table, which will be accessible. Then you bring the partition online and move to the next file and the next partition...
I'd still focus on getting the load time down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply