February 17, 2004 at 12:17 pm
Problem: Transfer a .dbf file from a remote location, update an SQL database with the info in the dbf file, then update the dbf file with info returned from SQL server and send it back to the remote location. Retrieving and sending the file is working fine.
Application: The remote locations are retail point-of-sale systems with employee data held in a .dbf fomat file. I need to retrieve new employee data from these .dbf files and use it to create records in our HR system. Subsequently I need to update that dbf file with the HR systems unique record ID for the employee.
Current solution:
1) We are using an application to transfer to the .dbf file to a directory with this format: \<storenumber>\emp.dbf
2) After the .dbf file is transferred a DTS package is run that takes the info from the DBF file and inserts it into a staging table. An insert trigger is fired that runs an ETL process against this data and subsequently inserts the data into the HR system.
3) At this point I have the unique ID from the HR system in the staging table. How do I update the .dbf with information from the staging table?
The DTS package is very simple – 1) a dynamic properties task to create the path to the dbf based on global variables; 2) a DBF connection to the employee .dbf file (emp.dbf); 3) A transform data task connecting the DBF connection to SQL Server. This moves data from the .dbf file into the SQL staging table (on insert a trigger is fired that generates the row in the HR system).
This is where I’m stuck – I don’t know how to update the employee records in the DBF file with new data.
Chris
MSN Messenger: chrisacahill@hotmail.com
February 18, 2004 at 8:31 am
Do you have the luxury of being able to simply recreate the dbf files? If so my recommendation is to upate the SQL tables, then dts out new DBF files that have the new UID in it.
February 18, 2004 at 8:45 am
This is a possibility, however it is really a "path of that resort". Any other suggestions?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply