As part of a recent project, I needed to check if a file existed before starting an SSIS package to import that file. The catch was that I did not know what time time file was going to be placed on the file system. If the SSIS package runs and the file does not exist yet, the package will fail.
You can create a script task component within SSIS to check for a file and then sleep, however several sources said that this could spike the processor so I deceded to go a different route. To solve the problem, I wrote a quick little powershell script to check if the file exists and then wait in a loop before starting the SSIS package.
I created a SQL agent job with 2 steps. The job runs daily at 1:00 AM. The job has a status of Executing on Step 1 until the file exists.
Step 1: powershell script to check for the file (see below)
Step 2: Execute SSIS package task.
The file that I am looking for in this example is: C:\test\ImportFolder\fileToCheck_20130212.txt
You will notice that today’s date is also appended to the end of the file name. I built the file name out dynamically along with the current date stamp.
$dt = Get-Date -format yyyyMMdd
$path = 'C:\test\ImporFolder\'
$theFile = $path + 'fileToCheck_' + $dt +'.txt'
# $theFile variable will contain: C:\test\ImportFolder\fileToCheck_20130212.txt
While (1 -eq 1) {
IF (Test-Path $theFile) {
#file exists. break loop
break
}
#sleep for 60 seconds, then check again
Start-Sleep -s 60
}
This post is part of the blogging phenomenon known as TSQL Tuesday. This month’s blog party is hosted by Wayne Sheffield, who is writing a series of blog posts in the month of February all about powershell..
