Deleting/moving file from remote FTP area using FTP task

Question

Post reply

Deleting/moving file from remote FTP area using FTP task

Rajarshi Mukherjee

SSC-Addicted

Points: 442
More actions
January 13, 2009 at 3:24 am

#383724

I have prepared a DTS which is scheduled via a job. The jobs executes after every 5 minutes and check whether data feed is available on remote FTP area. Once feed is available it will be uploaded into database. Now my requiremnt is that - that input file needs to be deleted/moved immediately after the loading is taken place. Otherwise it will be reuploaded into database.
How can I do this using SQL 2000 DTS FTP task? I tried but could copied the file to archive folder but couldn't move/delete. It's single file with static name and FTP area is a windows server
Please help me....

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

prescientdba SSC Veteran Points: 278 More actions · Answer 1

Have you tried to rename the file with the date appended to the regular fle name??

virgo Ten Centuries Points: 1266 More actions · Answer 2

u can just put an activex script after ur FTP task to move or delete processed file.

here is the code for activex script

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim fso, f

DTSGlobalVariables("Filepath").Value="Mention ur file path"

DTSGlobalVariables("MoveFilePath").Value="Mention path where the files need to be moved once processed"

Set fso = CreateObject("Scripting.FileSystemObject")

Set f = fso.GetFile(DTSGlobalVariables("Filepath").Value)

f.move(DTSGlobalVariables("MoveFilePath").Value)

fso.deletefile(DTSGlobalVariables("Filepath").Value )

Main = DTSTaskExecResult_Success

End Function

Rajarshi Mukherjee SSC-Addicted Points: 442 More actions · Answer 3

Great !!

Works Perfectly Fine. But showing a error message "Error Source= Microsoft VBScript runtime error Error Description: File not found". Not Sure why !!!

I have pasted the whole message from job history. Again many thanks for your help

DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 16 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 16 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: File not found Error on Line 15 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 0 Error Source= Microsoft VBScript runtime error Error Descript... Process Exit Code 1. The step failed.