locked
Picking a Data Source File from Configuration RRS feed

  • Question

  •  

    Hi there,

    I believe this is simple requirement but to me it seems to be complicated. My data source is excel. I would like to configure the path of my data source in SSIS Package and file name is not a constant value. SSIS package should pick the file named as Today's (current) date from the configured path.  As well, package should catch the exception for example file not found and log into custom log. It would be gr8 if some body helps me out.

    Friday, March 7, 2008 5:30 AM

Answers

All replies

  • The tool you need most is SSIS expressions: http://bi-polar23.blogspot.com/2008/03/express-yourself.html

     

    This page shows examples of different date expressions: http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html

     

    These pages show examples of building expressions on Excel connection managers to make the file path dynamic: http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html and http://bi-polar23.blogspot.com/2007/09/loading-multiple-excel-files-with-ssis.html.

     

    This should give you a good starting point, but putting it all together is up to you.

    Friday, March 7, 2008 5:42 AM
  • matthew's post would help you, but i want to point out something that may trip you up.  it would behoove you to have your package check for the file's existence before it goes any further.  the way that i recommend that this be done is by employing the script task.  the .net system.io namespace can be used within the script task to check if the file exists.
    Friday, March 7, 2008 7:39 AM
  • Thanks for your replies. I have a question, Cant we create the configuration file in SSIS as availabe in .Net. ? Because I would like to configure File Path (not file name) in config file rather than hard coding into a Variable.

     

    Friday, March 7, 2008 7:31 PM
  •  Amzu wrote:
    Thanks for your replies. I have a question, Cant we create the configuration file in SSIS as availabe in .Net. ? Because I would like to configure File Path (not file name) in config file rather than hard coding into a Variable.

     

     

    I suppose you could, but this would involve building some sort of application to update the configuration file every day. This might work, but it is definitely not the way SSIS configurations were designed to be used.

    Friday, March 7, 2008 7:38 PM
  •  

    You can also specify a file path in a configuration file.  You can specify two variables, one for path, one for file name, or just a single variable.  If you use two variables, you can put them together in a third variable via expressions inside SSIS and then you'd just use that third variable everywhere you need the "full path."

     

    Friday, March 7, 2008 7:47 PM