How do we know when a file is finished uploading?

The client will upload 2 files at any time during the day. We have an SSIS package than runs every 15 minutes. The first step within the SSIS package calls a .bat file to determine if the files are present. If the file is present the SSIS package will continue processing.

What we want to do is to determine that not only that the files exist, but that the upload is complete. Is there any way MS DOS can determine this, or can something be done in the SSIS package to determine that the upload is complete?
thirdwavellcAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
The files are uploaded to ? A database table?
In SSIS you can create a number of indicators to let you know the process is complete. You could even rename the file using a dos command through the execute process task in Control Flow. What do you want?
0
Nathan RileyFounderCommented:
Sure, use the send mail task.  Then drag the on completion line (precedence constraint) over to it.
0
Nathan RileyFounderCommented:
the above is in SSIS
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

thirdwavellcAuthor Commented:
Simply put, the client is uploading two files.

Can a DOS batch script determine if these files have finished uploading?
OR
Can a task in SSIS determine if these files have finished uploading?

The files are uploaded to a folder on the server, not a database.
0
Steve HoggITCommented:
You need to determine if the client is still uploading the file, not in the middle and you grab them before they are complete.
Can the client wirte an indicator file that you look for to know they are done?
0
Nathan RileyFounderCommented:
Yeah that's fine, the last step in the SSIS package should be the FTP process.  In visual studio on the left choose the send mail task and put in all your information.  After that drag the green line from ftp task to the send mail task.  That way whenever the ftp completes it will pump out an e-mail to whoever you tell it to.
0
thirdwavellcAuthor Commented:
Gallitin,

We are not FTPing the file in the SSIS package. The client is FTPing the files to a folder on the server, then we run the SSIS package.

But we only want the SSIS package to run if the files have finished uploading.

0
thirdwavellcAuthor Commented:
HoggZilla,

I have asked our client to see if he could upload an additional text file after the other two files have been uploaded as an indicator. This is a good idea, although it does not directly answer the question posted.

I'll reply to this posting with the answer from the client when I hear back from him.
0
Nathan RileyFounderCommented:
thirdwavellc, sorry I didn't see that before.  Why not have the SSIS package download the file then download the file again and if the file sizes are not = to each other then loop it to download again otherwise it's finished.
0
thirdwavellcAuthor Commented:
Gallitin,

Sorry, that's not what I'm looking for. If the file is 100+ MB, this will eat up to much processing power.
0
Nathan RileyFounderCommented:
In your current SSIS package why not call for the file size then delay 5-10 seconds then call for it again and compare the two values.  If they = each other then continue and download otherwise stop.  That way you wouldn't have to download the files.
0
thirdwavellcAuthor Commented:
Gallitin,

This is the kind of solution I'm looking for. Can this happen in a DOS command? If so do you have any code? OR can this be done in SSIS and if so do you have any samples I can see?
0
Nathan RileyFounderCommented:
I would assume it could be done in DOS, but why would you want to when you already have a SSIS package.  I'm assuming your downloading the files thru a Script Task in that package?  If so I would just add the logic in there.
0
nmcdermaidCommented:
I think the indicator file would be the most reliable. Checking the size is OK but what happens if the upload is interrupted for say 30 seconds, then continues. You might mistake that its complete when its not.
Assuming the client is FTPing then maybe there is something you can hook into on the FTP server side (your side) to indicate that the file is complete. i.e maybe the FTP server has an expected file size or a checksum.
This page:
http://en.wikipedia.org/wiki/File_Transfer_Protocol
says
No integrity check on the receiver side. If a transfer is interrupted, the receiver has no way to know if the received file is complete or not
Which implies that no, there is no way to check when a file is complete. But its worth reading your FTP server documentation.
0
Bill BachPresident and Btrieve GuruCommented:
If the file is still uploading, a DOS command RENAME will fail to rename the file.

As such, you can probably make SSIS call the REN command and simply check the return code.  If 0, the rename was successful.  If you gate back a 1, then it failed.  Alternatively, you could create a batch file to sleep pending the file's completion (with a small "sleep" loop), but I think an upload of a large file might have an impact on your process.  

To check it in a batch file (your original question), use this:
   REN filename  newfilename
   IF %ERRORLEVEL% == 0 ...

Using pure DOS, you're limited in what you can do to GOTO statements and the like, but I suspect that you're on a Win32 platform, so you can use the more powerful scripting function to put other stuff in place of the "..."
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve HoggITCommented:
That DOS RENAME is a great answer. Way to go BillBach!!! At least it is something in DOS.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.