By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

importing and archiving

P: n/a
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.com
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I 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

Jul 23 '05 #2

P: n/a

"EzekiŽl" <ezekiŽl@lycos.com> wrote in message
news:41***********************@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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.