There is a pre-existing SSIS package which performs multiple file manipulations based on a source file and often fails when that file is not found at the expected directory. I just want to build some smarts into it so that it will email notification of the missing file, rather than fail.
I have attempted MANY different Script Tasks, using VB, C and SQL, but NOTHING is controlling the process flow consistently. It works sometimes, and when it doesn't work others. I have listed my variables are below --- the top three are all I expected to use, but I added FullPath to simplify things. Unfortunately, that made no change.
My tests: I remove the source files from the directory and execute the package in VS, it DOES call the send email task letting me know the file does not exist. Then I put the files into place and execute the package, it calls the send email task again, as though the file is not there. I am not very good with breakpoints and watch windows, so I put two message boxes into place for Filepath and FileExists -- the Filepath returned IS correct, with the filename, yet the FileExists message box value returned immediately thereafter returns a 0. Note, at the very same time this is telling me it doesn't see the file, I have checked the disk and can see it is there.
Here's the kicker: I've been on this for days and was testing yesterday -- suddenly it works! I put the files into the source directory, it ran the whole process correctly. I removed the files from the source directory, it called the send mail task and completed successfully. I tested both conditions multiple times successfully -- and now it is failing again. I do not understand and have no desire or time to keep testing this file existence check script task that only works intermittently. I even tried today to get the File Properties task that I am hearing so much about (https://archive.codeplex.com/?p=filepropertiestask) but it is incompatible with the current versions of the software. I've tried VS 2019 and SSDT 2017, File Properties is incompatible/unsupported in either. Or, I just don't know how to install it.
Can anyone advise?
Variables -
- FileName string, fileName.txt
- FilePath string, C:\directory path\
- FileExists boolean, False (though I've tried int32, even char N/Y)
- FullPath string, C:\Directory path\filename.txt
C Script Task attempts -
public void Main()
{
// TODO: Add your code here
String Filepath = Dts.Variables["User::FilePath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
if (
File.Exists(Filepath))
{
Dts.Variables["User::FileExists"].Value = 1;
}
else
Dts.Variables["User::FileExists"].Value = 0;
Dts.TaskResult = (int)ScriptResults.Success;
}
OR
//TODO: Add your code here
String Filepath = Dts.Variables["User::FilePath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
if (
FileExists(Filepath))
{
Dts.Variables["User::FileExists"].Value = 1;
}
MessageBox.Show(Filepath);
MessageBox.Show(Dts.Variables{"User::FileExists"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
or even just as simple as this:
Dts.Variables("FileExists").Value = File.Exists(Dts.Variables("FilePath").Value).ToString
Dts.TaskResult = (int)ScriptResults.Success;
VB Script Task -
Public Sub Main()
' Fill WriteVariable with value from ReadVariable
Dts.Variables("User::FileExists").Value = Dts.Variables("User::FullPath").Value
Dts.TaskResult = ScriptResults.Success
End Sub
Exec SQL Task -
DECLARE
@FilesExist BIT = 0,
@FolderPath VARCHAR(100) = 'C:\directory path\'
DECLARE @Files TABLE ([FileName] VARCHAR(100), Depth INT, [File] INT)
INSERT INTO @Files
EXEC master.sys.xp_dirtree @FolderPath,1,1;
IF EXISTS(
SELECT 1 FROM @Files
WHERE [FileName] = 'fileName.txt'
AND Depth = 1
AND [File] = 1
)
SET @FilesExist = 1
RETURN;
Script Task Precedent constraints:
- Evaluation Operation: Expression and Constraint
- Value: Success
- Expression: @[User::FileExists]==1
- Logical AND
Evaluation Operation: Expression and Constraint
- Value: Success
- Expression: @[User::FileExists]==0
- Logical AND
This is a dummied screenshot of my control flow. Where the script task file existence check is the 7th item in the flow. The filename has no date in it. It is always 'filename.txt'. This file is created by the 4th task in the flow by merging other files, and I have just learned that I need to add a similar check here -- but there are multiple files, so I will need to do a wildcard check before the 3rd task in the package as well.
