February 25, 2005 at 6:50 am
Hi,
I was wondering how i could use dts to import accessfiles and then archive
them to another folder. I've read some examples on sqldts.com but i still
can't figure it out.
Basically this is what i want:
- import an accessfile which has a name like this
- import tables to temporary tables for processing (maybe to the tempdb, if
possible)
- for updating some fields, the dts should use the company's name from the
accessfile and check that with a lookuptable to translate the name into a
int value.
- after processing, the dts should place the file to another folder. In my
case it is d:\archive
If someone can help me with this, i would be very greatful.
February 25, 2005 at 10:18 am
You have a few moving parts here and I'd tackle them separately and get each to work.
For the moving of the files. You need to use an ActiveX script and the FileSystemObject to move them. Here's an example of moving files: http://www.sqlservercentral.com/columnists/sjones/pushthoselogsaway.asp
For the import, I'd setup a staging table and call a stored procedure to clear the table before importing the data from each file into this table. Then call a stored procedure from the DTS to process the data.
Lastly, if you need to loop, there are some examples on SQLDTS.com, but they are confusing. The way I've done it is to write an outer package that has an ActiveX script that loops through the files. Build this first. Then use this script to call the other package. SQLDts has some examples on a package calling another.
February 25, 2005 at 12:20 pm
Hi Steve,
I would like to do it, but my skills in ActiveX-scripting is not so good at all. I was hoping you could provide me some code to work with.
The access-files i'm working with contains at least 10 tables which need to be imported in sql server.
I hope i'm not asking to much, but can you help me with this?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply