asked
Dynamic Excel Source file in SSIS using Variable - can't get it working
**Problem:** Need to be able to provide a dynamic file name to Excel Source in SSIS. **What i have done so far:** I found a blog that walked me through setting up the variable data source, and got that all set up. **Question:** My variable,ExcelSource, "value" does not have a value. Do I need to set this prior to executing the package and then pass in a new file at runtime? If the ExcelSource does not know what to expect (i.e. columns) it fails. How do I set this up properly? All I see are "Output Columns" in the advanced editor. **Flow:** Excel Source (dynamic) -> derive some columns -> spit out all original data plus derived columns It's a very simple flow, but I'm totally stuck on the first step! Thanks! K
Not sure how you've made the source dynamic - but that's typically done by placing an expression on the ConnectionString of the Excel Connection Manager, so that's probably what you've done. The expression probably refers to a variable that you're going to push a file name into - most typically from a Foreach Loop with a File Enumerator. To develop and debug your package, you're going to have to use a "sample" Excel file. To specify that file, type the name/path into that SSIS variable that drives the expression in the connection manager. You can't leave that blank - SSIS needs something there beyond development and debugging. When it first opens the package (unless you've set DelayValidation to true) it will check to see if that property of the connection manager is set to something - and if you've left the variable blank, it will be too. You should set the DelayValidation property to true in order to tell SSIS NOT to check the value when it opens the package - because you're going to be changing it on every loop iteration (or however else you're making your package dynamic). That explanation's a little rambly, but it should get you started...
+1 for Todd, What I do when setting up dynamic connections is firstly create a static connection, so all the columns data types, etc are setup first, then I make it dynamic using the variable. And I leave a blank copy of the file at a location, and make the variable point at it just to avoid using the delayValidation flag.
