Setting Excel Source Connection dynamically

Question

Setting Excel Source Connection dynamically

maucar78

SSC Enthusiast

Points: 166
More actions
June 19, 2008 at 9:16 pm

#189462

Hi! I'm having a hard time looping through several excel workbooks. I tried setting the Excel Source Connection dynamically by
1. setting the Connection String property in the Expressions to "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelPath] + ";Extended Properties='EXCEL11.0;HDR=YES'"
2. setting the ExcelFilePath property in the Expression to
@User::ExcelFile
When I checked on the Excel Source Editor, I got the following error:
"Could not find installable ISAM"
Please help me with this. Thanks.:)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mark Beringer-730383 SSCommitted Points: 1824 More actions · Answer 1

Hi

Your expression syntax is not right. When you need double quotes in your expression add a backslash before the double quote. Check the expression below as an example:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Development\\Documentation\\File1.xls;Extended Properties=\"Excel 8.0;HDR=YES\";"

maucar78 SSC Enthusiast Points: 166 More actions · Answer 2

Hi SSC Journeyman,

Thanks for the correction. I got my package working now having followed your syntax . But it seems that it is just looping on a single excel file(Which i declared on the connection string.) how can i make it loop through all the excel files located on the same folder? Thanks!

maucar78 SSC Enthusiast Points: 166 More actions · Answer 3

Hi Mark,

I just figured out your name late. Sorry, I'm a newbie.:D Pretty obvious. 😀 uh, by the way, In addition with the above post, I tried to add ExcelFilePath property = @User::FilePath for the Expressions in the Excel Source Connection. But I still got errors. It says the path is already used by other applications, and I don't have permission to view the data on it. Actually I have used the same variable for my Dynamic Flat File Connection. It seems that I can't use a variable in another source connections. Is it true? What can I do with this? (Scenario: all type files(.xls, .txt, ,csv, etc.) should be coming from a specific folder). A lot of thanks!:)

Mark Beringer-730383 SSCommitted Points: 1824 More actions · Answer 4

Hey, no worries, SSC Journeyman sounds like some superhero 😀

Are you using a ForEach control task?

maucar78 SSC Enthusiast Points: 166 More actions · Answer 5

Hi Mark,

🙂 Yes, I'm Using a ForEachLoop Container for this.

maucar78 SSC Enthusiast Points: 166 More actions · Answer 6

Hey, I got my Excel Source Connection working now.:D I just created another variable for a default/"dummy" excel file which just contains a header row with the same column names, then used this variable in the ConnectionString property. Finally, I included the said variable on the ForEachLoop container Variable Mappings. And the package loop through all the excel files I have!:) thanks for the corrections.

Uhm, but I still have one thing to ask. 😀 I wish to include both my dataflow tasks for excel and flat file source inside a single ForEachLoopContainer. I included on the "Files:" section both *.txt, *.xls. (I have attached a screenshot of my foreachloop container) And so far, I'm not making it work. Can you please give me some tips?.:D thanks!:)