February 20, 2009 at 10:13 am
I setup a file system task to move a file between two servers. It works perfectly when I run the debug, it works when I run the package in Integration Services, it does not work when I try to run it through a SQL job.
File System Task setup:
IsDestinationPathVariable = True
DestinationVariable = User:: Destination
OverwriteDestination = False
Operation = Move file
IsSourcePathVariable = True
SourceVariable = User::File
Variables:
File = data type of string, value is \\server1\c$\backup\weeklybackups.bak
Destination = data type of string, value is \\server2\d$\backup\
I am running the job with a SSIS Package Execution proxy. The identity for the credential name used by the proxy has read/write access to both servers.
I receive the following error when I run the job step.
Code: 0xC002F304 Source: Move Backup File System Task Description: An error occurred with the following error message: "Could not find file '\\server1\c$\backup\weeklybackups.bak.". End Error Progress: 2009-02-20 11:36:24.44 Source: Move Backup Operation Complete: 100% complete End Progress Warning: 2009-02-20 11:36:24.44 Code: 0x80019002 Source: Move Backup Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Warning: 2009-02-20 11:36:24.4... The package execution fa... The step failed.
I have used this same setup before, but it was moving a file between folders on the same server. I wasn't crossing servers.
February 20, 2009 at 11:04 am
Simpliest explanation is that the FileSystem Task does not like to use UNC's. This is good to know, as I am going to embark on a project shortly that will be moving files between servers.
How to overcome this, not sure at the moment. You can be sure that I'll check here before I start work on the project to see if there is a resolution so I don't have to spin my wheels when the time comes.
If, on the other hand, there isn't you can be sure I will post what I find.
Sorry I can't be much help at the moment.
February 20, 2009 at 11:37 am
I don't think it relates to UNC but rather the permissions of the owner of the package and the account used to run the Agent. In configuration manager the SQL Server installer adds the Agent with Network Service account which is a limited permissions account that cannot access any remote location.
http://support.microsoft.com/kb/918760
Kind regards,
Gift Peddie
February 20, 2009 at 11:40 am
I have already tried Method 1: Use a SQL Server Agent proxy account.
Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
The credential has read/write permissions to both server folders.
February 20, 2009 at 11:48 am
File access fails because the current user does not have the required permissions to write to the file share that the connection manager accesses. For example, this scenario can occur with text log providers that do not use a login and a password. This scenario can also occur with any task that depends on the file connection manager, such as a SSIS file system task.
Actually that is not correct the proxy account must be admin account both in SQL Server and on the network level or it will not run. And in some cases the owner of the package must also be admin.
Kind regards,
Gift Peddie
February 20, 2009 at 12:00 pm
That worked! I had to add it as admin on the network level.
Thank you.
February 20, 2009 at 12:03 pm
You should be using 2 file connections, 1 for the source and 1 for the destinition. Each would point to the appropriate server (my guess here, as I haven't had to do this yet).
February 20, 2009 at 12:05 pm
I am glad I could help, why Microsoft will not document this I don't know.
🙂
Kind regards,
Gift Peddie
February 20, 2009 at 12:14 pm
You should be using 2 file connections, 1 for the source and 1 for the destinition. Each would point to the appropriate server (my guess here, as I haven't had to do this yet).
I think this relates more to Microsoft wanting all SSIS automation going back to some one with permissions to authorize the operation.
Kind regards,
Gift Peddie
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply