2

I have an SSIS package that transfers some tables to CSV files on a network drive; it runs fine from my computer manually. I store it on the server in the MSDB database and execute it from there and it runs fine, but when I create a job that has one step that runs the SSIS package from MSDB it fails saying it can't find the CSV file name.

I spent all day yesterday figuring out this means a permissions issue with whatever logon credentials are being used through the job. The job owner shows to be domain/myuserid and step properties show they are using windows authentication with my username. The problem is, I know I have access to this folder.

The first line of the error log says: "Executed as user: servername\SYSTEM". So I made sure user "SYSTEM" has access to the network folder I want to load the files on, but I still get the same error.

The command line looks like @command=N'/SQL "\SSIS package name" /SERVER servername /CHECKPOINTING OFF /REPORTING E'

edit: I found SQL Server agent job account issue where someone asks who the job is run under and marc_s says "I can't seem to find any definitive answers on that one, really. Since my Jobs typically select and update stuff in the database, I am lead to assume that the "Owner" account will be used by default, unless you specify some other account on a given step"

Which also leads me to believe it is using my logon information that has access

Community
  • 1
  • 1
markA
  • 1,609
  • 2
  • 17
  • 26

2 Answers2

4

The best practice that we've been able to come up with here is to make a domain account for SSIS and then set up a Proxy in SQL Server that is used to run the SSIS Package in a SQL Job.

ShaneBlake
  • 11,056
  • 2
  • 26
  • 43
2

I would say that the servername\SYSTEM account is a local account, and therefore won't have access to network folders on other servers.

You probably want to run this as a domain account of some sort, which does have access.

Typically this will be the SQL Server Agent, so check in the Services list, in the control panel, and see what account is running the agent, and if necessary change it to the appropriate account.

This may have knock on consequences though, so be careful what other jobs are running.

Bravax
  • 10,453
  • 7
  • 40
  • 68
  • 2
    Which is why you setup proxy accounts as mentioned in answer above. You can have multiple identities with specific rights for specific needs (or one proxy with combined rights for the needs) – cairnz Jun 24 '11 at 22:13