bigator

Check if file exists: using SSIS

Hi All,
Many times, we have to do this simple checking “if file exists/available .. then proceed” in SSIS operations. And Microsoft did not put any facilities to achieve this task in SSIS with default components.

People also have raised this concern to Microsoft, but Microsoft explains that they have more priority works to deal with so this feature is not coming soon. you can check complete story here.

Follow these steps:
1. Create a Boolean variable (I call it a Flag_Variable). Assign default value to “False“.

2. Use “Script Task” to check if file exists in given location. And Set Boolean variable’s value for later use.
(I have used C# for demo.)

Add this namespace:
using System.IO;

Write this code to your Main() method:
System.IO.FileInfo myFile = new FileInfo(@”C:\InputDataFile.csv”);
Dts.Variables[“User::Flag_Variable”].Value = myFile.Exists;

Save and close the script window.

Note: you can parameterize the InputDataFile in script task like following.
System.IO.FileInfo myFile = new FileInfo(Dts.Variables[“User::InputDataFilePath”].Value.ToString());

3. Use “Precedence Constraint Editor” and choose “Expression and Constraint” value for Evaluation operation. Provide Use following expression and test it.

Expression: 
@[User::Flag_Variable] == True

Here, “Flag_Variable” is your Boolean variable.

Note: If “Flag” variable is of different type, e.g. string then expression expects “True”. So it varies based on the type of value you are comparing.

Choose “Logical AND. All constraints must evaluate to True” option and click OK.

That’s it. now you can connect to any controls task you want to proceed with on the success/True (File Exists) value. And finally, it should look like this 🙂

I hope this will help you somewhere.
Thanks, Khilit

bigatorMay 10, 2012 · bigatorKhilitchandra Prajapati · bigator9 Comments
bigatorTags: , , , , ,  · Posted in: Business Intelligence, Interview QA, Quick Tips, SQL Server, SSIS, Tutorial

9 Responses

  1. Office 2010 Product Key - May 10, 2012

    Very great post. I just stumbled upon your blog and wished to say that I have truly loved surfing around your weblog posts. In any case I will be subscribing for your rss feed and I am hoping you write again soon!

  2. True Religion Jeans Men - May 29, 2012

    I’m sure this really the most important knowledge personally. At this point relieved looking at your personal write-up. Nevertheless would you like to comments about many typical facts, The web site personal taste is ideal, the particular reports is within simple fact excellent. Beneficial approach, Kind regards!

  3. Timon Ron - June 15, 2012

    Wow, thanks for this post.
    Ron

  4. Sai - August 13, 2012

    I need to verify if a file exists and following the above procedure and having issues running the package

    Variable FileExists is boolean set to False created at Package level
    and have code similar to one above in Main() and constraint is set to “@[User::FileExists]==True”

    ****Script code
    System.IO.FileInfo myFile = new FileInfo(Dts.Variables[“User::SAPFileLocation”].Value.ToString());
    Dts.Variables[“User::FileExists”].Value = myFile.Exists;
    Dts.TaskResult = (int)ScriptResults.Success;
    **** ScriptCode

    I see the following error messages in package doesn’t execute.

    Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

    Error: The type of the value being assigned to variable “User::FileExists” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

    Any thoughts what i need to do ?

  5. Khilitchandra Prajapati - August 13, 2012

    Hi Sai,
    Yes, The method seems to be correct. What i can see is that, you are directly assigning the value of “myFile.Exists” to the SSIS variable and thats what could be causing an issue. What version of SSIS (BIDS) are you using. I have created this demo in SQL 2012. i will check this today when i reach home.

    meanwhile, you can try this solution:

    ****Script code
    // Set Your File’s Path Here
    string sourceFilePath = Dts.Variables[“PATH_SRC_FILE”].Value.ToString();

    // Check if your file(s) is available
    string[] filesFound = Directory.GetFiles(sourceFilePath, fileFormat, SearchOption.TopDirectoryOnly);

    // If Found then set the value, True
    if (filesFound.Length > 0)
    {
    Dts.Variables[“User::FileExists”].Value = true;
    }

    Dts.TaskResult = (int)ScriptResults.Success;
    ****Script code

    Please let me know if you still face any issues on this.
    Thanks, Khilit

  6. Sai - August 15, 2012

    I am using BIDS 2008 and it still doesn’t work. I dont think its an issue with the script , since i removed everything within main() and it still shows the same error.

  7. AArvj - January 25, 2013

    Thanks a lot..it helped me 🙂

  8. aarvj - January 30, 2013

    if I need to check for the file with file mask.for example Test_MMDDYYYY.csv, then in this case how can I write the C# code using the same mentioned above

  9. aarvj - January 30, 2013

    I am having file declared as variable having mask Pay_MMDDYYYY.But my code is picking only that file which i gave in variable.It doesn’t pick file having any other datetimestamp other than this.how to pick the files having different mask.need to write it in C#

Leave a Reply


%d bloggers like this:
bigator