locked
Excel File not found RRS feed

  • Question

  •  

    Hi,

     

    i am new to SSIS. I need to know can i make the excel source connection to redirect to a different excel file if the file i am looking is not available. i am using parameters to identify the excel files with the current date. if the file with that date is not found, i need to take excel file with the previous date to load the data.

     

    Any one pl help me to do this successfully

    Thanks

    Friday, December 14, 2007 7:21 AM

Answers

  • You'll probably want to use an Expression to set the ConnectionString property of the Excel connection manager based on a variable in your package. The variable should hold the filename you are looking for. If the file is not found, update the value of the variable from a script task, which will force the expression to be reevaluated, and the connection manager to change.

     

    You can use a script task to see whether a file exists (http://agilebi.com/cs/blogs/jwelch/archive/2007/10/31/checking-for-the-existence-of-a-file.aspx).

    Friday, December 14, 2007 7:41 AM

All replies

  • I would use a File System Task that tries to set the excel file attributes to All which is the default.

    If the file is not there, this step will fail, so you can use the on success or the on failure precedence constraint to execute something.

    Of course, all the connections should be controlled via configurations and variables.

    Also be careful with the Delay Validation property of the initial Excel connection if you set it up via config.

    Friday, December 14, 2007 7:33 AM
  • You'll probably want to use an Expression to set the ConnectionString property of the Excel connection manager based on a variable in your package. The variable should hold the filename you are looking for. If the file is not found, update the value of the variable from a script task, which will force the expression to be reevaluated, and the connection manager to change.

     

    You can use a script task to see whether a file exists (http://agilebi.com/cs/blogs/jwelch/archive/2007/10/31/checking-for-the-existence-of-a-file.aspx).

    Friday, December 14, 2007 7:41 AM
  •  jwelch wrote:

    You can use a script task to see whether a file exists (http://agilebi.com/cs/blogs/jwelch/archive/2007/10/31/checking-for-the-existence-of-a-file.aspx).

    I am goimg to vote for the "File exist" feature, having to use the failure of a task as to be the success of a test is not good.

    Relying on a package step that must fail to do something is not cool. 

    If you use event handlers, you must make sure you confine them to the appropriate container.

    Friday, December 14, 2007 8:04 AM