1

I'm creating an SSIS package to loop through a series of Excel files (.xls). I create my while loop container and then create my data flow, which includes an Excel source.

When I edit my Excel source, I create a new connection for my Excel source using a directory where the package is going to use when the package is completed. SSIS is able to load the Excel file into the new connection and preview the columns as well data.

At this point, SSIS can load and read the file. It can access the directory and everything is good to go from this point on.

The next thing I do is create a new package variable that is a string with the value of the same exact path of the Excel connection. Remember, this is the same path that I loaded to create the connection. SSIS can access the directory and read the Excel file.

I then assign the new variable to the Excel connection under the Expression section. I set the Expression property to ExcelFilePath and assign my variable that has the Directory path of the Excel files I want to loop.

After this, I get the error:

It is already opened exclusively by another user, or you need permission to view and write its data.".

What gives? The package is 32-bit and can access the file...

  • Excel file is not open.
  • I closed/Re-opened Business Intelligence Studio to ensure it's not opened in memory.
  • I checked the file permissions, all look good.
  • File is not in read only mode.
  • I don't suppose you actually have the file open? Or it's on a network drive and someone else has the file open? Just asking because you've not said you haven't...! – Mark Sinkinson Oct 29 '14 at 16:16
  • Nope. No file open. I'll update the question to reflect it. – Fastidious Oct 29 '14 at 17:04
  • 2
    You might give process monitor a try to see what has it. You can filter by file name or path. – Shawn Melton Oct 29 '14 at 17:30
  • To completely rule out the idea that someone else has the file open. Try making a copy of the file. Then connecting to that copy and see if it works. – Zane Oct 29 '14 at 18:01
  • 1
    I can reproduce your error, if I have Excel open – billinkc Oct 30 '14 at 14:02
1

Ensure you have the right extension and path to the directory set in your variables within each object including the whereloop and connection file.

*.xls versus *.xls*

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.