March 1, 2017 at 10:44 am
I've been using Script Task to check for the existence of File(.xls) in a folder(directory). I've written C#Code by declaring three variables.
FileName, FilePath, FileExistsFlag.
Readonly variables are FileName & FilePath
Readwrite Variables are Fileexistsflag
This is the C# Code I'm using :
public void Main()
{
// TODO: Add your code here
String FilePath = Dts.Variables["User::FolderPath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
if (
File.Exists(FilePath))
{
Dts.Variables["User::FileExistsFlag"].Value = 0;
}
MessageBox.Show(FilePath); //Show the folder path with File Name
MessageBox.Show(Dts.Variables["User::FileExistsFlag"].Value.ToString()); //Show the flag value, 1 for exists and 0 for not exists.
Dts.TaskResult = (int)ScriptResults.Success;
}
Assigning FileExistsFlag Variable to 0 with Int32 Data Type.
When I run the script task messagebox pops up with Filepath and Filename correctly but then fileexists flag says 0.
My task is when file is there in folder go one path otherwise go other path. In my case its going to only one path.
March 1, 2017 at 10:50 am
wweraw25 - Wednesday, March 1, 2017 10:44 AMI've been using Script Task to check for the existence of File(.xls) in a folder(directory). I've written C#Code by declaring three variables.FileName, FilePath, FileExistsFlag.
Readonly variables are FileName & FilePath
Readwrite Variables are Fileexistsflag
This is the C# Code I'm using :public void Main()
{
// TODO: Add your code here
String FilePath = Dts.Variables["User::FolderPath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
if (
File.Exists(FilePath))
{
Dts.Variables["User::FileExistsFlag"].Value = 0;
}MessageBox.Show(FilePath); //Show the folder path with File Name
MessageBox.Show(Dts.Variables["User::FileExistsFlag"].Value.ToString()); //Show the flag value, 1 for exists and 0 for not exists.
Dts.TaskResult = (int)ScriptResults.Success;
}
Assigning FileExistsFlag Variable to 0 with Int32 Data Type.When I run the script task messagebox pops up with Filepath and Filename correctly but then fileexists flag says 0.
My task is when file is there in folder go one path otherwise go other path. In my case its going to only one path.
I don't see anywhere in that code where you are setting it to 1.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 1, 2017 at 11:04 am
Phil Parkin - Wednesday, March 1, 2017 10:50 AMwweraw25 - Wednesday, March 1, 2017 10:44 AMI've been using Script Task to check for the existence of File(.xls) in a folder(directory). I've written C#Code by declaring three variables.FileName, FilePath, FileExistsFlag.
Readonly variables are FileName & FilePath
Readwrite Variables are Fileexistsflag
This is the C# Code I'm using :public void Main()
{
// TODO: Add your code here
String FilePath = Dts.Variables["User::FolderPath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
if (
File.Exists(FilePath))
{
Dts.Variables["User::FileExistsFlag"].Value = 0;
}MessageBox.Show(FilePath); //Show the folder path with File Name
MessageBox.Show(Dts.Variables["User::FileExistsFlag"].Value.ToString()); //Show the flag value, 1 for exists and 0 for not exists.
Dts.TaskResult = (int)ScriptResults.Success;
}
Assigning FileExistsFlag Variable to 0 with Int32 Data Type.When I run the script task messagebox pops up with Filepath and Filename correctly but then fileexists flag says 0.
My task is when file is there in folder go one path otherwise go other path. In my case its going to only one path.
I don't see anywhere in that code where you are setting it to 1.
Dts.Variables["User::FileExistsFlag"].Value = 1;
Even if I set this to 1. My task isn't doing the right thing.
March 1, 2017 at 12:39 pm
Try this and see what happens. Hopefully my edits make some sense:
public void Main()
{
String FilePath = Dts.Variables["User::FolderPath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
Dts.Variables["User::FileExistsFlag"].Value = 0;
if (
File.Exists(FilePath))
{
Dts.Variables["User::FileExistsFlag"].Value = 1;
}
if (Dts.Variables["User::FileExistsFlag"].Value = 1)
{
//Show the folder path with File Name
MessageBox.Show(FilePath);
MessageBox.Show(Dts.Variables["User::FileExistsFlag"].Value.ToString()); //Show the flag value, 1 for exists and 0 for not exists.
}
Dts.TaskResult = (int)ScriptResults.Success;
}
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2017 at 11:57 am
wweraw25 - Wednesday, March 1, 2017 11:04 AMDts.Variables["User::FileExistsFlag"].Value = 1;
Even if I set this to 1. My task isn't doing the right thing.
If that's the case, then the bigger problem isn't in making the script-task return the correct value, but in how you evaluate the outcome to choose the right route.
In order to make SSIS take different routes based on the returned value, you need to double-click the green line that connects to the next step and add a check for the value of the variable returned from the script task. In this case the green line that connects to the route that processes the found file needs to test for positive outcome of the following evaluation: @FileExistsFlag == 1
The green line that connects to the route that handles the situation where the file does not exist should then conversely test positive for the evaluation @FileExistsFlag == 0
Other thoughts:
By using a boolean variable type you can make your scipt a little shorter, i.e. you can do this:
Dts.Variables["User::FileExistsFlag"].Value = File.Exists(FilePath)
The evaluation test would then be @FileExistsFlag == true
A script task that use the File.Exists function can test for the existance of a specific file, but if you want to test if one or more genericly named files of type *.xls exist in a folder, you need to go a different route, for example like this:
DirectoryInfo di = new DirectoryInfo(FilePath);
FileInfo[] xlsFiles = di.GetFiles("*.xls");
if (xlsFiles.Length == 0) {
Dts.Variables["User::FileExistsFlag"].Value = false;
} else {
Dts.Variables["User::FileExistsFlag"].Value = true;
}
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply