SQL Agent Job error

Question

SQL Agent Job error

dave.alexander1

SSC Veteran

Points: 261
More actions
October 1, 2013 at 11:10 am

#277743

Hi,
I am experiencing the following error below. I have an update uncf processes job that has 35 steps. The job runs automatically until it gets to step 15 Create.CSV. I need to find out how to have the job run automatically through all 35 steps. Pls help! Thanks.
Update NCF Processes,Error,15,SQL04,Update NCF Processes,Create Update.csv,,Executed as user: NCF\sql_services. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:20:23 PM Error: 2013-10-01 12:20:53.63 Code: 0xC020200E Source: Create CSV Flat File Destination [2672] Description: Cannot open the datafile "J:\Projects\NS to SF\NS Contacts Update with Org\update.csv". End Error Error: 2013-10-01 12:20:53.63 Code: 0xC004701A Source: Create CSV SSIS.Pipeline Description: component "Flat File Destination" (2672) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:20:23 PM Finished: 12:20:53 PM Elapsed: 30.202 seconds. The package execution failed. The step failed.,00:00:30,0,0,,,,0

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

I must admit that I'm not even close to being an expert on SSIS, but database jobs run by executing one step after the other. If a step throws an error, the behavior is determined by the properties of the step.

The following line in the error is saying that it can't open a file on the J: drive:

Cannot open the datafile "J:\Projects\NS to SF\NS Contacts Update with Org\update.csv"

If this is a network drive, the drive must be accessible from the server when the job runs. If the drive isn't mapped, then a call to a drive letter will fail. Keep in mind that servers usually sit there without anyone logged in to them, so you might not have anything mapped.

You should be able to run the job in a test environment and have it run all the way through. If you can't, it'll tell you what step bombed and that's where you should focus your attention. If you fix the steps so they all run, the job should run through all 35 steps and succeed.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

dave.alexander1 SSC Veteran Points: 261 More actions · Answer 2

Thanks for your response. The drive was mapped and it ran successfully until I had to go in skip a step in the job. It threw off the whole process unfortunately.

jasona.work SSC Guru Points: 50117 More actions · Answer 3

You also need to make sure that the user "NCF\sql_services" has access to the location you're trying to create the file in.

If it is a network drive, remember it won't be mapped if no one is logged into the server, so you'll either need to add a step to the job to map it (not sure how well this will work,) use the UNC path (which may require changing privileges for your SQL_Services user,) or create a proxy account (bit of a pain.)