We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SSIS Importing flat files from Directory yields Access Denied Error

Medium Priority
3,394 Views
Last Modified: 2013-11-10
I am using SSIS 2008 for the first time and am trying to use a foreach file loop to simply load the data from a delimited flat file into a sql server 2008 table.  I am using a config file to specify the directory to look in (I followed the directions in the online ebook) and each time I run the package I am getting an Access Denied error after the output line states "....The processing of file \\.... has started.  See below.
I have given everyone full rights to the directory I am trying to read from.
Any ideas?  Please help.


Information: 0x402090DC at Extract transaction Responses, Response transactions [1]: The processing of file "\\myserver\public\RecycleBin\SampleData" has started.
Warning: 0x80070005 at Extract transaction Responses, Response transactions [1]: Access is denied.
Comment
Watch Question

Commented:
Check if the user that is running the package has permissions to open the file and folder.
Regards,
Pedro
www.pedrocgd.blogspot.com

Author

Commented:
I have given all users full permissions.  Additionally, I have set the logon as user to an admin for the Sql Server Integration Services 10.0 windows service.  Is this the user running the package?

Commented:
If attach the file or send for my email I can create a package to check for you! :-(
Regards,
Pedro

Author

Commented:
How is that going to help me considering that I am trying to access a file on the network?
Considering that I am working for a business, I can not send you the file.  I am sure that you can understand that.
It was working for me previously, and then stopped.  I am not sure what changed

Author

Commented:
I just copied a test file to my local machine and changed the SSIS package to not use the config file for reading the directory and file specs.  I have deleted the expressions for setting the I have set it to read from local machine and it is still failing.  
I am stumped!

Commented:
in spite of using \\Server, use a drive letter... and test
CERTIFIED EXPERT

Commented:
Also make sure the user has access to all of the parent folders. I have seen this as an issue before. Log in with that user and open each directory individually, first the server, then the parent, next parent, next parent, etc..

Author

Commented:
I am now pointing to c:\tmp on my local machine.  I am the user and the admin.  It is not working.  This is the output from running the package in BIDS.

SSIS package "LoadData.dtsx" starting.
Information: 0x4004300A at Extract transaction Responses, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Extract transaction Responses, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Extract transaction Responses, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Extract transaction Responses, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Extract transaction Responses, Response transactions [1]: The processing of file "C:\tmp" has started.
Warning: 0x80070005 at Extract transaction Responses, Response transactions [1]: Access is denied.
Error: 0xC020200E at Extract transaction Responses, Response transactions [1]: Cannot open the datafile "C:\tmp".
Error: 0xC004701A at Extract transaction Responses, SSIS.Pipeline: component " Response transactions" (1) failed the pre-execute phase and returned error code 0xC020200E.
Information: 0x402090DD at Extract transaction Responses, Response transactions [1]: The processing of file "C:\tmp" has ended.
Information: 0x4004300B at Extract transaction Responses, SSIS.Pipeline: "component "Response" (268)" wrote 0 rows.
Information: 0x40043009 at Extract transaction Responses, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Extract transaction Responses
Warning: 0x80019002 at Foreach Response file in folder: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at LoadData: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "LoadData.dtsx" finished: Failure.
CERTIFIED EXPERT

Commented:
There is no filename, only the path. The processing of file "C:\tmp" has started.
You need the filename.

Author

Commented:
I noticed that.
I don't understand.  I am not using any variables or configs.  Isn't the filename taken from the foreach loop editor on the collection tab as in the attached screen shot?
That also should not explain the Access Denied error above that lineitem.
tmp.bmp
CERTIFIED EXPERT

Commented:
Pedro can explain this. You need to capture the filename on the Variable Mappings tab of the Foreach Loop Editor.

Author

Commented:
You mean like the attached screen shot?
I am already doing that, and it is not working.

BTW,
  Thank you very much for the quick responses.
tmp.bmp
CERTIFIED EXPERT
Commented:
In your Data Flow tab, the File Connection Manager should have this variable as the value (defined by Expression) for ConnectionString. I know Pedro has an example of this so I am kinda hoping he will send it to you.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
eheheheehehhe Steve!!! Only you!!
raphil,
I made an example for, but first check the attached doc and see if it helps you! :-)
regards,
Pedro

Post-SSISEX01.doc

Author

Commented:
YAY!
That fixed it.  Apparently that was the issue.
Seems that the connection string was not set properly.

I think what is needed to make this configurable is to set the connection string property in the data flow of the flat file connection manager to blank, and to then set the connections string in the expression property.

It now seems to be working properly.

Thank you HogZilla!

Now I need to figure out how I can store the filename of the file being processed in the db.  Any examples?

Author

Commented:
Thanks Pedro, I was in middle of writing a response to HogZilla when your message came in.  I think my issue was with the connection string setting for the flat file.

I appreciate both of "yous" help in getting this working!

Do you have any pointers before I start googling around for storing the file name in the db as well.  I know that I have it in a variable.  How can I add that to the filename column in my table, if I am using SSIS for moving the data from a flat file into the db?
CERTIFIED EXPERT
Commented:
Actually a couple of ways to do it. You can setup a Derived Column transformtion and use the Variable to create a new column. Then you map that to your Destination.
You can also do this in the File Connection Manager, Pedro - do you remember how to do this? How to add a column that holds the filename?

Author

Commented:
Perfect!  I was able to do this by adding these few steps.

1)  On the Data Flow tab add a Derived Column from the Toolbox
2) Delete the green arrow between the Flat file and Sql Server connection managers
3) Connect the Flat File to the Derived Column
4) Connect the Derived Column to the Sql Server
5) Double Click on the Derived Column and set the Derived Column Name field, leave the Derived Column to <add as new column>, in the Expression column add the FileName variable which was set in the foreach loop on the variables tab then we needed to type cast the expression to a DT_STR (since apprently SSIS defaults to WSTR and does not allow other type, currently).  So my Expression looks like this (DT_STR,255,1252)@[User::varFileName].


Run the package and BINGO!

Thanks for all of your help HogZilla.  Great Response Time!

Author

Commented:
Great job and thanks for all of the help!
Being that I am new to SSIS, it would have been more helpful if you would have provided screen shots or step by steps.
Other than that, i was very happy.  Great Response HogZillla!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.