[Webinar] Learn how to a build a cloud-first strategyRegister Now


Powershell Script Help

Posted on 2012-09-15
Medium Priority
Last Modified: 2012-10-14

I have the following powershell script which executes a command when a new file is created / uploaded / moved to a specified location. The command executes a SSIS package which needs the entire file contents to be present before running. My issue is, I do not want it to execute the command until the move / upload is completely finished. Sometimes large files get uploaded (5gb) and if the powershell script executes the command before the entire file move / upload is complete, the SSIS package will fail.

How can I modify this powershell script to wait until the entire file has been uploaded / moved?

$folder = 'W:\Uploads'
$filter = '*.*'                             # <-- set this according to your requirements
$cmd = 'dtexec.exe /FILE W:SSIS\Import.dtsx'
$fsw = New-Object IO.FileSystemWatcher $folder, $filter -Property @{
 IncludeSubdirectories = $false             # <-- set this according to your requirements
 NotifyFilter = [IO.NotifyFilters]'FileName, LastWrite'
$onCreated = Register-ObjectEvent $fsw Created -SourceIdentifier FileCreated -Action {
 invoke-expression -command $cmd

Open in new window

Question by:brandonc7

Author Comment

ID: 38402233
Modified original question

Author Comment

ID: 38402251
Maybe a better way to say this is to make sure the file is not 'locked' by another application?

Accepted Solution

namethis earned 501 total points
ID: 38402332
You assume that the file will always get uploaded completely at some time. But this may not be the case. The file may be uploaded partially even if another application stopped writing to it.
Or it may be damaged. You need to check file integrity in some way.
One way is to get the file archived before upload, and check archive integrity after upload.
Another way is to get md5- or sha hash along with the file.
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

LVL 81

Expert Comment

ID: 38402358
One way is to look for a change file that was modified a certain time before, I.e. look at the modify date on the file to be five minutes ago this way you will be certain that no process is still writing to it.
You do not want to act on created.

Author Comment

ID: 38402359
Hmmmmm good point. The uploads are being done via FTP so I am limited because up to client as far as what they send. My ssis script is smart enough to catch a damaged file I think. That being said, is it possible to just modify this power shell script to detect lock / unlock status?
LVL 40

Expert Comment

ID: 38402720
LVL 71

Assisted Solution

Qlemo earned 501 total points
ID: 38403112
AFAIK FTP does not lock a file while transferring.
That is the reason we usually take an approach of copying the file to a different file name or folder, and after finishing let the FTP script perform a local rename/move to the target name. Another approach is to create a "lock file" before starting the transfer, and deleting it when finished. Of course that requires that you have control over the FTP script, which seems not to be an option here.

Sadly, FSW does not support a scanning interval, delayed triggering or triggering on no change for some time ... So the only reliable way IMO is to have a scheduled task (or looping script) checking for files older than e.g. 2 seconds. Or wait in the FSW event for at least 1 second, and recheck if the LastWrite date has changed.
LVL 16

Assisted Solution

AlexPace earned 498 total points
ID: 38406565
You could use a scriptable FTP server that can run a script when an upload is complete.  Here is a Robo-FTP Server event script that automatically moves uploaded files to another folder: http://kb.robo-ftp.com/script_library/show/46 

If you had something like that, your powershell script could monitor the destination folder instead of the FTP upload folder.... or you could just make a Robo-FTP Server event script launch the powershell script directly and pass in the name of the uploaded file.

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question