Running an SSIS Package as a SQL Server Agent Job doesn't copy files?

Question

I have an SSIS package I can import into Integration Services on my server and run with no problems. All it does is copy files from a directory on the network to the server it is running on.

When I execute the SQL Agent Job it says the job ran successfully but no files are copied. I verify there are files in the source location and the destination path exists. I am also using absolute paths (no mapped drives).

Why doesn't it copy any files when I run it as a SQL Agent Job?

FYI - the source directory is actually on a UNIX box and to map a drive to that location you have to enter a user/password combination.

I have a feeling that the SQL Agent Job runs as NT SERVICE\SQLSERVERAGENT, which is not the user that has permission to the UNIX box. Is there a way to run the SQL job as a specific user?

Thanks in advance.

SliverNinja - MSFT SliverNinja - MSFT · Accepted Answer · 2013-05-14T13:34:08

You need to create a Credential, a SQL Agent Proxy, and then assign the proxy account to the SQL Agent job step. Proxy accounts are specific to each subsystem (e.g Powershell, CmdExec, SSIS, etc.)

-- creating credential
USE [master]
GO
CREATE CREDENTIAL [Superuser] WITH IDENTITY = N'DOMAIN\account', SECRET = N'mypassword'
GO

-- creating proxy for CmdExec subsystem, adding principal
USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'My custom proxy',@credential_name=N'Superuser', 
        @enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'My custom proxy', @subsystem_id=3
GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'My custom proxy', @fixed_server_role=N'sysadmin'
GO

-- assigning job step to run as a given proxy user
USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_id=N'0df2dac2-4754-46cd-b0bf-05ef65e1f87e', @step_id=1 , @subsystem=N'CmdExec', 
    @proxy_name=N'My custom proxy'
GO

Running an SSIS Package as a SQL Server Agent Job doesn't copy files?

1 Answers