7

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.

NeverEnding
  • 171
  • 2
  • 2
  • 12

11 Answers11

4

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

makciook
  • 1,537
  • 10
  • 19
  • It is an idea that I will try, however, I do not understand why the other data loads work fine without it... – NeverEnding Jul 22 '13 at 10:06
  • 2
    For me it was enough to select 'Fully qualified' in 'Retrieve file name' in 'Foreach Loop Editor'. Thanks for the hint! – MPękalski Jul 29 '14 at 12:20
  • The "Fully Qualified" thing worked for me as well, but I don't understand why that worked... – jtate Dec 04 '14 at 20:30
3

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.

Neil P
  • 2,920
  • 5
  • 33
  • 64
2

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.

Adam Nofsinger
  • 4,004
  • 3
  • 34
  • 42
1

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.

NeverEnding
  • 171
  • 2
  • 2
  • 12
1

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

EbruB.
  • 76
  • 4
  • This actually helped me. I had the flat file (csv) open in excel while I was trying to run the job. It ran without error as soon as I closed it – rohan-patel Jan 18 '22 at 21:36
0

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.

Blexy
  • 9,573
  • 6
  • 42
  • 55
0

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

Miguel Sanchez
  • 29
  • 4
0

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.

akinduluth
  • 1
0

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.

סטנלי גרונן
  • 2,917
  • 23
  • 46
  • 68
Isd Sava
  • 196
  • 1
  • 7
0

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

KirstieBallance
  • 1,238
  • 12
  • 26
0

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.

Sabito stands with Ukraine
  • 4,271
  • 8
  • 34
  • 56
jcbooth
  • 31
  • 3