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
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!
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!
Wow, thanks for this post.
Ron
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 ?
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
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.
Thanks a lot..it helped me 🙂
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
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#