February 8, 2012 at 10:44 am
I have a new SSIS package that extracts data from an excel file and loads that data into SQL. When the task completes, I want to rename the excel file that I just processed.
It is at this step that I am encountering the "Access to path is denied" error. BTW this is occurring from the designer, the package has not been deployed.
The SSIS flow is as follows
Load excel file (Connection - Excel connection manager)
Send to a flat file (Connection - Flat file connection manager)
Load the flat file into a SQL table (Connection - OLEDB)
Process a series of SQL tasks against the loaded file
Rename the excel file (SourceConnection - File; Connection string - full UNC path and name)
When I attempt to execute the FileSystemTask I receive this message
Task Archive excel file
Start, 11:16:12 AM
Validation has started
Validation is completed
[File System Task] Error: An error occurred with the following error message: "Access to the path is denied.".
Progress: Operation Complete - 100 percent complete
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number. . .
Task Archive excel file failed
Finished, 11:16:12 AM, Elapsed time: 00:00:00.031
What I don't understand is that I can read the source file without issue
I can create the flat file without issue
But I get the error when attempting to rename the excel file
All of this takes place in the same directory.
Any ideas?
February 8, 2012 at 12:19 pm
It could be that the handle on the file has not yet been released when the file system task is executed.
To see if this is the case, add a script task before the file system task that will wait for a few seconds.
Hope this helps.
Martin.
February 9, 2012 at 12:33 am
An off-topic question: why don't you load the Excel directly in the database, instead of dumping it in a flat file first?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 9, 2012 at 5:34 am
Thanks Martin, but I am not certain that is the case. What I have done for an immediate "fix" is to simply delete the file rather than renaming it.
However, I am wondering if it might be related. The file I am attempting to rename is one of several in a ForEach container. Could it be possible that the container is preventing the rename? Doesn't sound likely, but I will check it out.
Koen,
I am loading to a flat file so that I don't run into any data conversion issues. I have a table with all fields set as VARCHAR to receive the flat file. Once loaded into that table I have more control on the handling and reporting of bad source data (i.e. invalid dates, invalid numeric fields, out of range values, etc.).
February 9, 2012 at 6:49 am
mike.welborn (2/9/2012)
Thanks Martin, but I am not certain that is the case. What I have done for an immediate "fix" is to simply delete the file rather than renaming it.However, I am wondering if it might be related. The file I am attempting to rename is one of several in a ForEach container. Could it be possible that the container is preventing the rename? Doesn't sound likely, but I will check it out.
Koen,
I am loading to a flat file so that I don't run into any data conversion issues. I have a table with all fields set as VARCHAR to receive the flat file. Once loaded into that table I have more control on the handling and reporting of bad source data (i.e. invalid dates, invalid numeric fields, out of range values, etc.).
Koen has a valid point. I don't think you are circumventing data conversion issues by transferring the data into a flat file first. You're probably better staging it or performing some conversion checks/processes inside the SSIS package.
February 9, 2012 at 6:54 am
This is off topic - my question has to do with the Access denied message.
February 9, 2012 at 6:57 am
mike.welborn (2/9/2012)
This is off topic - my question has to do with the Access denied message.
Wow...then I guess we apologize for trying to give you as much help and advice as we possibly can, even if it is slightly off topic.
February 9, 2012 at 9:59 am
Maybe you have only read rights on the folder, not modify/write?
Off-topic again 🙂
You're not circumventing data conversion issues by pumping it in a flat file.
Most data conversion issues occur when reading the Excel file, so it doesn't matter which steps you do after that.
I would read the entire Excel file as text and dump it right away in your varchar table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 9, 2012 at 12:20 pm
The attributes for the folder are Readonly = false, Hidden = false
February 9, 2012 at 1:29 pm
What happens if you move the file with a script task using .NET (FileInfo.CopyTo ; FileInfo.Move ; FileInfo.Delete) instead of a file system task? You can log in a script task using the DTS.Log command and by enabling the logging event on the script task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 27, 2012 at 8:03 am
Get real Martin Schoombee internet forums are full of people like you. You are just internet noise you contribute nothing - so get off your high horse, read the question and if you can, answer it
June 27, 2012 at 12:38 pm
Please keep your responses professional and stick to the topic of the original post.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply