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.
Improve this question
10
  • 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, 2014 at 16:16
  • Nope. No file open. I'll update the question to reflect it.
    – Fastidious
    Oct 29, 2014 at 17:04
  • 2
    You might give process monitor a try to see what has it. You can filter by file name or path.
    – user507
    Oct 29, 2014 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, 2014 at 18:01
  • 1
    I can reproduce your error, if I have Excel open
    – billinkc
    Oct 30, 2014 at 14:02

1 Answer 1

Reset to default
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*
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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