Cannot open the datafile

Question

Post reply

Cannot open the datafile

jsph152

Ten Centuries

Points: 1366
More actions
September 29, 2008 at 1:44 pm

#125910

I have an SSIS package that gives me the error "[Coradiant Export [1280]] Error: Cannot open the datafile "page-TS12-29-7C-A-0097-2008.09.09.15.30-2008.09.09.15.4020080929135617.csv". [DTS.Pipeline] Error: component "Coradiant Export" (1280) failed the pre-execute phase and returned error code 0xC020200E.
The csv files are generated by another program so the name changes for every file. But I have set up the package so that the flat file connection is pointing to a folder and I loop over the folder and grab all the csv files that are in that folder and process them. This folder is on the local machine.
To get the files to open I have to go into BIDS and in the flat file connection manager I have to preview the files that I am going to import. As soon as I have previewed them the package will run fine, but if I haven't the package will fail with this error.
I had originally thought that the permissions of the files were wrong, but I have set them to give full control to everyone so I don't think this is the issue.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 1

How are you running this when you get failure?

Normally, your SQL Server Agent account would require permissions to the required folder

ramu4ind SSC Eights! Points: 865 More actions · Answer 2

You need to give correct path in the variable and that variable can be use in the connection as expression.I think it helps

jsph152 Ten Centuries Points: 1366 More actions · Answer 3

I have made sure that I granted SQL Server Agent full access to this folder but I am still getting this error.

as for how I run this. I try running this through the command line using the dtexec command, or I will open up development studio and try and run through there. In development studio I can get the package to work by opening the flat file connection manager and previewing a file otherwise I get the same error.

jsph152 Ten Centuries Points: 1366 More actions · Answer 4

ramu4ind (9/30/2008)
You need to give correct path in the variable and that variable can be use in the connection as expression.I think it helps

in the properties for the flat file connection manager under expressions I have set ConnectionString = '@[User::MyFileName]'

This seems to be working since when it errors out It is returning the correct file name.

ramu4ind SSC Eights! Points: 865 More actions · Answer 5

It seems that the flg link may be help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1073865&SiteID=1

jsph152 Ten Centuries Points: 1366 More actions · Answer 6

I looked through that link, added in some logging which I hadn't been doing before and this is what I get.

PackageStart,********,********,********,{83D9CA85-F329-42F1-83B6-7DB9B53F7785},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:03 PM,9/30/2008 1:20:03 PM,0,0x,Beginning of package execution.

Diagnostic,********,********,********,{83D9CA85-F329-42F1-83B6-7DB9B53F7785},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:03 PM,9/30/2008 1:20:03 PM,0,0x,Based on the system configuration, the maximum concurrent executables are set to 10.

OnWarning,********,********,Populate Dimension Tables,{6FE9B562-9730-4105-A183-2AB693EF5FF5},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:16 PM,9/30/2008 1:20:16 PM,-2147024894,0x,The system cannot find the file specified.

OnWarning,********,********,Foreach File in Folder,{00ABC9EB-C562-4310-ACE8-ABD2CD622598},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:16 PM,9/30/2008 1:20:16 PM,-2147024894,0x,The system cannot find the file specified.

OnWarning,********,********,{83D9CA85-F329-42F1-83B6-7DB9B53F7785},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:16 PM,9/30/2008 1:20:16 PM,-2147024894,0x,The system cannot find the file specified.

OnError,********,********,Populate Dimension Tables,{6FE9B562-9730-4105-A183-2AB693EF5FF5},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:16 PM,9/30/2008 1:20:16 PM,-1071636466,0x,Cannot open the datafile "page-TS12-29-7C-A-0097-2008.09.09.15.30-2008.09.09.15.4020080929135617.csv".

OnError,********,********,Foreach File in Folder,{00ABC9EB-C562-4310-ACE8-ABD2CD622598},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:16 PM,9/30/2008 1:20:16 PM,-1071636466,0x,Cannot open the datafile "page-TS12-29-7C-A-0097-2008.09.09.15.30-2008.09.09.15.4020080929135617.csv".

OnError,********,********,********,{83D9CA85-F329-42F1-83B6-7DB9B53F7785},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:16 PM,9/30/2008 1:20:16 PM,-1071636466,0x,Cannot open the datafile "page-TS12-29-7C-A-0097-2008.09.09.15.30-2008.09.09.15.4020080929135617.csv".

OnError,********,********,Populate Dimension Tables,{6FE9B562-9730-4105-A183-2AB693EF5FF5},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:16 PM,9/30/2008 1:20:16 PM,-1073450982,0x,component "Coradiant Export" (1280) failed the pre-execute phase and returned error code 0xC020200E.

OnError,********,********,Foreach File in Folder,{00ABC9EB-C562-4310-ACE8-ABD2CD622598},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:16 PM,9/30/2008 1:20:16 PM,-1073450982,0x,component "Coradiant Export" (1280) failed the pre-execute phase and returned error code 0xC020200E.

OnError,********,********,********,{83D9CA85-F329-42F1-83B6-7DB9B53F7785},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:16 PM,9/30/2008 1:20:16 PM,-1073450982,0x,component "Coradiant Export" (1280) failed the pre-execute phase and returned error code 0xC020200E.

OnTaskFailed,********,********,Populate Dimension Tables,{6FE9B562-9730-4105-A183-2AB693EF5FF5},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:17 PM,9/30/2008 1:20:17 PM,0,0x,(null)

OnWarning,********,********,Foreach File in Folder,{00ABC9EB-C562-4310-ACE8-ABD2CD622598},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:17 PM,9/30/2008 1:20:17 PM,-2147381246,0x,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.

OnWarning,********,********,********,{83D9CA85-F329-42F1-83B6-7DB9B53F7785},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:17 PM,9/30/2008 1:20:17 PM,-2147381246,0x,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.

PackageEnd,********,********,********,{83D9CA85-F329-42F1-83B6-7DB9B53F7785},{EF6ADB1F-7B4A-4932-BE63-039FC9421FAA},9/30/2008 1:20:17 PM,9/30/2008 1:20:17 PM,1,0x,End of package execution.

I am seeing "The system cannot find the file specified." message but I am not sure what I can change to fix that while still using the foreach loop container to grab all files from a folder.

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 7

Try and disable validation of the loop container, maybe the path that you are accessing doesnt have the file. Also double check what files you are checking for, and consider making the path a variable which you add to the expression of the container

jsph152 Ten Centuries Points: 1366 More actions · Answer 8

I finally figured out what was causing this error. On the collection page of the Foreach Loop Editor, in the retrieve file name section, I had for some reason selected "Name and extension", I changed this to "Fully qualified" and this issue went away.

Thanks for all the help, the advice about checking the path and double checking the files that I was checking for got me looking on the right page. 😀