0

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!

https://i.stack.imgur.com/XqZWb.png

migrated from stackoverflow.com Sep 12 '18 at 9:30

This question came from our site for professional and enthusiast programmers.

  • What happens when you open the file saved in the root directory (C:\CsvToUpload.csv)? – Shiv Sidhu Sep 11 '18 at 16:44
  • Is there anything in the package other than the data flow task? – Eric Brandt Sep 11 '18 at 16:49
  • Shiv- I just moved the file to be right on the C: drive and updated the package. I still get the same error message. – Cam.G Sep 11 '18 at 16:52
  • Eric-I created the task in SQL server not via visual studio so I assume the only thing in the package is a data flow. All it does is goes to the file and puts it in the data base in SQL Server, if there is already data it will delete the rows and add in the new data. – Cam.G Sep 11 '18 at 16:54
  • Are you sure that file extensions are enabled, that is, your file is indeed not C:\CsvToUpload.csv.csv. To make sure check folder options -> View and check uncheck the hide file extensions box. – Shiv Sidhu Sep 11 '18 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy