So I have researched this problem thoroughly, or so I think. I want to schedule an upload of a CSV to our database in SQL Server. I have created the SSIS package using tasks --> import data. When I initially create and run the package it works fine. However, when I want to schedule the package to run I run into an error. "Cannot open the datafile "C:\Users\mydomainaccount\CsvToUpload.csv" The file is saved locally on my laptop, however, the ultimate goal is to have the source be a mapped drive where our CSV files are.
This is what I have do so far to trouble shoot:
When creating the package I have saved the SSIS package using both the SQL Server option and the File System option, both will work initially running the package, but neither work when attempting to schedule the job. Also, for protection level I have tried the Encrypt sensitive data password and the do not save sensitive data options.
I checked and made sure that SYSTEM has full control of the folder and file where the package and csv are located.
The job step is being run as a proxy account that I created, I have also tried running it as the SQL Server Agent Service Account.
The owner of the job is my domain account. My account has sysadmin privileges for that database and server. Under the msdb database option I have given my account SQLAgentOperator, Reader, and User Roles. the NT Service\SQLAgent and NT Service\MSSQL accounts have those same roles and sysadmin privileges.
So basically, I know the package will run, it is just when scheduled and seems to have some sort of permissions issue that is not allowing it to run. Any help or suggestions would be great!