Check If File Exists in Folder or Not (Ssis, Sdt 2015 and Sql 2016)
I Need to Load Multiple Files into a Sql Table. I Have Three Different Formats, Csv, Xlsx and Xls, but I Will Load One Type of Files at a Time, No Combination...
I need to load multiple files into a SQL table. I have three different formats, CSV, XLSX and XLS, but I will load one type of files at a time, no combination of different file types.
First, I will check in my source folder if the file exists or not. If it exists, load it to table and move to next file. The file checking and selection process must be dynamic. I don't want to hard code the file name in a variable. When the package starts, I need the filename variable to hold the very first file present in the folder each time when I run the package.
If the file is not found in the folder, send mail alert that file doesn't exist.
For example, if I have three files in my folder,
File1.csv
File2.csv
File3.csv
I run my package and I hard code the file name variable = File1.csv in the first run. My package gets executed successfully and loads the file1 to the table and archives it to the archive folder.
When next time for each loop runs, it looks for variable filename = File1, but it's already archived, so my package will not able to process file2 from the folder. How can I do this properly?
3 Answers
First use a script task to check if any file is present in the folder or not.Create an integer variable and set it to 1, if file is found and to 0 if file not found. Use expression and constraint in the precedence constraint. If evaluation expression is false,connect it to a mail task,else connect to a for each loop task. Please refer: or more details.
A trick in SSIS is to create a Foreach Loop.
- Create a Variable containing the path where the file resides. Example User::filepath
Create another variable to save the actual file found on the Foreach loop, example User::filename
Add the Foreach Loop from the SSIS Toolbox.
On the Foreach Loop editor, on Collection left tab select in Enumerator - Foreach File Enumerator
Click Expressions and add one for Property Directory and select in the expression the @[User::filepath]
Enter on the Folder the value of the filepath variable (this will be overridden at execution from the Expression above, this is if you filepath is changed at runtime)
On file enter something like . or *.txt or whatever filter you want to apply to the folder.
On the Variable Mappings left tab select on the Variable column the User::filename variable, the Index column will default to 0.
Then put your logic inside the Foreach Loop box.
This will only execute your logic if a file is found. You can also use this to process multiple files if they exist on the folder.
Hope this helps.
The problem with Foreach Loop Container is it will not process any task within the container but will continue outside of it.
A solution is an expression on the constraint to the next task.
It will check the variable length used in the Foreach Loop Container. If a file isn't present, the expression will return 0 and FALSE.
LEN(@[User::filePath]) > 0 ? TRUE:FALSE
filePath = Foreach Lop Container variable
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
