x

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

more ▼

asked Sep 16, 2011 at 08:05 AM in Default

avatar image

katie 2
74 6 6 10

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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...

more ▼

answered Sep 19, 2011 at 09:42 AM

avatar image

Todd McDermid
391 2 3

(comments are locked)
10|1200 characters needed characters left
  • 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.

more ▼

answered Sep 20, 2011 at 08:36 PM

avatar image

Daniel Ross
2.9k 11 15 18

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

Sep 21, 2011 at 06:24 AM katie 2
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2031
x1221
x148
x30

asked: Sep 16, 2011 at 08:05 AM

Seen: 7660 times

Last Updated: Sep 16, 2011 at 08:05 AM

Copyright 2017 Redgate Software. Privacy Policy