June 12, 2012 at 1:37 am
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
June 12, 2012 at 1:52 am
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.
June 12, 2012 at 2:00 am
Thanks Howard.
So, we need permissions for all the packages to execute it from SQL server job agent
Regards
SqlStud
June 12, 2012 at 4:18 am
is there any other solutions for this issue
Regards
SqlStud
June 12, 2012 at 4:36 am
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" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply