How do I verify the existence of a flat file in an SSIS package?

Question

I have a tab delimited file on a shared path. I've setup that flat file as a connection in an SSIS package. I'd like my package to verify the existence of the file before I try to do transformations and import it into a database table. I'm new at this, and I'm replacing a script that checks the existence of the file by hard-coding the path into a script (which I'd like to avoid).

Is there a way to reference the path from the connection from within a script or some other method I'm unaware of?

Answer 1

Try this, I think it goes where you want.

Notice the assignment of Path to a variable within SSIS.

http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/

Here's another (maybe more elegant) solution..

http://blogs.pragmaticworks.com/devin%5Fknight/2009/08/does-file-exist-check-in-ssis.html

Answer 2

I ended up using a combination from the articles madcolor pointed out:

Public Sub Main()

  Dts.TaskResult = Dts.Results.Success

  Dim myFlatFileCM As ConnectionManager = Dts.Connections("MyFlatFile")

  If Not File.Exists(myFlatFileCM.ConnectionString) Then
    Dts.TaskResult = Dts.Results.Failure
  End If

End Sub

The "MyFlatFile" was the name of the connection manager. Note, this was done with SQL Server 2005.