dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
1835
share rss forum feed


drew
Automatic
Premium
join:2002-07-10
Port Orchard, WA
kudos:6

SQL Server Integration Services Maintenance Plan Package

We've been fighting a whole host of issues for almost three years now trying to "templatize" our maintenance plans on our 2005/2008 boxes.

We go into VS, create the plan, save it and either save a copy to the SSIS server using VS OR save it to disk and use the import utility inside SSMS.

Right now, on a SQL 2008R2 box running on 2003 Server (both 32-bit), when running a simple package with three subplans: three sequence containers, each with a task in them- one is rebuilding a specific database's indices, one is verifying a specific database's integrity and the last is backing up the database, if successful cleaning up old backup files/translog backups/backup txt files.

When a step (any step) is ran from SSMS via SQL Agent, the following error returns:

Error: 2012-10-01 09:00:05.13     Code: 0xC0017006     Source: Tfs_Analysis      Description: The expression "LEN(@[global::RunId]) > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value.  End Error  Error: 2012-10-01 09:00:05.13     Code: 0xC0014021     Source: Tfs_Analysis      Description: There was an error in the precedence constraint between "DB Backup and File Cleanup" and "Reporting Task for subplan-{B416902A-896A-432A-AF2A-00F2628F952A}".  End Error  Could not execute package because of error 0x80004005.  Started:  9:00:01 AM  Finished: 9:00:06 AM  Elapsed:  4.485 seconds.  The package executed successfully.  The step succeeded.
 

the GUID changes depending on the package, and so does the name of the source maintenance plan (in this instance, it's a TFS 2012 Analysis services database).

I've spent my Google-fu powers on this and am coming up with nothing.

Note that if I create a maintenance plan inside of SSMS, I can run it without issue. I cannot save it to disk and then re-upload it (the package ID property GUID seems to bork out and not let scheduling occur properly).

Thoughts?

--
flickr | 'Cause I've been waiting, all my life just waiting
For you to shine, shine your light on me


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7

I presume this is you or someone else working on the problem? If not, check out the reply there first.

SSIS is Greek to me, but just looking at the error message, I'm going to bet that RunID is returning null or an otherwise invalid GUID, which causes LEN to fail as well as the greater than 0 comparison. Or another possiblity, does RunID always return a guid and it may be an empty guid (all zeros) if it hasn't been initialized/set to a specific guid (similar to how guids are used in .NET)?



drew
Automatic
Premium
join:2002-07-10
Port Orchard, WA
kudos:6

I've made some progress...

The RunId variable is not getting defined in the package created by BIDS. If I manually add it through BIDS, set the proper scope & namespace, we get a bit farther.

There seems to be a fundamental issue with the way a SSIS package is created versus the way a specialized IS package of type "Maintenance Plan" is created... there are other global variables that get added to the package by SQL Server/IS when trying to run the package after importing it.

I've got it working (after adding the variable), however the text-based reporting is completely broken. The "reporting tasks" (an item that can be defined within a package) are not getting created by the server and are unable to be defined at the package level within BIDS.

This issue is an artifact of the way we've chosen to do business - there's no need to template our maintenance plans if we weren't doing a separate plan for each DB.
--
flickr | 'Cause I've been waiting, all my life just waiting
For you to shine, shine your light on me