question

katie 2 avatar image
katie 2 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
sql-server-2005ssisexcelvariable
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Todd McDermid avatar image
Todd McDermid answered
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...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image
Daniel Ross answered
+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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the responses. I will try the suggestions today and post back questions if any.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.