FileSystemObject is the way to go. Here is some sample code:

dim fso

dim fil

set fso = CREATEOBJECT("Scripting.FileSystemObject")

set fil = fso.GetFile(DTSGlobalVariables("gv_FileFullName").Value)

'check if file exists in archive location

if fso.FileExists(DTSGlobalVariables("gv_ArchiveLocation").Value...

Having used all of the DB products (including MySQL and PostgreSQL), I have to say that for both development and administrative productivity there is no match for SQL Server. ...