September 7, 2005 at 5:57 am
Hi All,
I need to import a txt file from Server A to Server B and import the data from txt file to one of the tables on Server B on a dilay basis. Every new file should overwrite the existing file.
The table should be truncated before importing data from txt file daily.
Please guide on the steps to create a DTS package and job to implement this.
Thanks.
September 7, 2005 at 6:11 am
OK. Stick it in a dts package to make it manageable and trackable.
Step 1 - use xp_cmdshell to overwrite the existing file with the new on.
Step 2 - set up a text file source connection and a database destination connection.
Step 3 - Create an Execute sql task using the db destination connection truncating the destination table.
Step 4 - set up a simple transform data task mapping the text file columns to the table columns.
Make sure you connect the flow using on success in the following order.
1.Overwrite file
2.Truncate table
3.Pump the data across
That way, if the text file does not import, you will still be left with the previous day's data rather than none at all. Remember to turn logging on so that you can check for errors if they occur.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply