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.
Best Answer
Ensure you have the right extension and path to the directory set in your variables within each object including the whereloop and connection file.