SSIS Script task to check if file exists in folder or not

Question

I want to check to see if a file exists in a particular folder from SSIS. How can I accomplish this?

You can use this as your starting point. http://stackoverflow.com/questions/7385251/how-to-check-if-a-file-exists-in-a-folder — Anoop Verma, Jul 10 '13 at 10:48
I'm using the below code Dts.Variables("FileExists").Value = File.Exists(Dts.Variables("FileLocation").Value) where FileExists is a boolean variable and "FileLocation" is a string variable with file path Though the file is present in the folder, still it is giving False value. — user1429135, Jul 10 '13 at 11:16
Just curious if the solution given below was useful to you and if you were able to resolve your issue. Your feedback will be helpful to me and also the future visitors who be having similar issue. Thanks. — Anoop Verma, Jul 15 '13 at 21:22

score 10 · Answer 1 · answered Jul 10 '13 at 14:49

10

Variables:

folder - string - C::\Temp\

file - string - 1.txt

fileExists - boolean - False

public void Main()
{
    string folder = Dts.Variables["User::folder"].Value.ToString();     //@"C:\temp\";
    string file = Dts.Variables["User::file"].Value.ToString();         //"a.txt";
    string fullPath = string.Format(@"{0}\{1}", folder, file);

    Dts.Variables["User::fileExists"].Value = File.Exists(fullPath);

    Dts.TaskResult = (int)ScriptResults.Success;
}

answered Jul 10 '13 at 14:49

Anoop Verma

1,495
14
19

1

I tried the answer above but I got an error when running the ssis in our environment.Exception has been thrown by the target invocation. – user2756589 Apr 08 '17 at 18:59
Please make sure that those variables are being passed to the script; folder and file as ReadOnly, fileExists as ReadWrite – Anoop Verma May 08 '17 at 19:59

Andrey Morozov · Answer 2 · 2017-05-12T09:16:52.280

6

You can use Foreach Loop Container and simply place all your items into it. It will be executed if file exists and won't if not. Very simple :)

edited May 12 '17 at 09:16

answered Apr 01 '15 at 13:47

Andrey Morozov

7,839
5
53
75

Very nice! I was looking for something simple and easy to implement and that works fine. – Vladimir Jan 25 '18 at 13:01

score 3 · Answer 3 · answered Oct 09 '14 at 14:34

As an alternative to having an "out" variable, you could also Change the Dts.TaskResult based on whether or not the file exists. The snippet below fails the script task if the file doesn't exist. (It also creates a log entry if logging is enabled.)

public void Main()
{
    string fileName = Dts.Variables["User::sourcePath"].Value.ToString() + Dts.Variables["User::fileName"].Value.ToString();

    if (File.Exists(fileName))
    {
        Dts.TaskResult = (int)ScriptResults.Success;
    } 
    else 
    {
        Dts.Log(string.Format("File {0} was not found.",fileName),0,null);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }

}

score 1 · Answer 4 · answered Jul 10 '13 at 12:26

There are no native tasks inside SSIS that can do this check but you can accomplish this using a Script Task but i suggest you check the following links for simple steps required to achieve that.

http://www.bidn.com/blogs/DevinKnight/ssis/76/does-file-exist-check-in-ssis

http://sqlmag.com/sql-server-integration-services/simple-effective-way-tell-whether-file-exists-using-ssis-package

SSIS Script task to check if file exists in folder or not

4 Answers4

Linked