Post reply

SQL Jobs moved into steps of another SQL job

  • August 2, 2010 at 12:02 pm

    #1201157

    It took ~2.5hrs. That's normal. Typically it's 2.5-3hrs depending on what's running.

    There really is no different executing an SSIS pkg as a job compared to a step withing several jobs is there?

    This is weird.

  • August 2, 2010 at 12:49 pm

    #1201183

    Can you please post the SSIS package and the job script?

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • August 25, 2010 at 9:38 am

    #1211742

    OK, I figured it out. In the backup maintenance package that I created in BIDS for backup, you create a SQL agent job. Within the SSIS package that got built will have the subplan in it. By default that subplan is disabled. If you export that SSIS package and open it with Visual Studio you'll see that the "Container" that holds the subplan is in fact disabled. Then how does it get executed you ask????

    Well, when you run the SSIS package it is executed via commandline. The backup job I had has the following commandline execution like so and worked..

    /SQL "Maintenance Plans\Backup_SystemDB_Daily" /SERVER localhost /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package\Subplan_1.Disable";false /REPORTING E

    Now when I just add the execution of the backup SSIS package as a "Step" within another SQL Agent job, the commandline looks like this and doesn't seem to execute the step....

    /SQL "Maintenance Plans\Backup_SystemDB_Daily" /SERVER localhost /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

    Notice what is missing? The segment that enables the one subplan of the package is missing. " /SET "\Package\Subplan_1.Disable";false". It opens the SSIS package and runs everything that is enabled. There is nothing enabled by default.

    First thing I thought was....."that was stupid, why did they do that?". But after a while it made sense because in some BIDS designed maintenance plans there are many other subplans. You don't want ALL of them to run every time you call that package.

    That was my problem all along. I needed to set the switch to enable the subplan within the commandline string. Now the SSIS package executes like nrormal.

Viewing 3 posts - 16 through 17 (of 17 total)

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