So in this blog I am going to show you how you can write a quick Script Task inside of SSIS to see if a file exists. In my next blog in this two part series I will show you how you can then loop over this script task until the file does exist.

You can find Part 2 in this series here:

Business Problem:

So you know that you are going to receive a file that you need to process, and you want to process and load that file the moment it “exists”. If you run your package before the file exists the package will fail, so you want to first check to see if the file exist and then run your data flow task that will subsequently load the file.

Walkthrough

Open up your Visual Studios environment and create a new package.

1. Pull the Script Task component into the data flow.
2. First let’s create two variables, we will use these in our script task.
3. 
   1. The value for the strFileName variable is the location of the file, please change yours as needed.
4. Open the script task and make sure the Script Language is set to Visual Basic.
5. Select “strFileName” for the read only variables.
6. Select ‘”bolDoesFileExist” for read/write variables.
7. 
8. Click “Edit Script…” to open visual studios.
9. Ok. Inside the VB project we first need to add a new class of code.
10. In the Imports section add the following code: Imports System.IO
11. 
12. Now we enter our VB code. Find the section titled “Public Sub Main” and type the following code where it says ‘Add Code Here’.
13. Dts.Variables(“bolDoesFileExist”).Value = File.Exists(Dts.Variables(“strFileName”).Value)
14. Here is a screenshot of the final result:
15. 
16. Now simply click the x at the top right to close the visual studio project to close it out, you don’t have to click save.
17. Back in the script task editor main screen click ok to compile the code. All Done!

Explanation of Visual Basic Code

The code inside the script task is in two parts.

• The first part is the function “File.Exists”. The file exists function is reading the value of the strFileName variable and checking to see if that file exists. It will return a value of either True or False.
• The second part is writing the result of the File Exists function to the variable bolDoesFileExist. So everytime this package runs the value of that variable will change based on whether that file does or does not exist.

Thanks for looking and I hope this helps. In my next post I will explain how you can loop over this task over and over again until the file does exist.