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!
0 Comments
You can be the first one to leave a comment.