none
Script Component to check file exists

    Question

  • How can I use script component to check the existence of the file?  I have a excel file which I am loading to table, one of the column of excel file has complete file name with path for each line item, I want to use this to check if the file exists or not.

    I want to do this in Data flow task using script component, and pass Yes or No value as a output column to the OLEDB destination to update a log table, weather the file exists or not

    excel file

    ID     FilePath

    1 c:\file1.txt

    2      c:\file2.accdb

    3.     c:\file3.csv

    Target table I want to update it as

    ID FilePath              FileExist

    1 c:\file1.txt           Y

    2      c:\file2.accdb      Y

    3.     c:\file3.csv          N -- in case of file do not exist 

    Thanks

     

    Neil



    • Edited by Neilcse Friday, July 18, 2014 10:14 PM
    Friday, July 18, 2014 10:13 PM

Answers

  • In your script component select column you need ID,FilePath and usage type is ReadOnly. Now on the Output0 on SynchronousInputId choose none. Copy all input variable in the output and add FileExist STR datatype.

    On the Input0_Process in your script use this

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    dim FileExists as string
    If Sysmtem.IO.FileExist(Row.FilePath) then
      FileExists = "Y"
    else
     FileExists = "N"
    end if
    
    Me.Output0Buffer.AddRow 
    Me.Output0Buffer.FileExist = FileExists
    Me.Output0Buffer.Id = Row.Id 
    Me.Output0Buffer.FilePath = Row.FilePath
    
    End Sub
    Try it

    Friday, July 18, 2014 11:25 PM

All replies

  • In your script component select column you need ID,FilePath and usage type is ReadOnly. Now on the Output0 on SynchronousInputId choose none. Copy all input variable in the output and add FileExist STR datatype.

    On the Input0_Process in your script use this

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    dim FileExists as string
    If Sysmtem.IO.FileExist(Row.FilePath) then
      FileExists = "Y"
    else
     FileExists = "N"
    end if
    
    Me.Output0Buffer.AddRow 
    Me.Output0Buffer.FileExist = FileExists
    Me.Output0Buffer.Id = Row.Id 
    Me.Output0Buffer.FilePath = Row.FilePath
    
    End Sub
    Try it

    Friday, July 18, 2014 11:25 PM
  • Sorry I didnt understand that. If file doesnt exist how will you read path of the file which is itself stored in a column in file? Or are there  two files? One like master file with details of all files with their path information etc

    What we do in similar scenario is to check existence of file in control flow in a script task and only if its present data flow gets executed. The precedence constraint from script task to data flow will be setup using Expression and Constraint option with Constraint as OnSuccess and Expression as below

    @FileExists == "Y"


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, July 19, 2014 5:35 AM
  • I think Nielcse has a master file that has a file path of other files. And wants to load it to a table and put a status of the file if it is existing.

    I think Nielcse will just extract data from the master file.

    Saturday, July 19, 2014 1:42 PM
  • Yes James, 

    I have a master file which has all the path etc. in it.  why I want to use Data Flow Task(DFT) Script, Instead of script in Control Flow(CF) is, as I am loading the master file which has all these columns using DFT so while uploading the master table I want to update it with this information also that file exists Yes or No and pass on this info to OLDEB destination table.

    Thanks


    Neil

    Saturday, July 19, 2014 3:12 PM
  • In your script component select column you need ID,FilePath and usage type is ReadOnly. Now on the Output0 on SynchronousInputId choose none. Copy all input variable in the output and add FileExist STR datatype.

    On the Input0_Process in your script use this

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    dim FileExists as string
    If Sysmtem.IO.FileExist(Row.FilePath) then
      FileExists = "Y"
    else
     FileExists = "N"
    end if
    
    Me.Output0Buffer.AddRow 
    Me.Output0Buffer.FileExist = FileExists
    Me.Output0Buffer.Id = Row.Id 
    Me.Output0Buffer.FilePath = Row.FilePath
    
    End Sub
    Try it

    Try the above.

    Then add LookUp use FilePath and ID, If it is not existing it will go to oledb destination for insert but if it is existing it will go to oledb command for update.

    Saturday, July 19, 2014 11:03 PM