importing and archiving

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

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

--

----------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.comI tend to avoid DTS. I prefer to use xp_cmdshell (to perform Command
line commands) and linked servers. In this case, use xp_cmdshell to
FTP the files down. Create a linked server to the Access MDB.
Process. Then use xp_cmdshell to move the file to the archive folder.

//FTP SP
CREATE PROCEDURE FTP
(
@.ftpserver varchar(50)='myserver' ,
@.ftpuser varchar(50)='myusername' ,
@.ftppwd varchar(50)='mypassword' ,
@.batchfile varchar(100)='\\myserver\path\ftpcmd.txt' ,
@.destfile varchar(100)='\\myserver\path\mydestfile.txt' ,
@.sourcefile varchar(50)='mysourcefile',
@.mode varchar(3)='get' -- get | put
)
as
DECLARE @.cmd varchar(1000)

SELECT @.cmd = 'echo ' + 'open ' + @.ftpserver + ' > ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'echo ' + @.ftpuser + '>> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'echo ' + @.ftppwd + '>> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd

SELECT @.cmd = 'echo ' + @.mode + ' ' + @.sourcefile + ' ' + @.destfile + '
>> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'echo ' + 'quit' + ' >> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'ftp -s:' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'del ' + @.batchfile
EXEC master..xp_cmdshell @.cmd|||"Ezekil" <ezekil@.lycos.com> wrote in message
news:41ffd97b$0$71093$cd19a363@.news.wanadoo.nl...
> 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

Generate the current Access filename and assign it to a package global
variable in an ActiveX step, then use a Dynamic Properties task to set the
Access Connection properties.

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

It depends if you're going to load the Access data into MSSQL or not - if
so, then an Execute SQL task could join the tables or call a stored proc.
Otherwise, ActiveX will work again.

> - after some processing, the dts should place the file to another folder.
> In my case it is d:\archive

Another ActiveX step, or just an Execute Process task to move the file.

Simon