Job Agent - Copying files to remote share

Question

Job Agent - Copying files to remote share

Simon Evans

SSC Enthusiast

Points: 184
More actions
May 19, 2007 at 5:36 am

#118764

I have created an SSIS package, which extracts data from a SQL table, creates a csv file on a remote shared folder and then copies the newly created csv file and put it on another remote share folder.
The Package runs okay in BIDS and also if it is executed manually in SSIS, however fails when scheduled in Job Agent with the following error message:
Message
Executed as user: SQL2005-03\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:55:49 AM Error: 2007-05-19 11:56:20.23 Code: 0xC020200E Source: Data Flow Task Destination - code_csv [34] Description: Cannot open the datafile "\\DATA-01\Import\code.csv". End Error Error: 2007-05-19 11:56:20.23 Code: 0xC004701A Source: Data Flow Task DTS.Pipeline Description: component "Destination - code_csv" (34) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:55:49 AM Finished: 11:56:20 AM Elapsed: 30.484 seconds. The package execution failed. The step failed.
The remote shares within the package are expressed as UNC's.
I think the problem may lie with the security permissions assigned to teh remote shared folders, but I have tried giving Full Control permissions for teh folders to the SQL Server computer account and teh domain level SYSTEM account, but teh package still fails.
Am I missing something?

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

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

Are you running SQL Server agent using a local account on the SQL box? If so, you'll have problems copying the file out to a network share. If this is the case, try restarting SQL Server Agent using a domain account that has write access to that share.

hth

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Catherine Eibner Ten Centuries Points: 1364 More actions · Answer 2

You could try giving the user that the package is set up to run as sysadmin access.

Kindest Regards,

Catherine Eibner
cybner.com.au

philcart SSC-Forever Points: 47795 More actions · Answer 3

Was this package running before your upgrade to SP2?

Seen a number of these type of errors occurring after SP2 was applied. File permissions don't seem to be the problem.

Are you able to open the package in Development Studio without any errors?

--------------------
Colt 45 - the original point and click interface

Cory E. SSCertifiable Points: 7076 More actions · Answer 4

I am having this issue as well - started happening just the past week. All I get for an error message is the following:

for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:44:40 AM Progress: 2007-05-21 08:44:41.08 Source: {B33567D4-A21E-44E8-B8DD-E9CFAB24C1E9} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:44:40 AM Finished: 8:44:41 AM Elapsed: 1.047 seconds. The package execution failed. The step failed.

C

-- Cory

lklein Ten Centuries Points: 1234 More actions · Answer 5

I have the same issue - the job will fail - but if I run in manually it runs.

david megaly Valued Member Points: 53 More actions · Answer 6

You need to create an account on the server that hosts the shared folders. this account has to have the same user name and password as the account that the Sql Server server is running on. (IF the Sql Server Agent is running on a different account and you are running the copy as a job you need to set an account that corresponds to the agent's account).

Funny enough the new accounts can be local accounts which means that they are technically different accounts than those on the DB servers... but hey Thanks Microsoft!!!