SSIS package - how to determine if a file is in use.

[Everett Music]

I have several SSIS packages that import data from files. I am having
trouble reliably determining whether a file is in use. I am working in
Visual Studio 2005. I am creating SSIS packages for SQL2005. I am importing
data from text files. These text files are written to network directories
from local, remote (WAN) and dialup sources. I need to be able to determine
if a file is still being written so that I don't try and import only part of
a file.

[Aeterna]

Several options spring to mind.

One would be to monitor the modified datetime of the files and start the
SSIS packages when a certain amount of time has elapsed since the last
update.

Another would be to add a separate termination file for each of the files
which is produced, so that you only run a SSIS package on files where a
termination file is present.

Would either of these options work for you?

Post by Everett Music
I have several SSIS packages that import data from files. I am having
trouble reliably determining whether a file is in use. I am working in
Visual Studio 2005. I am creating SSIS packages for SQL2005. I am importing
data from text files. These text files are written to network directories
from local, remote (WAN) and dialup sources. I need to be able to determine
if a file is still being written so that I don't try and import only part of
a file.

[Everett Music]

Unfortunately neither of these options will work for me.
As for monitoring the timestamp of the file and waiting for a specific
amount of time to pass before trying to read the file; Several of the files I
am interfacing with are uploaded via dialup and the timestamp is not
modified, so the file could have been created yesterday and uploaded today
with yesterdays timestamp.
As for creating a seperate termination file for each file; that would
require all processes that I receive files from to be re-written to generate
a termination file.
I currently test the availability of the file by trying to open the file
in 'non sharing' mode. This works most of the time, but I am still plagued
by some of the files not being locked while they are being written to. The
most grevious offender appears to be java.

Post by Aeterna
Several options spring to mind.
One would be to monitor the modified datetime of the files and start the
SSIS packages when a certain amount of time has elapsed since the last
update.
Another would be to add a separate termination file for each of the files
which is produced, so that you only run a SSIS package on files where a
termination file is present.
Would either of these options work for you?

Post by Everett Music
I have several SSIS packages that import data from files. I am having
trouble reliably determining whether a file is in use. I am working in
Visual Studio 2005. I am creating SSIS packages for SQL2005. I am importing
data from text files. These text files are written to network directories
from local, remote (WAN) and dialup sources. I need to be able to determine
if a file is still being written so that I don't try and import only part of
a file.

[Todd C]

I know there is no native 'Task' in SSIS to make this determination, but can
you use a Script task to poke at the file to see if it's fully baked?

In the script task you can leverage all of the System.IO library. I'm no
expert in this area and only guessing here, but it is where I would start.

HTH
--
Todd C
MCTS SQL Server 2005

Post by Everett Music
Unfortunately neither of these options will work for me.
As for monitoring the timestamp of the file and waiting for a specific
amount of time to pass before trying to read the file; Several of the files I
am interfacing with are uploaded via dialup and the timestamp is not
modified, so the file could have been created yesterday and uploaded today
with yesterdays timestamp.
As for creating a seperate termination file for each file; that would
require all processes that I receive files from to be re-written to generate
a termination file.
I currently test the availability of the file by trying to open the file
in 'non sharing' mode. This works most of the time, but I am still plagued
by some of the files not being locked while they are being written to. The
most grevious offender appears to be java.

Post by Aeterna
Several options spring to mind.
One would be to monitor the modified datetime of the files and start the
SSIS packages when a certain amount of time has elapsed since the last
update.
Another would be to add a separate termination file for each of the files
which is produced, so that you only run a SSIS package on files where a
termination file is present.
Would either of these options work for you?

Post by Everett Music
I have several SSIS packages that import data from files. I am having
trouble reliably determining whether a file is in use. I am working in
Visual Studio 2005. I am creating SSIS packages for SQL2005. I am importing
data from text files. These text files are written to network directories
from local, remote (WAN) and dialup sources. I need to be able to determine
if a file is still being written so that I don't try and import only part of
a file.