SSIS – Checking if a file exists

I’ve found that SSIS will throw an exception if the CSV file I want to load is missing. While I do have a script that checks all my connections, this doesn’t seem to do enough for text file data sources and lets them pass.

The solution I’ve found is using Powershell to test for the file and return the result to SSIS.

check_file_exists.ps1

param (
 [string]$filename,
 [string]$extension,
 [string]$filepath
 )

if (test-path ($filepath + $filename + $extension))
 {
 # True
 return $True
 }
else 
 {
 # False
 return $False
 }

I needed to add a Variable in SSIS:

Variable Name: CSVSourceExists

Variable Type: String

This  script is called from SSIS from an Execute Process Task node using:

Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Arguments: -ExecutionPolicy Unrestricted -File \\sqlapp02\Scripts\check_file_exists.ps1 -filename stcd -extension .csv -filepath \\SQLApp02\DataDump_DEV\MME\

Working Directory: \\sqlapp02\Scripts

Standard Output Variable: User::CSVSourceExists

Note the Arguments field is dynamic based on the expression:

"-ExecutionPolicy Unrestricted -File "

+ @[$Project::Script_Source]
+ "\\"
+ "check_file_exists.ps1"
+ " -filename "

+ @[User::CSVName]

+ " -extension .csv"
+ " -filepath "

+ @[$Project::CSV_Dump_Source] + "\\"

I then use Expression precadents to test the result using

@[User::CSVSourceExists] == "False\r\n"

and

@[User::CSVSourceExists] == "True\r\n"

I really wish the ‘\r\n’ weren’t there, but hey it’s working and time to move on!