Couple of weeks back I was working with a SSIS package development. My requirement is to check whether the file exists before I start importing the data. If it doesn’t exist then the package should update the db table. When I heard this requirement I thought I can make use of the “File System Task” in SSIS toolbox. When I start the work I came to know that you can’t get this piece of information from that task. It’s really weird for me as you can move\copy a file or folder using this task however checking the file existence is not possible with that task as shown below.
So I need to do some workaround to get rid of my requirement. There are couple of methods which I’ve tried and both worked like a charm. Below are the two ways you can attain the requirement
- Using “Script Task”
- Using “File Properties Task”, this is not a default task. This is developed in codeplex and it’s shared for free. You need to install it to add the user defined task in SSIS. You can download it from the link http://filepropertiestask.codeplex.com/
Let’s test both the ways
Using “Script Component Task”
In this method I’ll use script component task. I’ll use dot net scripting inside this script task to check the existence of the file. To use this method, drag and drop “Script Task” into the package. I’m declaring two variables (one as string and result variable as Boolean)to the package, first variable is to point the file and second one is to return the status. Use these two variables as shown below and click on Edit script button
*) Provide file path variable as readonly variable and provide another variable to get the result. In this example Filepath variable contains the file path and Result variable has the result of file existence
*) Now click on Edit script , you will be poped up with Visual studio editor. You can see from the image below I’ve added System.IO name space which is used to get file properties. Once you added the name space scroll down till you see Public main, there add the code below to check the file existence and return the value a variable
1 | Dts.Variables("Result").Value = File.Exists(Dts.Variables("FilePath").Value.ToString) |
*) After add the code click OK on script task. In my example I need to execute import task if the file exists or else it need to update SQL Server db table with details. Below is the package I developed, if you can see there is a function symbol displayed in the direction flow, that’s where I’m checking the file existence
*) Double click on the flow path and choose Evaluation Operation as “Expression and Constraint” and then in Expression provide the variable name where we get the file existence details. From the image you can see I’ve set it to true so that when file exists it will use that path.
That’s it all set. Now you have used script task to check file existence status.
I’ve faced the below error while using the script task.
Namespace or type specified in the Imports ‘Microsoft.SqlServer.Dts.Runtime’ doesn’t contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn’t use any aliases.
Script task uses “Imports Microsoft.SqlServer.Dts.Runtime” name space which is not getting resolved. When I checked I couldn’t find the dll “Microsoft.SqlServer.Dts.Design.dll” in the directory “C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\” which means I haven’t installed SDK. So to resolve this make sure you have installed “Client Tools SDK” during SQL Server installation as shown below
Using “File Properties Task”
As I said earlier this isn’t a default task. You need to install it, once it’s installed you can see it in the toolbox. Just drag and drop the task into the package and open the task.
You need to provide the source path. In this case I stored it in a variable so I pointed it to FilePath variable. Next you need to click on the drop down menu and select “Task always succeeds and records file existence in variable”, once you chosen you will be able to select the destination variable, In this example I’ve chosen the Result variable. Now do the same logic which we applied in the control flow which is just to add this expression. That’s all, all set. This method is pretty straight forward you don’t need to type your code instead you can directly get the result to a variable. I prefer this method however in some organizations they won’t allow us to use the open source references so in that case we can go for the first method.
In addition to this you can also accomplish this with the below tasks however I prefer to stay with these two methods.
- Foreach Loop Container Task– Just provide the filename explicitly in the path
- Execute SQL Task – You can use XP_FileExists to get the status.
I had a curiosity to check whether Microsoft has added this small piece of code in File system task on SQL Server 2012. Do you know this is not added in SQL Server 2012 Integration services, so you are left only with these methods 
. I don’t think MS will add it to the “File System Task”, there is a suggestion already raised in Microsoft Connect (http://connect.microsoft.com/SQLServer/feedback/details/231838/check-if-file-exists-operation-in-ssis-file-system-task) but they closed it.
. I don’t think MS will add it to the “File System Task”, there is a suggestion already raised in Microsoft Connect (http://connect.microsoft.com/SQLServer/feedback/details/231838/check-if-file-exists-operation-in-ssis-file-system-task) but they closed it.
