sql server dts: 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 <companyname>_<today's
date>.mdb. The importfolder is called in my case d:\import

- 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.

Actually, examples of the pieces of what you need to do are
on sqldts.com.
Create a global variable for the Access file name.
Use FileSystemObject, set the folder to this directory and
search for the file in this folder. Follow the examples in
the looping, importing, archiving example -
http://www.sqldts.com/default.aspx?246
Look at the ShouldILoop function to get an idea. You just
set the FileSystemObject folder to D:\import, e.g.:
set fso = CreateObject("Scripting.FileSystemObject")
set fold = fso.GetFolder("D:\Import")
Once you find the file, set the global variable to the full
path name.
DTSGlobalVariables("YourAccessFileGV").Value = fil.path
Change the connection for the Access source to the Path you
have in the global variable, e.g.
Dim Cnn
Set Cnn = DTSGlobalVariables.Parent.Connections("Microsoft
Access")
Cnn.DataSource = DTSGlobalVariables("YourAccessFileGV).Value
You can find another example on sqldts at:
http://www.sqldts.com/default.aspx?201
Look at the FileSystemObject.MoveFile for archiving and the
example on sqldts.com for Looping, Importing, Archiving.
Look at step 6 loop around.
http://www.sqldts.com/default.aspx?246

That probably covered the basic on most of the pieces, not
all. You need to try some of those things out, make whatever
changes you need for your situation.

-Sue

On Fri, 25 Feb 2005 09:36:29 +0100, "Jason"
[quoted text, click to view]