| Author |
 Topic  |
|
|
pyu.agrawal
Starting Member
29 Posts |
 Posted - 06/15/2012 : 03:02:44
|
Hello
I have a huge FoxPro database table (which is not normalized). This data now needs to be moved to SQL Server. We tried doing it with a DataFlowTask in SSIS which was time consuming and took considerable time - a day to complete. To overcome this, we created a BulkInsert task which moved the data from FoxPro to SQL in a few minutes and then invoked an SP, which read data (with a cursor) from the temp table and copied it to respective tables, which again is time consuming.
Any possible suggestions/alternatives so that this could be achieved in lesser time. We have numerous tables for which this needs to be done.
Thanks |
|
|
prett
Posting Yak Master
USA
161 Posts |
Posted - 06/15/2012 : 06:54:02
|
There is another way to move FoxPro database table to SQL Server that is: “DTS Import / Export Wizard”. You can start the DTS Import / Export Wizard by choosing Start / Programs / Microsoft SQL Server / Import and Export Data. The initial screen will inform you to transform data from a variety of sources using the DTS. Next, you are asked to select a data source.
The Dropdown box lets you select the driver for your data source. The available options include MS SQL Server, Oracle, FoxPro, Dbase, Paradox ODBC and OLE DB drivers as well as spreadsheet files (in Excel format), text files and OLE DB provider for Analysis Services (which was referred to as OLAP Services in SQL Server 7.0).
Ref: http://sqlserverpedia.com/wiki/DTS_Import/Export_Wizard
http://msdn.microsoft.com/en-us/library/aa176528%28v=sql.80%29.aspx |
 |
|
|
pyu.agrawal
Starting Member
29 Posts |
Posted - 06/15/2012 : 08:27:07
|
| Thanks. But I would like to use Stored Procedures to insert data in SQL. Will "DTS Import / Export Wizard" help me with that ? |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/15/2012 : 12:00:04
|
might be able to use a sproc to get the data, dump it to a staging area and then use another sproc to push it to sql
http://support.microsoft.com/kb/207595
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
prett
Posting Yak Master
USA
161 Posts |
|
|
pyu.agrawal
Starting Member
29 Posts |
Posted - 06/22/2012 : 10:24:53
|
Only SProcs won't help me. I have already tried using procs. but it takes around 10 hours which obviously won't help me.
Here's what I have done:
1. Get data from FoxPro to SQL staging table (already done with the help of BulkInsert task)
2. Process each row from the SQL staging table and push those rows to the actual SQL table (with the help of a proc, since I have custom logic written to it).
What I would like to have:
1. Reduce the time taken with (maybe by running parallel tasks, or any other way as may deem fit)
Thanks |
Edited by - pyu.agrawal on 06/22/2012 10:25:17 |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/22/2012 : 13:06:41
|
why are you processing one row at a time? you should try doing it set based 100,000 rows at a time.
also why is it taking so long, are you missing indexes on Staging tables? how are you pushing these rows from staging to destination, can you show us sproc that does that?
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
pyu.agrawal
Starting Member
29 Posts |
Posted - 06/23/2012 : 04:14:16
|
How could that be done set based ?
I'm using an Oledb command (within an SSIS package) to read a row and to push it to the destination.
Regarding indexes, there are no indexes on the staging table, and they are being disabled on the destination table before the package executes. |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/25/2012 : 12:56:08
|
I would recommend you use a Data Flow task with but you would need some other table in FoxPro that keeps track of what has moved to sql or not.
so that once you dump the data to sql you have to have a way of keeping track of what was moved so that you can do set based 100k at a time.
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
pyu.agrawal
Starting Member
29 Posts |
Posted - 06/27/2012 : 09:04:58
|
This would be a one time job. I just need to move that data to SQL.
The script takes 1 hour for then thousand rows, and I have 7 million rows. |
|
|
| |
Topic |
|
FoxPro to SQL
