February 2, 2009 at 6:02 am
Hi everyone - I'm new to both SSIS and brand new to this forum, so Thanks in advance to anyone who may be able to help me before I pull my hair out in clumps ... 😉
Last week I successfully managed to recreate the well-trodden msdn example of looping through a number of flat (text) files using a ForEach Enumerator, and simply storing the values as objects in a Recordset Destination.
I'm experimenting now with trying to loop through a folder on my local drive which contains two very basic Excel files.
I've followed the advice in the msdn article 'How to loop through Excel Files and tables ... http://msdn.microsoft.com/en-us/library/ms345182(SQL.90).aspx but without luck.
When I assign the Excel Data Source to the 'hard coded' variable - the path of the first file name, this works no problem - The loop runs twice, but (obviously) picks up the same file twice (The number of .xls files in the folder)...
However, when I adjust the connection manager to make the expression look at the string variable I set up, and remove the 'connection string property', the Data Source returns an error ...
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
Thanks Everyone - Steve
February 3, 2009 at 3:00 pm
Hi Steve,
I had the same annoying problem when I did a walk through of the same process. It definitely made me pull my hair out too. I'm not sure exactly why it acts this way but can only guess that it needs to point to a real file when doing its pre-execute checks. Regardless of the reason, I was able to get the error to stop occurring.
I initially removed the Connection String as you had done. But, this appeared to be what causes the problem. So, what I did was leave the original file I used to set up the connection at design time in the Connection String in the properties for the Excel Connection Manager. I also set the Delay Validation property to True to avoid the validation of the connection. My expression for the Connection String for my Excel Connection Manager was
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ImportFileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
You should be able to run you package and have it grab the files correctly.
As an FYI, on my For Each container, I am using the Foreach File Enumerator pointing to the folder that contains my files and grabbing the Excel files with the *.xls extension. Then, I am assigning in the Variable Mappings of the object the Enumerator Index 0 value to my @[User::ImportFileName] variable.
Also, I did occasionally have a locking issue with the SSIS BIDS environment and the Excel file I was using at design time. A couple of times I had to shut down and restart bids and on a few occasions recopy the Excel file to my directory again.
Hope this helps.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply