Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
versatech
Starting Member
1 Post |
 Posted - 2002-08-27 : 22:19:13
|
| Hi,I've never used DTS before, but would like to employ it to do a rather complicated data import (well complicated in my opinion).I need to use DTS to import rows from a DBF File into a SQL Table. These DBF files reside on a separate server and can only be operated on once copied to another location so they aren't in an open state. Specifically I want to append the data from the DBF file into a SQL table. That is, a program writes data to a DBF file, and I want to import the new data not yet imported into SQL since the previous import. Another issue is that these DBF files change filenames (table names) every month. In other words, every month a new DBF table is created with the month number in the table name. I would have to import those, but make sure before I do so that all the rows from the previous month's table have been imported and then import the current month. Where I am find myself having the real problem is that in the DBF file, each row doesn't have a primary key I can reference it with. Only way to distinguish one row from another is to reference two columns, a timestamp and account number, those two columns can never be the same in another row. So I am not sure how to keep track of which row to start importing data from the DBF file.Of course, lastly I'd like to automate this entire process so it happens every minute or two.Hope that makes sense. Any help or thoughts would be greatly appreciated.Regards,-= Ayaz |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-28 : 03:11:13
|
| You don't say how much data is in the dbf files but it sounds like you will be importing all the data into a database then running a storted proc on it to merge with the production system.Why are you set on dts?I would keep the import as a separate task so that you can change it at will - maybe the source system can give you a better fotmat later.Schedule a task in sql server which has several steps.Check DBF file - check file is in order (previous month complete) + log filename - you can change the name that is accessed in dts but might find it better to run a step to rename or copy the file to a constant name.Prepare for import - clear data from import table - remove indexes?Import the data - this can be your dts task or any other method .Index import table?Merge data - using the indexed columnslog completionAt every stage log what has happenned for support purposes.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-08-28 : 06:44:29
|
| Hi.I agree with nr, the DTS (or whatever) is a tool to import the raw data into a staging table or database situated on the server. I tried implementing a similar import using only DTS, but I soon realised it worked much better when I separated the steps and made all the "cleaning" and merging of data using stored procedures instead of the DTS.Good luck,Kalle Dahlberg |
|
|
|
|
|
|
|
|
DTS to Import DBF