July 11, 2006 at 4:21 pm
I am using SQL 2000 with sp 4
The overall goal of this package is to create 76 different text files with certain data.
Then zip up each file separately
Then copy each file to a location on a different server.
Each text file has a unique name and each zip file uses that name as the zip file name.
FTP is not an option.
Currently the package fails the copy segment.
I have a DTS package with the following:
76 DTSStep_DTSDataPumpTask = creating text files, works correctly
76 DTSStep_DTSCreateProcessTask = Zip up each file separately, works correctly
Once these tasks complete I call a copy file package on the same server passing in the correct parameters.
Here is what problem is.
The files are not getting copied from server one to server two.
Now the first thing you might suggest is login and/or permissions.
The SQL Agent is running under a domain account that has the necessary permissions on each server.
If I run the task from within the main package the one that calls the copy file package it works.
If I schedule the copy file package it works.
When the main package is run from a schedule everything works except the copy segment.
I enabled error logging on the main package and according to the log all tasks were successful but the file were not copied.
So I am at a loss here.
Any help is always welcome even if I have already tried it.
Thanks
July 11, 2006 at 4:45 pm
>>Once these tasks complete I call a copy file package on the same server passing in the correct parameters.
How are the files being copied ?
Exec Process task calling a batch file ?
VBScript task using FileSyetemObject or other VBScript method to copy the files ?
T-SQL with xp_cmdshell ?
July 11, 2006 at 4:52 pm
I am sorry I did not mention that. I am using the filesystemobject through an activex task.
Gary
July 11, 2006 at 4:58 pm
Make sure logging is turned on for the package. In the ActiveX script task use this:
DTSPackageLog.WriteStringToLog "Your extra logging goes here"
... to add additional diagnostic traces to the DTS package log file. Assuming you're doing this in some sort of loop, maybe send the values of Err.Number and Err.Description to the package log after every attempt to call FSO.CopyFile()
July 11, 2006 at 4:59 pm
So your main package has an Execute Package Task that executes a 2nd DTS package that has an Active X Script task that uses the FileSystemObject to copy the files?
If so, I assume you're using a Global variable to pass the file path to the 2nd package. Does the name of the variable you're using in the Execute Package Task's Outer Package Global Variables list match the name of the global variable in the 2nd package exactly? If not, it should.
July 11, 2006 at 5:01 pm
Yes, the variable names match exactly.
Gary
July 16, 2006 at 1:54 am
Since the copy is working fine when run on it's own, or when run manually from the main package, the first place I would be looking is the global variables that determine the "to" and "from" file paths. Are they possibly being changed by the previous steps when you run the entire "main" package, or in your job ? You could try logging the GV values from the activex script in your copy package, using WriteStringToLog as suggested by PW, to make absolutely sure the scheduled job is not changing file paths
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply