Package failing but the table is already there and the system says its missing!!

  • 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

    Attachments:
    You must be logged in to view attached files.
  • 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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

  • 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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply