Cannot open data file error while running SSIS package through SQL Server job

Question

Cannot open data file error while running SSIS package through SQL Server job

sqlstud

SSCrazy

Points: 2527
More actions
June 12, 2012 at 1:37 am

#259235

Hi,
I created a SSIS package which will generate an output file and place it on a remote fileshare location
\\RemoteServerName\RemoteFilePath
The package is executing fine when I am executing it through BIDS or through execute package utility and writing the output file to remote file share location.
I created a SQL job for the package and ran the Job.
For a package, it successfully generated output file.
While tried to execute another package(Using the same share folder)
Then, its throwing an error saying
Executed as user: Domain\User. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:33:47 AM Error: 2012-06-12 02:33:49.50 Code: 0xC020200E Source: Write sample Data S_SAMPLE [468] Description: Cannot open the datafile “\\RemoteServerName\RemoteFilePath\OutputFileName.csv". End Error Error: 2012-06-12 02:33:49.50 Code: 0xC004701A Source: Write Sample Data SSIS.Pipeline Description: component "S_SAMPLE " (468) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:33:47 AM Finished: 2:33:49 AM Elapsed: 2.297 seconds. The package execution failed. The step failed.
Domain\User have all the permissions on the remote file share location.
SQL server agent is running with the log-on account Domain\User(same as the above).
It has full rights to that folder to any user .
Could anyone help me in resolving this issue.
Regards
SqlStud

Viewing 5 posts - 1 through 4 (of 4 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

It definitely sounds permission based. I'd logon to the server as the SQL Agent user and double check that it can physically access the folder/file through the share. File permissions also don't necessarily inherit from folder permissions, so if there's an existing file, make sure that the file also has the correct permissions.

I've had some funny permission issues, especially on Windows Server 2008 where permissions are granted at the folder/file level, but when browsing to the share as the service account user, you get a UAC style prompt to grant access and it then magically works.

sqlstud SSCrazy Points: 2527 More actions · Answer 2

Thanks Howard.

So, we need permissions for all the packages to execute it from SQL server job agent

Regards

SqlStud

sqlstud SSCrazy Points: 2527 More actions · Answer 3

is there any other solutions for this issue

Regards

SqlStud

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

HowardW (6/12/2012)
File permissions also don't necessarily inherit from folder permissions,

They don't at all. Share permissions and NTFS permissions are completely separate but do combine when accessing objects through a share, the most restrictive applies. The default for a share from Windows 2003 on is everyone - ReadOnly

Go to the server and check the share permisions on the share tab, then go to the security tab and check the NTFS permissions. Ensure the combination allows sufficient access for the required user account (on the security tab click the advanced button and go to the effective permissions tab and supply the username 😉 )

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉