CODEWITHSUNDEEP
ssis
NeverEnding
NeverEnding

Reputation: 181

"Flat File Source" (471) failed the pre-execute phase and returned error code 0xC020200E. How do I stop this Happening?

Hi I wonder if you can help...

Every single week I get this error.

"Flat File Source" (471) failed the pre-execute phase and returned error code 0xC020200E. Cannot open the datafile "G:\Data\To be processed\RO_Orders.csv".

I know that if i go into the flat file connection manager editor and browse for the file at this location "G:\Data\To be processed\RO_Orders.csv" it will work. It is as if i have to reset where the location of the file is every week yet, the file is in the same location as it always has been.

I have not edited the file, or moved the location.

I have lots of other packages which load different types of data and they are fine.

Also I have a logging file attached for errors.

I also do not believe it is a ownership error as I can run the other packages perfectly and they are all owned by the same person.

This is a reoccuring annoyance that I cant seem to work out why it is happening...Ideas?

Thanks in advance.

Upvotes: 8

Views: 97107

Answers (11)

EbruB.
EbruB.

Reputation: 116

If your flatfile is open while you're executing the package. you can also see this error

Upvotes: 1

jcbooth
jcbooth

Reputation: 31

For me, the issue was that I had to register the server. I've been working on my own project on my own server/database and prior to adding a For Each Loop the package ran fine. It only ran again after adding the For Each Loop when the server was registered.

Upvotes: 0

KirstieBallance
KirstieBallance

Reputation: 1358

For me I had accidently created a variable with the same name, in two levels of scope. One was created in the full SSIS Package, and one was created just for the For Each loop. When I eliminated the one that I created for just the for each loop, my problem was resolved.enter image description here

Upvotes: 0

Isd Sava
Isd Sava

Reputation: 306

I was faced with a similar error:

Unable to retrieve column information from the flat file connection manager.

I had delay validation set to TRUE. I was using an expression to set the file name for the CSV file from a variable. The file name had changed but nothing else had. Even though I would debug the code and know that variable was being set correctly this error would continue. I even populated the expression variable with an old, valid file name as a place holder (to be written over by the script).

The only thing that worked was to create a new connection manager. Using the variable (with the old file name placeholder) this immediately solved the problem.

So in short try to avoid re-using connection managers for reasons I'm not quite clear on.

Hope this helps someone.

Upvotes: 0

akinduluth
akinduluth

Reputation: 1

I had folder permissions correct. I even logged onto the server as the user who controls the SQL Server Agent service and I could access the file. I went into the SSIS package and it turns out I did not fully-qualify the network path.

Upvotes: 0

Miguel Sanchez
Miguel Sanchez

Reputation: 29

I would check to make sure that your SQL Server service account has full rights to the landing folder.

After experiencing the same issue as you, I finally checked the folder permissions that were created for our SQL Server service account. Come to find out that it was missing the "Full Control" and "Modify" folder permissions. Once I granted these to our service account, the issue went away.

Folder Permissions Dialog Box

Upvotes: 0

Nick Blexrud
Nick Blexrud

Reputation: 9603

Had the same issue. It was a permissions/user setting for me. I was able to use the Set the Service Startup Account for SQL Server Agent (SQL Server Configuration Manager) in order to update the current SQL Server Agent user to myself.

Upvotes: 0

Neil P
Neil P

Reputation: 3210

For me, this was a permissions error when running the package as a job through SQL Server.

The Solution was to add the User account that runs SQL Server to the folder with read/write permissions.

Upvotes: 3

Adam Nofsinger
Adam Nofsinger

Reputation: 4172

We figured out that if we attempt to run the Package from SSMS on a different workstation (connected as a different user, maybe?) then we get this csv access isssue. However, if we execute the package from SSMS on the server itself, or if we run the SQL agent task that executes the package from anywhere (kick it off from my PC or from the server) then it works fine. It must be doing some user token switching or whatever trying to do it from my PC through SSMS.

Upvotes: 2

NeverEnding
NeverEnding

Reputation: 181

When Connecting up the network drive where the file was located (H:), I did not check the Connection string and what level that the file was grabbing it from in SSIS.

This is why I had to reset the connection for each step as it was not matching correctly.

Upvotes: 1

makciook
makciook

Reputation: 1555

Try setting up the connection string in Expressions. Enter full file path on the server of the file you are loading under ConnectionString property.

Upvotes: 4

Related Questions