INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Checking size of flat file in SSDT package before loading

Checking size of flat file in SSDT package before loading

(OP)
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:

CODE --> basic

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.

RE: Checking size of flat file in SSDT package before loading

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(...

RE: Checking size of flat file in SSDT package before loading

(OP)
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.

CODE --> basic

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 File_Size As Int32



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

            File_Size = File_Location.Length

            Dts.Variables("User::FileSize").Value = Convert.ToInt32(File_Size)


            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 

RE: Checking size of flat file in SSDT package before loading

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

What do you mean?

RE: Checking size of flat file in SSDT package before loading

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

CODE --> basic

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'


RE: Checking size of flat file in SSDT package before loading

(OP)
Got it working, here is the VB script

CODE --> basic

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close