SSIS File System Task Error while copying files between servers

Question

I can copy files between two servers say Server A and Server B manually and I have permissions to folders on either side.

I am using File System Task to Copy files.

When my Source and Destination are within the Server the Package works fine in visual studio as well as SSISDB.

When my Source and Destination are in different Servers the Package works fine in visual studio but package fails in SSISDB. It is saying access is denied. My Account is mapped to SSISDB.

Any idea to solve this issue.

The package runs fine using the the SQL Server Agent JoB. The job is run through the proxy account.

Any way we can configure Package to Run through proxy account.

Error Screenshot

Sueannsuede

Have you configured a proxy account for the SQL Agent job to run as? Have you assigned the permissions on the remote server for that proxy account? – Glume

dba.stackexchange.com/questions/29798/… – Hispanic

@BaconBits yes. – Sueannsuede

works fine in visual studio but package fails in SSISDB. It is saying access is denied 99% certain the issue is account related - nothing earth shattering here. I'm having difficulty understanding the conditions under which it fails. What circumstances does different servers fail in (Visual Studio, SSISDB, SQL Agent). Check the logs to see who it is running under. – Equivalent

Your last statement "The package runs fine using the the SQl Server Agent Job" - does it also run fine in a SQL Agent job when using different servers? – Equivalent

@Nick.McDermaid i don't think that!!? if so, what is the question?? :) – Briseno

@Nick.McDermaid Yes the same Package runs fine when run through sql server agent job copying the files betwwen servers – Sueannsuede

So the only case for failure is running from the catalog - I assume right click / execute? This #15289942 says that it will run as whatever you're connected to SSMS as. Does your error message tell you what it's running as ? it might be a double hop issue – Equivalent

@ManojNayak can you add the full error message (screenshot or text)? – Hispanic

@ManojNayak are you using an SQL authentication login to log in to sql server? – Hispanic

The Job is running through a proxy account. How to to configure Package to run form proxy account – Sueannsuede

What about using linked server - where you can define the user context? – Closelipped

@ManojNayak try adding a windows authentication login (same credentials used in proxy) and login to the integration services catalog using this account (must be a member of administrator).You cannot use a proxy account when running packages from SSISDB. – Hispanic

Smae Permissions Still Issue Is same – Sueannsuede

Answer 1

First of all @Nick.McDermaid provided a very helpful link in the comments to learn more on Which user credentials does Integration Services Catalog use to execute packages?

Suggested Solutions

After searching there are many issues that may causes this problem, so i will provide many solutions that can solve your problem.

When Running package SSISDB

1. SQL Server Accounts Permissions

Add Read & Write permissions to the The account you are logged in on the specified paths

2. Add a windows authentication to the Network account

You can add a Windows authentication login for the network account (used as proxy in sql server agent) and run the package using it.

When Running package from SQL Agent

This is not your case, but these infos may helps

1. SQL Server Accounts Permissions

Add Read & Write permissions to the following accounts on the specified paths:

• NT SERVICE\SQLSERVERAGENT
• NT SERVICE\MSSQLSERVER

2. Setting up a Proxy

You can setup a proxy for the SSIS package and run the job using that proxy account.

You can refer to one of the following links to learn more:

• Running a SSIS Package from SQL Server Agent Using a Proxy Account
• Setting up a Proxy Account to run SQL Server Integration Services (SSIS) 2012 packages

3. Map Network Drives to SQL Server instance

Some articles suggests to map the network drives you are using on SQL Server (not the OS).You can refer to one of the following links to learn more:

• Make Network Path Visible For SQL Server Backup and Restore in SSMS
• Configure Network Drive Visible for SQL Server During Backup and Restore Using SSMS

4. Adding SysAdmin Role

Add SysAdmin Role to the following accounts:

• NT SERVICE\SQLSERVERAGENT
• NT SERVICE\MSSQLSERVER

Other Links having Similar issue

• Access denied when trying to run an agent job
• How to resolve SSIS access denied error in SQL Server Management Studio
• Can't access a network share when executing a package from the SSIS Catalog
• "Access to the path is denied" error in SSIS package
• File system task error ; Access to the path " " is denied
• [File System Task] Error: An error occurred with the following error message: "Access to the path is denied."
• Access To The Path Is Denied Error
• File system Task -copied from one folder to another folder get the error - Access path is denied

Hispanic

Thanks for the links. We did a workaround by copying files by setting up a separate scheduler through proxy account and running it every time 5 minutes to copy inputs. Directly copying file between servers through Package doesn't seems to work . – Sueannsuede

Answer 2

I got a similar error. Trying to move the file from one folder to another using File System task and got the following error message and

Solution: Create Connection manager to point to the Source file location and Create a variable named "DesVariable" to show the destination file path(eg) C:\abc. In the File system, Task properties set the following

IsSourcePathVariable: False SourceConnection : SourceFileConnectionManager

IsDestinationPathVariable: True DestinationVariable: DesVariable

Then it went through

[Move/Copy file-file system task properties]

FileSystemTask properties

Cadmium

Answer 3

Have you looked at permissions on MSDB for your domain account. I would follow these steps. I have had similar issue

1) deploy the package in server ( as you have already done)

2) Ensure packages are Loaded and Available in Integration Services Catalogs Under SSISDB

3) Redeploy the project and try it.

If these all fine, still package failing in SSISDB, i would try windows scheduler task with CMD (DTEXUI/ File/'Package Path'). Apologies if it isnt relevant talking about windows scheduler.

Grimbal

Answer 4

I ran onto a similar issue some time ago : I had to copy files from a server to the one executing SSIS before loading them.

If the package was configured to use only local directories, it worked every time. If I launched it manually, it worked with different machines. But when it came to SQL Agent + different machines, it failed.

I figured it was a problem with local and AD accounts by crawling through SQL logs and a bit of scripting in Visual Studio.

I solved this issue by :

1. creating a non interactive user account that I granted enough on the SSIS machine (more or less sysadmin on SQL Server, a very few folder access on server... fit to your needs)
2. granting this account to have access to the other server's folder (through ACL)
3. updating my SQL Agent service to use this "legal" AD account
4. done !

This has been working every day since almost a year without a single problem.

Siret

Answer 5

There are a lot of guesses and red herrings in the answers and comments posted. It has nothing to do with SQL Agent proxies, mapped drives, MSDB or sysadmin. As eventually gleaned from your question, the execution in SQL Agent is fine, it's just when you run it interactively by right clicking in the Integration Services catalog that the issue occurs.

The final part of your question indicates the root cause is understood here:

Any way we can configure Package to Run through proxy account.

The interactive user (the one right clicking on the catalog) doesn't have rights and you need a way to run from the catalog as a different user that does have rights.

Just for confirmation: if you click View Context in your SSIS log it will show you which user it ran as, which will be different to the successful package executions from SQL Agent.

I dug around the execution and configuration menus in the catalog as well as the create_execution sp, and I don't see any way to run it as a different user.

I have two suggestions:

1. Use Run As when starting SSMS and run as a different user
2. Just wrap the package up in a SQL Agent, which allows you to use other accounts to run the package (either the SQL Agent service account or a proxy)

Equivalent

i agree with that, my answer was generic because the question was too broad when asked and before editing. and i didn't edited my answer after that. I edited my answer to be more clear. thx for your remark. Also +1 for a good explanation – Hispanic