Checking size of flat file in SSDT package before loading

[ahmedcheema]

I've created a script task that checks whether a flat file exists before the data flow task is executed and that the file was last updated today, but I'm having some trouble with checking for the file size. After making sure that the file exists I would like to make sure that the file is larger than x bytes.


Here is my VB code from script task:

Public Sub Main()
        '   GET THE FILE LOCATION & WRITE IT TO THE 'FilePath' VARIABLE.       
        Dim File_Location As String = CType(Dts.Variables("User::FilePath").Value, String)
        '   DEFINE A DATESTAMP FOR FILE UPDATE DATE & TIME (LAST WRITTEN)
        Dim File_DateStamp As Date
  

        '   IF THE FILE EXISTS, RECORD THE UPDATE DATESTAMP
        If My.Computer.FileSystem.FileExists(File_Location) Then


            File_DateStamp = FileDateTime(File_Location)

            Dts.Variables("User::FileUpdateDate").Value = File_DateStamp

            Dts.TaskResult = ScriptResults.Success

            '   UNCOMMENT FOR TESTING THE OUTPUT
            '   MsgBox("File Date_Stamp: " & File_DateStamp)



            ' IF THE FILE DOES NOT EXIST, RETURN FAILURE
        Else

            MsgBox("File not found.")

            Dts.TaskResult = ScriptResults.Failure

        End If

    End Sub

I use an expression in the precedence constraint to check that the file is from the same date as when the package runs.

DATEDIFF("dd",@[User::FileUpdateDate],@[System::StartTime])==0
Every time I've attempted to write the file size to a variable I get a DTS Script Error: Exception has been thrown by the target of an invocation.

Thanks for your help.

 

[SaltyTheFrog]

I don't know SSDt but My.Computer.FileSystem. has a GetFileInfo function

Dim infoReader As System.IO.FileInfo
infoReader = My.Computer.FileSystem.GetFileInfo("C:\testfile.txt")
MsgBox("File is " & infoReader.Length & " bytes.")

https://msdn.microsoft.com/en-us/library/99h600fc(v=vs.100).aspx

 

[ahmedcheema]

I forgot to add the FileSize variable in the ReadWriteVariables in Script Task Editor. Now I'm working on getting the expression built. But it looks like the .Length property doesn't give me the actual size on disk.

Public Sub Main()
        '   GET THE FILE "LOCATION\NAME.ext" & WRITE IT TO THE 'FilePath' VARIABLE.       
        Dim File_Location As String = CType(Dts.Variables("User::FilePath").Value, String)
        '   DEFINE A DATESTAMP FOR FILE UPDATE DATE & TIME (LAST WRITTEN)
        Dim File_DateStamp As Date
      [highlight #FCE94F]  Dim File_Size As Int32[/highlight]



        '   IF THE FILE EXISTS, RECORD THE UPDATE DATESTAMP & RETURN SUCCESS
        If My.Computer.FileSystem.FileExists(File_Location) Then

            File_Size = File_Location.Length

            [highlight #FCE94F]Dts.Variables("User::FileSize").Value = Convert.ToInt32(File_Size)[/highlight]


            File_DateStamp = FileDateTime(File_Location)

            Dts.Variables("User::FileUpdateDate").Value = File_DateStamp

            Dts.TaskResult = ScriptResults.Success

            '   UNCOMMENT FOR TESTING THE OUTPUT
            MsgBox("File Date_Stamp: " & File_DateStamp)
            MsgBox("File is :" & File_Size)





            ' IF THE FILE DOES NOT EXIST, RETURN FAILURE
        Else

            MsgBox("File not found.")

            Dts.TaskResult = ScriptResults.Failure

        End If

    End Sub

 

[SaltyTheFrog]

But it looks like the .Length property doesn't give me the actual size on disk."

What do you mean?

 

[ahmedcheema]

The .Length property returns the correct size when the Dim object is declared as Byte only.

 Dim File_Size As Byte()
 File_Size = My.Computer.FileSystem.ReadAllBytes(File_Location)

 MsgBox("File Size: " & File_Size.Length)

The File_Size.Length needs to be passed into a variable where I can specify how large the file needs to be for the script task to return success or failure. The problem is that File_Size.Length writes out only as an object data type which cannot be used in an expression.

Link

SIS expressions not allowed. Because they are designed to store a variety of information structures, object-typed variables cannot be used in an SSIS expression. Even if the underlying data stored in the variable is of a type that could be stored in a native SSIS type, attempting to add an Object typed variable to an SSIS expression will throw an error.

Also, the byte data type within the script does not allow operators either. I was hoping to include a condition like File_Size > 0 to make sure the file was not empty.

Error: Operator '>' is not defined for types 'Byte()' and 'Integer'

 

[ahmedcheema]

Got it working, here is the VB script

 Public Sub Main()
        '   GET THE FILE "LOCATION\NAME.ext" & WRITE IT TO THE 'FilePath' VARIABLE.       
        Dim File_Location As String = CType(Dts.Variables("User::FilePath").Value, String)
        '   DEFINE A DATESTAMP FOR FILE UPDATE DATE & TIME (LAST WRITTEN)
        Dim File_DateStamp As Date

        Dim flatFileInfo As New FileInfo(File_Location)


        '   IF THE FILE EXISTS, RECORD THE UPDATE DATESTAMP & CHECK THAT FILE IS NOT EMPTY

        If My.Computer.FileSystem.FileExists(File_Location) Then

            File_DateStamp = FileDateTime(File_Location)

            Dts.Variables("User::FileUpdateDate").Value = File_DateStamp


            Dim File_Size As Long = flatFileInfo.Length

            If File_Size > 0 Then

                Dts.Variables("User::FileSize").Value = CType(File_Size, Long)

                ' TASK RESULT = SUCCESS - IF FILE EXISITS & IS NOT EMPTY (> 0 BYTES)
                Dts.TaskResult = ScriptResults.Success

                '   UNCOMMENT FOR TESTING THE OUTPUT
                'MsgBox("File Date_Stamp: " & File_DateStamp)
                'MsgBox("File is :" & File_Size)


            Else
                ' TASK RESULT = FAILURE - IF FILE DOES NOT EXISITS OR IS EMPTY (= 0 BYTES)
                Dts.TaskResult = ScriptResults.Failure

            End If
        End If
    End Sub