none
Execute package in job agent delete all the records in the table RRS feed

  • Question

  • Hello everyone,

    I have a package to update a table in data warehouse. The updates include adding, updating, and deleting. When I run the package through Visual Studio and integration services catalogs. It works fine. However, when I run this package in a scheduled job, it deletes all the records in the table even though there are not records need to be deleted.

    I have been trying to solve this problem for a while, but no success. Any help would be greatly appreciated.

    Thanks,

    Xiaowen

    Thursday, December 12, 2019 2:06 PM

Answers

  • Thanks Lily,

    It turned out that the list I am using for this package has a special permission set up. When I use windows authentication to get the OData and to run the package it is OK. But when I run it through job agent, we are using SQL service account which doesn't have permission to access this list. This caused the problem.

    Thanks,

    Xiaowen

    • Marked as answer by XiaowenF Thursday, January 9, 2020 1:45 PM
    Thursday, January 9, 2020 1:44 PM

All replies

  • It's strange issue.

    Please cross check if you have same package (visual studio) deployed in IS Catalog which is being used by Job. 
    Also verify if same Server/DB is being referred when you run package from VS and SQL job.


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Friday, December 13, 2019 5:55 AM
  • Hi Xiaowen,

    May I ask that where is the package in the job obtained from?

    If it is from the ssisdb catalog, have you updated the package in SSIS? If it is updated, you need to deploy it again.

    Also please view details in job history to find more information.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 13, 2019 8:16 AM
  • Hello Vaibhav,

    Thank you very much for your reply. It is very strange. I have totally five updating packages and all the other four work well except this one. I have checked everything you have mentioned but it is still not working.

    Thanks,

    Xiaowen

    Friday, December 13, 2019 2:36 PM
  • Hello Lily,

    Thank you very much for your reply. I have deploy the new version of my SSIS package to SSIS catalog and If I execute it through SSIS catalog, it was fine.(didn't delete the records in the table). Only when I run as a job, the records are deleted. I viewed job history and compared the package execution reports, couldn't find any clue.

    Thanks,

    Xiaowen

    Friday, December 13, 2019 2:58 PM
  • Hello Vaibhav,

    Thank you very much for your reply. It is very strange. I have totally five updating packages and all the other four work well except this one. I have checked everything you have mentioned but it is still not working.

    Do you have any truncate table or DELETE FROM statements in package based on some conditions? 

    If it's a test environment, you can setup a SQL profiler, run the package and stop profiler. Then see the profiler log to see what SQL queries SSIS package has run. It might give you a hint.


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Friday, December 13, 2019 3:08 PM
  • Hi Xiaowen,

    Is there any tasks in your package that indicates deleting all the records?

    Please feel free to let us know if there are anything updated, SQL Server profiler may help you.

    If you have solved your issue, you could kindly mark the helpful post as answer. This would benefit the community.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, December 17, 2019 9:31 AM
  • Hello Vaibhav,

    Yes, I do have a DELETE FROM statement in my package. However, when I run the package through VS and SSIS catalog, it won't delete the records. I will setup SQL profiler to check what happens and will let you know the results. Thank you so much for your reply.

    Xiaowen

    Tuesday, December 17, 2019 2:19 PM
  • Hello Lily,

    I have a delete statement like this "Delete from dbo.FactRegulatoryPayment where RegulatoryPaymentAlternateID=?", not deleting all the records.

    For sure I will let you know the result.

    Thansk,

    Xiaowen

    Tuesday, December 17, 2019 2:25 PM
  • Hi Xiaowen,

    May I know the configuration of your variable( the '?' in your query)? If the variable didn't work when executing the agent job, it will execute as 'delete from Table' and delete all the records, I guess.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, December 18, 2019 9:56 AM
  • Hello Lily,

    I tried all the things you mentions in your reply. They are not the issues. I have four other similar packages. They all work fine.

    I have just found that one of the sharepoint list I used as a data source may have some problem. I tested only get  Ids from this list and put them in a table. The same thing happens. If I run the package through Visual Studio or from "Integration  Services Catalogs" all the Ids are added into the table. However, if I run it through a job agent, nothing added in. Any ideas? I used this id to split the data. Because when the package run through the job agent can't get the ids, all the table records goes to the delete branch in my package. 

    Any ideas? what should I check on my sharepoint list to make it works.

    Thanks,

    Xiaowen


    Friday, December 20, 2019 6:27 PM
  • I have just found that one of the sharepoint list I used as a data source may have some problem. I tested only get  Ids from this list and put them in a table. The same thing happens. If I run the package through Visual Studio or from "Integration  Services Catalogs" all the Ids are added into the table. However, if I run it through a job agent, nothing added in. Any ideas?

    Please cross check the job step configuration. Have a look at the parameters tab in it. 

    Maybe, the parameters are being passed correctly in VS but not in job step.



    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav


    Saturday, December 21, 2019 5:50 AM
  • Hi Xiaowen,

    Thank you for the reply.

    Do you mean that you have some confusion about the sharepoint list?

    Please check the steps here: Reading SharePoint Lists with Integration Services

    Best regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Monday, December 30, 2019 4:48 AM
    Monday, December 23, 2019 5:50 AM
  • Thanks again for your help.

    It turned out that it's a permission issue. I will explain more details later.

    Thank you very much for all your replies.

    Xiaowen

    Thursday, January 9, 2020 1:36 PM
  • Thanks Lily,

    It turned out that the list I am using for this package has a special permission set up. When I use windows authentication to get the OData and to run the package it is OK. But when I run it through job agent, we are using SQL service account which doesn't have permission to access this list. This caused the problem.

    Thanks,

    Xiaowen

    • Marked as answer by XiaowenF Thursday, January 9, 2020 1:45 PM
    Thursday, January 9, 2020 1:44 PM