DTS Package to copy .txt file to another folder
Posted on 2008-10-01
I'm currently using SQL 2000. I'm attempting to create a DTS package that will copy a text file, created by another DTS, to a different folder. The files I'm copying will be saved under the same name with the addition of today's date for historical purposes. Below is the DTS Function I have written:
Set oFSO = CreateObject("Scripting.FileSystemObject")
strtoday ="_"&left(now(),2) & right(left(now(), 5),2) & "_"
SourceFile = "\\IEHPCORP\MaxMC\Data\MEM\HOURLY\MEMBER2.dat"
DestinationFile = "\\IEHPCORP\MaxMC\Data\MEM\HOURLY\Historical\MEMBER2"&strtoday&strtime&".dat"
oFSO.CopyFile SourceFile, DestinationFile
oFSO.CopyFile "\\IEHPCORP\MaxMC\Data\MEM\HOURLY\MEMBER2.dat", "\\IEHPCORP\MaxMC\Data\MEM\HOURLY\Historical\MEMBER2"&strtoday&strtime&".dat"
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
When I run the DTS fromt he DTS section it runs with no issues, but when I create a JOB to run it, it fails at the following line: oFSO.CopyFile SourceFile, DestinationFile. I'be crated the job from the DTS section and I've also created the job manually using exec master..xp_cmdshell 'DTSRun /S IEHPSQL1 /E /N "HrlJobs"' but I get the same results.
Can you give me an idea what I'm doing wrong or suggestions on how else to copy these files?