Dear Everyone
Im using SSIS 2019 and VS 2019
The data scientist gave me a project with multiple packages and when i use VS all the packages run fine
However when i deploy the project to the SSIS catalog and create a new job to run the packages in a certain sequence; i get the error attached.
The Execute SQL Task runs fine but it fails when it moved to the Data Flow Task 1 with the error in the attached document.
I read that it might be linked to the protection level as on the project level the security is set to EncryptSensitiveWithUserKey
Any ideas?
regards
Kal
Looks like an issue with access rights. Are you sure that the user (as shown in the All Executions report) has full access to the object?
Also, are you inserting data to a view, rather than a table?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 8, 2021 at 8:27 pm
So the user who is calling the job in SQL server agent only has the Public role on the database / table.
I cant give him db_owner so let me give him db_datawriter and see if that works.
Is that the recommended permission to give to service accounts calling SSIS jobs in the SQL server agent?
Kal
May 8, 2021 at 8:45 pm
You were right
I gave the service account db_datareader and db_datawriter on the database level and the package worked fine.
I guess when the SSIS error message says an object is missing; then it means the user running the job has no access.
Thank you its working now!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply