SSIS Package can't read flat files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS Package can't read flat files

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-08-03 : 14:55:40

I have an SSIS Package that downloads flat files ("|" delimited text files) from and FTP server and uses them to load data into tables. It runs fine both as SSIS package and as a scheduled job on our Dev server, but fails as a scheduled job on the Production server.

A sample error message in the Job History is:

Error: 2010-07-31 12:03:09.27     
Code: 0xC020200E     
Source: IT0032 Data Flow Task IT0032 176573 SourceSystem CDS FF Source [119]     
Description: Cannot open the datafile "D:\SSIS\R9\Hewitt\Import\HRMS_CDS_CON176573.TXT".  
End Error  

Error: 2010-07-31 12:03:09.28     
Code: 0xC004701A     
Source: IT0032 Data Flow Task SSIS.Pipeline     
Description: component "IT0032 176573 SourceSystem CDS FF Source" (119) failed the pre-execute phase and returned error code 0xC020200E.  
End Error

In the Application Event Log we get:

Event Name: OnError
 Message: Cannot open the datafile "D:\SSIS\R9\Hewitt\Import\HRMS_HR6X_CON176588.TXT".

 Operator: CORPPVT\sqlsrvr
 Source Name: IT9000 Data Flow Task
 Source ID: {3A505335-2CD3-4C9F-ADF4-6EE42FE12BEA}
 Execution ID: {1B10CF94-976A-4E29-8D86-F80B39767585}
 Start Time: 7/31/2010 12:03:11 PM
 End Time: 7/31/2010 12:03:11 PM
 Data Code: -1071636466

I know the file names are different - this occurs for all the flat files, so the file name is irrelevant in these sample error messages.

The Dev server is running SQL Server 2005 while Production is running 2008. Are there differences between the two that could account for this?

What's really weird is that this used to run fine in Production. Before anyone asks, I'm being told that no changes have been made to that server since the last successful run.

One final detail - the job is supposed to write a custom log to a text file via System.IO.StreamWriter. It writes the log in Dev but not in Prod. It seems our Prod server has a problem with text files.

Any help would be greatly appreciated!

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-08-04 : 09:30:21

Really? 20 views and no one has any suggestions?

sherrys
Starting Member

37 Posts

Posted - 2010-08-09 : 16:34:48

When you run the ssis manually on the production server, it is running under your credentials.

The scheduled job on the production server is running with the permissions of the Agent. What ID is running the production Agent? Is it an Network account, a local account or a service account? Whoever it is, that account has to have read/write permissions to the file system.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-09 : 22:06:06

quote:
Originally posted by JeffK627

Really? 20 views and no one has any suggestions?

I have a suggestion: Ask a few dozen of us what our consulting fees are. We're mostly professionals here who take time out of our days to help others learn. Free of charge too. But we don't guarentee turnaround times. We may actually be working on our paying jobs or spending time with our families.

Aside from that, looks like Sherrys gave you the answer you need.

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-08-10 : 11:24:31

sherrys - the Agent has all permissions necessary to run the job and access the files, that was checked. Thank you though.

russell - I know nobody guarantees turnaround times, but I usually see responses much faster on this forum and I was expressing surprise, not complaining. Although if you're too busy to answer questions you should probably be too busy flame people. Have a great day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources