SSIS Package can't open 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 2008 Forums

SSIS and Import/Export (2008)

SSIS Package can't open flat files

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-08-03 : 14:28:44

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:31:12

16 views and not one suggestion? Really?

oldjeep
Starting Member

5 Posts

Posted - 2010-08-06 : 08:48:45

Does the account which the SSIS package is running under have permissions to that directory?

Chuck P

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-08-06 : 09:29:47

According to the server admin, yes.

Part of the problem is the Dev and Prod servers are running two different OS's, two different versions of SQL Server, have different hardware setups, etc. So I can't develop and test in an environment that matches the one the package needs to run in. As such, I know I may never get the answer.

oldjeep
Starting Member

5 Posts

Posted - 2010-08-06 : 09:31:19

I guess that I would turn on full logging of the package to a text file and see if there is any additional information presented.

Chuck P

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-08-06 : 10:20:52

Yep, trying that this weekend.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources