I'm using DTS to update an Excel file on a local drive and then FTP that
updated file to another server. The FTP process (an 'execute task process')
is set to begin 'on success' of the 'copy data' process and, sure enough, the
DOS window in which the FTP process runs doesn't fire up until the 'copy
data' process has completed.

The problem is that the FTP process is somehow picking up the original
version of the Excel file rather than the updated version.

Can anyone explain and/or suggest a solution for this?

I've run several similar types of things and never ran into
that issue in the process. Are you archiving the old file
somewhere? Did you double check whatever is used to
determine what file to send and the path, file name for this
file? Sounds like it could be something in the code or logic
in sending the file.

-Sue

On Tue, 15 Feb 2005 09:19:05 -0800, Phil Cook <Phil
Cook (AT) discussions (DOT) microsoft.com> wrote:

Thanks Sue.

I've checked many times and I can't see anything obvious. I've tried just
executing the single 'execute task process' on its own and it works fine.
Could it be that the task is somehow kicking off before the 'copy data'
process has completed the writing of the updated file?



"Sue Hoegemeier" wrote:

Hi Phil,
I suppose anything is possible but you already said that the
ftp process doesn't fire up until the 'copy data process'
has completed so that's why I was focusing on the file
rather than the process. And you said you have a precedent
constraint defined between the two tasks?
What is the task you are using for the copy data process?
Also, are you sure it's not a matter of the file just not
overwriting when you ftp it?

-Sue

On Tue, 15 Feb 2005 14:59:03 -0800, Phil Cook
<PhilCook (AT) discussions (DOT) microsoft.com> wrote:

Sue, its a 'transform data' process that copies the data into an Excel
spreadsheet. The spreadsheet is then moved using an ftp.exe 'put'. The file
at the FTP destination is getting updated each time but with the version that
was present on the source before the DTS process started.

???????

"Sue Hoegemeier" wrote:

Sue,

since my earlier message I've tried some further testing. I've inserted a
'rename' task between the 'transform data' and the 'ftp', but the rename
fails because the file is 'in use by another process'. That suggests to me
that although the 'transform data' process has supposedly completed, it
hasn't actually let go of the file by the time the next process starts up.
Further testing, by adding a pause in the interim process, still doesn't
resolve the problem.

Any thoughts?


"Phil Cook" wrote:

No...you'd get an error if that were the case. When testing,
make sure you don't have the Excel file open. Also, make
sure it's excluded from any anitvirus scans. You can use a
tool from sysinternals called FileMon to see what process is
accessing a file: www.sysinternals.com
I would move the file to another location after the FTP task
to ensure that you are creating a new file. You can use an
ActiveX script task and FileSystmeObject to do this. You can
find great examples of how to move, rename, copy, delete,
etc files using FSO at:
http://www.sqldts.com/default.aspx?292
If it's just a data pump and FTP, I'd consider just creating
a new package and starting clean as well.

-Sue

On Wed, 16 Feb 2005 06:55:05 -0800, Phil Cook
<PhilCook (AT) discussions (DOT) microsoft.com> wrote:

Sue,
I've started this package from scratch a number of times but the problem
remains.
I've also removed the Excel file from both the local source machine and the
FTP destination before running the package.
I've run FileMon against the process and captured the results. There is no
evidence of external programs opening the file.

The log file shows the following:-

The 'transform data' process runs;
the rename fails and the log shows a sharing violation;
the file length is 5632 at this stage;
the ftp process runs and copies the file across;
the file on the destination server is 5632 bytes;
the file on the source then continues to be written to until it reaches
810496 bytes;
THE END

I can supply the filemon.log if that would help, but it certainly appears
that the completion of the file is suspended while the subsequent process
tasks run. Once the FTP process has finished, the file writes resume.

Grrrr!

Thanks for listening.
phil.cook at barcap.com

"Sue Hoegemeier" wrote: