Use Windows account to grant SSIS packages R/W access to files on file share

Problem

To schedule a job that runs SSIS packages that need read/write access on folders and files on a file share, the windows account running the SSIS service needs access to the file share, or you can setup a proxy account with a dedicated windows account that has access to the file share and that can run the SSIS package. Packages are stored in the SSIS package store.

A solution is also explained in Running a SSIS Package from SQL Server Agent Using a Proxy Account

Solution

• Create a windows account that has sufficient rights on the file share.
  • e.g. MYDOMAIN\ssisfileuser with password xyz
• On the SQL server where the SQL Server Agent will be running the jobs, add the windows account as a new login.
  • In the user mapping of the Login properties, select the ‘msdb’ database and assign the ‘db_ssisoperator’ Database role membership for msdb to the login.
• To run the package from a SQL job, the SQL Agent has to use the windows account as a proxy, to define the proxy we have to define a credential, where the password of the windows user can be stored.
  • Under Security / Credentials add a new credential
    • Credential name: ssisfileusercred
    • Identity: MYDOMAIN\ssisfileuser
    • Password: xyz
  • Under SQL Server Agent / Proxies, add a new proxy
    • Proxy name: ssisfileuserproxy
    • Credential name: ssisfileusercred
    • Activate the following subsystems:
      • Operating system (cmdexec) (if you are still running on 64-bit version of SQL 2008, not on SQL 2008 R2)
      • SQL server Integration Services Package
• Define a new job to be run under the windows account
  • Under SQL Server Agent Jobs, add a new job
    • General
      • owner can be your own windows account name
      • category : I use ‘Data collector’ to put the job under this kind of category
    • Steps – add new step
      • Name: use a name of your choice, I use the same name as the package to run in this step.
      • Type: Sql Server Integration Services Package
      • Run as:ssisfileuserproxy
      • General
        • Package source: SSIS Package store
        • Server: my servername
        • Use windows Authentication is default and only setting
        • Package: select the package name to be run.
    • Schedules: add a schedule when to run the job.

With this approach you can schedule packages that need file access on a share to run from the SSIS package store.