[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SSIS Importing flat files from Directory yields Access Denied Error

Posted on 2009-02-11
19
Medium Priority
?
2,834 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.
0
Comment
Question by:raphil
  • 10
  • 5
  • 4
19 Comments
 
LVL 22

Expert Comment

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

Author Comment

by:raphil
ID: 23613809
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?
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23613913
If attach the file or send for my email I can create a package to check for you! :-(
Regards,
Pedro
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:raphil
ID: 23614311
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
0
 

Author Comment

by:raphil
ID: 23614497
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!
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23614594
in spite of using \\Server, use a drive letter... and test
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23614694
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..
0
 

Author Comment

by:raphil
ID: 23614777
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.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23614819
There is no filename, only the path. The processing of file "C:\tmp" has started.
You need the filename.
0
 

Author Comment

by:raphil
ID: 23614908
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
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23614943
Pedro can explain this. You need to capture the filename on the Variable Mappings tab of the Foreach Loop Editor.
0
 

Author Comment

by:raphil
ID: 23615687
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
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 1200 total points
ID: 23615725
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.
0
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 300 total points
ID: 23615999
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
0
 

Author Comment

by:raphil
ID: 23616018
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?
0
 

Author Comment

by:raphil
ID: 23616131
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?
0
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 1200 total points
ID: 23617145
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?
0
 

Author Comment

by:raphil
ID: 23617822
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!
0
 

Author Closing Comment

by:raphil
ID: 31545656
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!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question