A very common need in SSIS is to check to see if a file exist before you run what could be a very long process in your package. There are no native tasks inside SSIS that can do this check but you can accomplish this using a Script Task. Here are the steps to check to see if a file exist.
1. Setup two variables. The variable strFileLocation has a string data type with the value being the location of the file I want to check for. The variable bolFileExists has a boolean data type with the value changing based on whether the file exist or not. If the file is found the value will be changed to True otherwise it stays False.
2. Use a Script Task in the Control Flow and set the ReadOnlyVariables to use the strFileLocation variable and the ReadWriteVariables to use the bolFileExists variable then select Edit Script.
3. There are two methods for writing this script. The first method you must first add the namespace System.IO. The second method does not require this.
4. Scroll down the editor until you find the green commented out text that says Add your code here. Replace that with the following code then save and close the editor :
Dts.Variables("bolFileExists").Value = File.Exists(Dts.Variables("strFileLocation").Value)
The second option for the script is (Remember this option is not using the Imports System.IO step) :
Dts.Variables("bolFileExists").Value = My.Computer.FileSystem.FileExists(Dts.Variables("strFileLocation").Value)
5. Now that you have the script done you can use things more familiar to you in SSIS like precedence constraints with expressions to get the desired results. Connect the completed Script Task to a path that you want the package to move if the file does exist. Open the precedence constraints editor by double clicking on the line. change the Evaluation operation to Expression and Constraint and add the expression @bolFileExists==True. Remember the double equals is a comparison. If it was a single equal sign it would be trying to set the value of the variable to True. Click OK once complete. If you want a path for if the file is not found then follow the same steps with the precedence constraint but the expression should be @bolFileExists==False.
I’m using script tasks just as placeholders here for how the rest of the package may look. If my file did exist it would go down the left path. If it did not exist my package flow would go right.
Any suggestions as what to do when you don't know the exact file name? For example if you were looking for files created by a SQL maintenance plan and they looked something like:
What's the best way check existence for M2Mdata01*.bak ?
Also, how would one check to see if the date of the file was within a certain range?
I know you can do wildcards like you've shown but i'm not sure how to check a date range in VB. I'll do a little looking.
I brought your book and think having a video is the best idea in terms of teaching. One comment thoough, you should really make the screen larger. Been pulling my hair out trying to see what it is youre typing.
But apart from that,,why am I getting different results to you when I done exactly the same thing? Everything is right except even when the file is not found, it goes to the scrip task that says found. Any ideas?
That's strange Sam. It's kind of hard to tell without seeing the package. Feel free to send me screenshots of it.
Can I email it to you and if so, what's your email? Sorry, I am new to this site and also wasnt able to paste it here.
Just another question if you dont mind..do you know where I can download AdventureWorks2008?
Up until now, I've been using AdventureWorks in place of the 2008 version which worked fine if I changed the connection in your packagees but Chapter 19 I think refers to a column in a table which is not in the earlier edition.
I've been to numerous sites and have downloaded several packages but on each launch, it doesnt give me the option of installing AdventureWorks2008 (it has everything else-so I guess its not related to the setup on my pc).
thanks in adance
Sure go ahead and email it to me. May take a couple days for me to review right now. firstname.lastname@example.org
The AdventureWorks databases are on http://msftdbprodsamples.codeplex.com/. You need to enable fulltext search on your db instance to get the AdventureWorks2008 database.
Hi Devin,I just noticed you had another 24 hour trainer book ((MS Business Intelligence) and wanted to buy it straight away from Amazon (soft copy for iPad). Only question is where do I go afterwards for the video? (which is the main reason for me buying it)Please let us know otherwise I'll need to buy the hard copy which I dont want to do because of cost and because I need to wait a couple of weeks (am in Australia) thanksSam
Good question Sam. I'm not sure how the videos are provided when purchasing the soft copy. I'll see if I can find out from the publisher.
Devin, if you need to use Script Task in SSIS (irrespective of language - VB or C#), do we've to have Visual Studio installed on the server where you host the SSIS package (production box), for the script portion to work?
You do not need Visual Studio installed on the server.
Thank you Devin. One more thing, your steps above hold good if we're looking for a particular file name, right? I have a scenario where we need to scan a folder for multiple CSV files (different file names - but all are CSV files) coming in from a vendor. Sometimes a single file is dropped & sometimes four! Irrespective of the number of files present, the load needs to kick off. I tried your method, by adding a wildcard (*.csv) and seems SSIS won't accept it, and it treats it as if no file exists, and I get email saying "No Files to Process" from the load. Please advise.
I found this example someone had written. I think the variables names are a little different but uses the same idea.
Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.IOPublic Class ScriptMainPublic Sub Main()Dim di As DirectoryInfo = New DirectoryInfo("c:\")Dim fi As FileInfo() = di.GetFiles("*.txt")If fi.Length > 0 ThenDts.Variables("User::FileExists").Value = TrueElseDts.Variables("User::FileExists").Value = FalseEnd IfDts.TaskResult = Dts.Results.SuccessEnd SubEnd Class
Thank you Devin. But, that requires installation of Imports System.IO anmespoace and all, right? Basically I did not want to use the "Script Task". One of the blogs recommended using the "For Each Loop" container instead. I was surprised - was easier than I thought, for my situation just used the For Each Loop container, an Execute SQL Task and two variables - mission accomplished! Ha...so it seems there're several ways/options to do a particular task in SSIS!
Great glad you figured it out!
Great article, thank you very much :)
I have one question (and I am very new to SSIS), using your example, how can I check for multiple specific file names in the same directory?
For example, we have a flat file directory that contains many multiple files with different names. This particular package only needs to process/import File1.txt, File2.txt and File3.txt.
I'm guessing I will need to create multiple variables, one for each respective file name. But not sure how the code is written withen the script editor?
Many thanks in advance.
For example, we have a flat file directory that contains many multiple flat files with different names. This particular package only needs to process/import File1.txt, File2.txt and File3.txt.
You can either place the task in a foreach loop to capture each file name or use ("*.txt") for the file name