File Exists Check in SSIS

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.

file_exists_ssis_1

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

file_exists_ssis_2

*) 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

Dts.Variables("Result").Value = File.Exists(Dts.Variables("FilePath").Value.ToString)

file_exists_ssis_3

*) 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

file_exists_ssis_5

*) 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.

file_exists_ssis_4

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

file_exists_ssis_6

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.

file_exists_ssis_7

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 Smile . 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.


Posted

in

by

Comments

11 responses to “File Exists Check in SSIS”

  1. ajssis avatar
    ajssis

    You are creating a lot of extra steps that are not required in the Script task. You fail to see the line right after your code that is
    Dts.TaskResult = (int)ScriptResults.Success;

    The opposite of this is
    Dts.TaskResult = (int)ScriptResults.Failure;

    The code you should be using is
    // Check if the file exists
    if (File.Exists((string)(Dts.Variables[“FilePath”].Value)))
    {
    Dts.TaskResult = (int)ScriptResults.Success;
    }
    else
    {
    Dts.TaskResult = (int)ScriptResults.Failure;
    }

    If the file exists then the Script task result will be “Success”. If the file does not exist the Script task results will be fail.

    Then all you do is set the Success direction flow (the green line) to go to your data flow task. If the result is Failure then the Failure direction flow (the red line) will go to your Update error task. Be sure to RIGHT-CLICK the direction flow and select “Failure” from the pop-up menu.

    1. admin avatar
      admin

      @aj — Both the scripts are same. In my example I’m passing the output value to a variable. In your example you aren’t passing it instead you make the task failure or success.

    2. Bhaskar avatar
      Bhaskar

      I think you are correct in someway but the problem would be that the package execution will result with errors.

      1. VidhyaSagar avatar

        @Bhaskar – It shouldn’t thrown an error. Im using this in the existing SSIS packages and it runs fine. What error are you facing?

  2. Ray avatar
    Ray

    This is a good example. There are many times I need a script to test for a condition but I don’t want it to throw an error if the condition fails.

    While I agree that the code is a bit more simplistic with a Succeed/Fail on exit, it does add more code on the process to handle that failure. I’m all for a clean exit regardless.

    1. VidhyaSagar avatar

      Thanks Ray for the comment. Yes its just a simple example to show folks how to handle the situation

  3. Albert avatar
    Albert

    I have tried the example above…But it seems that The data flow tasks is still started even the Result variable is false (file not exists).
    one major different is that I haven’t set the path for file not exists case. Should it be also set so that the whole thing works?

  4. dotnetsqlCoder avatar

    i use a for each container to run tasks only when there are files in a certain directory .

  5. Dan avatar
    Dan

    Hello –

    I have the latest version of VS… I get this error when attempting to use your code:

    Severity Code Description Project File Line Suppression State
    Error CS1955 Non-invocable member ‘ScriptObjectModel.Variables’ cannot be used like a method. ST_b339575eb9cc4e17a5776c8ec2723678 C:\Users\M88576\AppData\Local\Temp\Vsta\SSIS_ST140\VstacHwYZ8jT00GB0MgnuL6Wyg\Vsta9l0uXEBIZkO2A6OG94AMcQ\ScriptMain.cs 97 Active

    It doesn’t seem to like “Variables” – any ideas?

  6. namita avatar
    namita

    Hey Sagar,
    The above approach is not working for checking multiple file exits 🙁
    I need to doit without using Script Task

    1. VidhyaSagar avatar

      Sorry Namita for delayed response. It’s been a while I worked on this and the steps I provided is for older version

Leave a Reply

Your email address will not be published. Required fields are marked *