473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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.com
Jul 23 '05 #1
2 1195
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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: tim groulx | last post by:
Hello, Does anyone know of a way to schedule the archiving of analysis databases? Seems pretty lame if you can't... The only answer I've gotten is "maybe in Yukon".... Thanks.
2
by: Lee Ottaway | last post by:
In order to achieve the archiving of old data from my database I have exported the information from my dataset to an XML file using the standard WriteXML method of the object. Now suppose in the...
5
by: SunSmile | last post by:
Hi, I am logging my exceptions to a word document(*.doc). After the size of word document is 5KB. I am archiving the word document to *.doc.1 Here when I am archiving the word document to...
4
by: Geoff | last post by:
I want to archive records from one recordset to another recordset. Not too sure of what to do in commented areas shown below. Dim Db As DAO.Database Dim Rec1 As DAO.Recordset 'Set as...
5
by: pike | last post by:
Hi Single partition DB2v8.1 FP7 on AIX 5.2. I'm trying to determine whether we're archiving logs directly to tape (and not to disk) using TSM. Without knowing from which sample it was...
1
by: sandip | last post by:
Hi All, Can someone please help me with good and easy-to-use data archiving tools for DB2 database? Does anyone have previous experience with IBM DB2 Data Archive Expert tool? Is this a...
4
by: Paul H | last post by:
Could some one give me some pointers on basic archiving techniques? I have developed several databases but never been faced with this issue. Here is the basic scenario.. Suppose I have the...
2
by: Knokmans | last post by:
Hi, first, this concerns db2 v8 and v9 on AIX and Linux/intel Until now we used a "user exit" when we want to archive logfiles. We changed the user exit executable a bit so we receive an email...
3
bvdet
by: bvdet | last post by:
Following is an example that may provide a solution to you: """ Function makeArchive is a wrapper for the Python class zipfile.ZipFile 'fileList' is a list of file names - full path each name...
6
by: Salad | last post by:
I'm going to implement an archival database for my app. I have Table1 and Table2. Table2 is a duplicate structure of Table1. I appended records with "ID between 1 and 100", then "ID between 300...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.