Post reply

SQL Server Daily Job Fails

  • June 3, 2017 at 5:24 am

    #337452

    Hello,

    I have a SQL Server  job that was setup by a  third party. Inspecting the script and the stored procedure looks fine. The job runs a series of stored procedures and exports them out to various csv files and it runs daily Tuesday to Saturday at 7am. Over the last couple of weeks the job run without issue Tuesday to Friday, but the Saturday run fails. According to SQL Server Agent the job completed successfully, but looking at export files there is no data. One Saturday, I saw the same data in different files. When running the same job manually it works and the files are correct. My team and I have check the logs on the Windows Server as well as SQL, there is nothing that would cause the jobs to fail. Also, no backups are ran at that time. I am stumped and do not know where else to look for the issue. Any suggestions?

  • June 3, 2017 at 6:25 am

    #1945239

    if this is deployed to the SSISDB catalog, then there is a detailed log you can check(SSMS>>Object Explorer>>Integration Service Catalogs>>SSISDB>>TheSpecificProject
    since you say it runs on Saturday without errors, but doesn't produce the right results, i'd look there first

    by reading the steps, you might see something like a nothing inside a for-each loop is called, implying the files were not there to be processed. the actual log would have the steps that occurred, and how long each step took.
    does Saturday process files from a different location than Tues-Friday?
    "Same Data in different files" would mean what, specifically? that you know the data exists, so why is it not in the file you expect, or are you saying the package put the data elsewhere than it should?
    also, check the job, and see if any environment variables exist that are overriding what you do when you run manually, and also check for a ghost copy of the same package on Dev or something, that might be overwriting the real files with empty dev data.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 3, 2017 at 7:22 am

    #1945241

    Lowell,
    Thanks for the replay and you are correct about the Dev server. The was running on Saturday at the same time as Prod. This morning while working on the server, I kept think that the issue is staring me and the team in the face and you have confirmed it.

    Thanks,
    Maurice

Viewing 3 posts - 1 through 2 (of 2 total)

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