SQL Server Agent: Deploying a job mid-execution - Effects

  • Hello all,

    I have one specific question, but please feel free to include as much related information as desired! The main question is numbered -- additional thoughts are bulleted.

    1) Does redeploying a SSIS package to a SQL Server Agent while the job [that executes that package] is running have any effects on the current execution?

        • How do the jobs work? Do they copy the ispac or dtsx file to temporary memory at each execution, or point directly to the deployed file?
        • If variables within the SSIS package are configured to a SQL Server configuration table, does changing the values of these variables within the table while the related job/package is executing affect those variables during run-time?

        I hope these questions are clear. Many thanks in advance for your thoughts and time!

      • reelnoncents - Thursday, February 2, 2017 8:45 AM

        Hello all,

        I have one specific question, but please feel free to include as much related information as desired! The main question is numbered -- additional thoughts are bulleted.

            • How do the jobs work? Do they copy the ispac or dtsx file to temporary memory at each execution, or point directly to the deployed file?
            • If variables within the SSIS package are configured to a SQL Server configuration table, does changing the values of these variables within the table while the related job/package is executing affect those variables during run-time?

            I hope these questions are clear. Many thanks in advance for your thoughts and time!

            Interesting questions ... and I can't answer them all.

            But ... SSIS packages get deployed to the server, not to SQL Agent.
            SQL Agent jobs execute deployed packages.
            There are several options here, depending on how the packages have been deployed. Packages can be deployed to the file system, to MSDB or to SSISDB – each type of deployment requires a different type of execution to be configured in your SQL Agent job.

            If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

          • Thank you for your prompt response, SSCrazy Eights. I am aware that the packages get deployed to the server instead of the agent -- please pardon my terminology. I hear so many speak about the process differently that it get confusing sometimes on how to refer to the separate points. As far as I know, you deploy the package to the server, then you use the SQL Agent to schedule jobs using the deployed packages, T-SQL, or any other types the Agent offers. Please correct me should I be wrong or if I'm missing any details or the "big picture."

            I should correct myself, and explain that I'm more interested in packages deployed to SSISDB. These jobs will run a "SQL Server Integration Services Package" job type that points to a package contained within the SSIS Catalog of a given server. These packages will always contain Package Configurations [for variables] that tie to a configuration table within a database contained on the same server that the SQL Agent and its jobs run on. Again, any and all related topics are welcome, but the details contained within this reply and my original post is the run-time environment I'm most interested in. Should anyone need more detail, please ask and I will provide.

            Thank you.

          • Do you have a development environment to test it on? Or even test it on your local PC?
            You wouldn't necessarily need to use the same package...just something that runs long enough to give you time to change the package for the job.
            I was just playing around with the job side of it (changing jobs while they were executing) with verbose logging enabled for SQL Agent. When the jobs get updated, there is a request (or two) to update it in the cache but it doesn't look like everything is updated right away. So it would be interesting to see what happens when it's a package changed in the job that is running.

            Sue

          • When a SSIS package is executed, the package is loaded into memory.  If there are package configurations, they are applied next.  Then the package starts executing.
            So, once the package is executing, changing the package by deploying a new version of it does not affect the one that is executing.  Same deal for the package configurations.
            If the package logic reads data from a database table as part of its processing, then it will get the values when that logic executes.
            If you have packages that execute other (child) packages, those child packages will be whatever is deployed when those child packages are executed...i.e. if the master package start, then you deploy an update to the child package and after that deployment finishes the master package calls the child package, then it will be calling the new version (which will use any configurations that were in place at that point in time...although, you can also pass information from the master to the child package which may confuse things a little)

            And, no ispac files are not executed.  DTSX file are executed.

          • happycat59 - Thursday, February 2, 2017 8:47 PM

            When a SSIS package is executed, the package is loaded into memory.  If there are package configurations, they are applied next.  Then the package starts executing.
            So, once the package is executing, changing the package by deploying a new version of it does not affect the one that is executing.  Same deal for the package configurations.
            If the package logic reads data from a database table as part of its processing, then it will get the values when that logic executes.
            If you have packages that execute other (child) packages, those child packages will be whatever is deployed when those child packages are executed...i.e. if the master package start, then you deploy an update to the child package and after that deployment finishes the master package calls the child package, then it will be calling the new version (which will use any configurations that were in place at that point in time...although, you can also pass information from the master to the child package which may confuse things a little)

            And, no ispac files are not executed.  DTSX file are executed.

            Do you what happens if you have a job with two job steps and a package for both? The job is running on the first step  and you delete the second step with the second package?
            With a job that just has t-sql, the second step still runs if it is deleted while on step one. It shows as Unknown step 0 but the deleted step still runs. Haven't worked at testing it with packages yet but curious if you know.

            Sue

          • Thank you HappyCat59! You've made this cat very happy with your detailed response! I saw it happen both ways (changes have effects, or don't have effects) so I was curious why. However, I couldn't find any solid documentation regarding the back-end processes taking place so I was stumped. Your response definitely helped me see the big picture, and now I can safely assume the effects I saw were due to the child packages taking in the changes while the parent didn't. Much appreciated!

          • Sue_H - Friday, February 3, 2017 11:33 AM

            happycat59 - Thursday, February 2, 2017 8:47 PM

            When a SSIS package is executed, the package is loaded into memory.  If there are package configurations, they are applied next.  Then the package starts executing.
            So, once the package is executing, changing the package by deploying a new version of it does not affect the one that is executing.  Same deal for the package configurations.
            If the package logic reads data from a database table as part of its processing, then it will get the values when that logic executes.
            If you have packages that execute other (child) packages, those child packages will be whatever is deployed when those child packages are executed...i.e. if the master package start, then you deploy an update to the child package and after that deployment finishes the master package calls the child package, then it will be calling the new version (which will use any configurations that were in place at that point in time...although, you can also pass information from the master to the child package which may confuse things a little)

            And, no ispac files are not executed.  DTSX file are executed.

            Do you what happens if you have a job with two job steps and a package for both? The job is running on the first step  and you delete the second step with the second package?
            With a job that just has t-sql, the second step still runs if it is deleted while on step one. It shows as Unknown step 0 but the deleted step still runs. Haven't worked at testing it with packages yet but curious if you know.

            Sue

            I think you have answered your question already.  "...the second step still runs...".
            If you delete the step AND the related SSIS package before the second step actually starts executing, SQL Agent will still try to run the package but will fail

          Viewing 8 posts - 1 through 7 (of 7 total)

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