If File.Exists() always returns False in Script Task
-
Oy, I'm about to go insane. I'm converting a DTS package to SSIS and I have a Script Task which checks to see if a file exists. If the file exists, it processes it. If the file doesn't exist, it sends out an email saying the file wasn't delivered yet. The email in question is *not* the package failure email and I need to keep the two distinct.
I read in a global variable from the XML config file and use the below code to check for the existance of the file. I've tried both local and network drive paths, neither of them working. I finally added message boxes to tell me what steps were being followed and never got the message from where File.Exists() = True. Argh! Can someone help?
Public Class ScriptMain
Public Sub
Dim TotFileName As String
Dim FileNameString() As String
Dim TrueFileName As String
TotFileName = CStr(Dts.Variables.Item("gsFilePath").Value)
MsgBox(TotFileName & " Pre IF")
MsgBox(TrueFileName & " Pre IF")
If File.Exists(TotFileName & "My_File_*.txt") Then
Dts.ExecutionValue = 1
Dts.Variables.Item("FileNameString").Value = Directory.GetFiles(TotFileName, "My_File_*.txt")
TrueFileName = TotFileName & FileNameString(1)
MsgBox(TrueFileName & " Success IF")
Else
Dts.ExecutionValue = 0
End If
Thanks in advance for your assistance.
Brandie
-
Lets start with the simple stuff... Does your path have a terminal slash? Appending the file name w/o the slash will result in never finding the file.
Steve G.
-
That was the first thing I checked. I have \\MyServerName\Sharename\path\ in my gsFilePath variable. That's why I put in the MsgBox stuff, so I could verify the path & file name it was pulling in.
Thanks for responding. I appreciate it.
-
I do something quite similar. I'm using a .Net script to check for the existance of the file.
The input parameter ExcelFileLocation is a full path. It is handed in via ReadOnlyVariable.
The output parameter FileFound is handled as a ReadWriteVariable.
-----code------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
'debug display commented out
'MessageBox.Show(Convert.ToString(Dts.Variables("FileFound").Value), "PRICECHECK Start", MessageBoxButtons.OK, MessageBoxIcon.Information)
If File.Exists(CStr(Dts.Variables("User::ExcelFileLocation").Value)) Then
Dts.Variables("FileFound").Value = "Found"
'debug display commented out
'MessageBox.Show(Convert.ToString(Dts.Variables("FileFound").Value), "PRICECHECK Found", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
Dts.Variables("FileFound").Value = "NotFound"
'debug display commented out
'MessageBox.Show(Convert.ToString(Dts.Variables("FileFound").Value), "PRICECHECK notFound", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
'debug display commented out
'MessageBox.Show(Convert.ToString(Dts.Variables("FileFound").Value), "PRICECHECK End", MessageBoxButtons.OK, MessageBoxIcon.Information)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
-
Am I correct in reading that you're checking for a file name which is always consistent?
I'm beginning to think my issue has to do with the wildcard character. It doesn't seem to like the way I'm doing the check for it. Instead of using the Directory.GetFiles, I tried using the Dir() function. Once I saved it as an external variable and then read it back into my File.Exists() clause, I got a success message.
Dts.Variables.Item("FileNameString").Value = (Dir(MyPath & "My_File_*.txt"))
If File.Exists(TotFileName & CStr(Dts.Variables.Item("FileNameString").Value)) Then...
How odd is this that I have to save the file name to an external variable (it didn't work when I tried to assign it to a local - inside the script - variable) before I could get the concatenation in the File.Exists() to work???
Anyone have any thoughts as to why this might be?
Thank you to everyone reading and responding to this thread. I greatly appreciate your ideas and assistance. @=)
-
If haven't tried it because my path location is 'static' - in the sense that I don't need to merge. I was recommended to use the following when I was initially looking at my requriements:
system.IO.Path.Combine
I "thnk" you would do something like:
if
file.exists(system.IO.Path.Combine(CStr(Dts.Variables.Item("FileNameString").Value),CStr(Dts.Variables.Item("TotFileName").Value))
-
I think you're right about the wildcard character. "File.Exists" does a test for existance on one file, and (I believe - haven't tested it) doesn't interpret the wildcard character as a 'match any'. That's why running things through Dir() works - it locates the real file names that you can then test for.
Steve G.
-
Try concatenating path and name and storing it in variable. print the file name with msgbox. Make sure its what you expect.
Try using .ToString and .Value and combining them. The resulting value may not be what you expect. I found it necessary to refer to variable.tostring.value
Bill T
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply