Post reply

How to run xp_slssqlmaint in Exec SQL Task

  • January 18, 2012 at 5:30 am

    #266891

    Hi,

    We are working for the first time in SSIS to create a DB Backup plan using Sequence container so that multiple backups can be taken at the same time.

    So far, we have tried two scenarios :

    1.

    We are using four containers, each containing Execute SQL Task.

    The SQL task executes a SQL query to take the backups using an extended procedure used by litespeed to take the backups.

    The parellel execution of the containers is fine and are able to take multiple backups simultaneously.

    Issue : SSIS logging does not log which DB has failed to get backed up in case of any failure. It only logs the success/failure of the containers.

    Is there any way, the Execute Sql Task get logged like a maintenance plan logs the backup activity per database as the query is using xp_backup_database as used by Litespeed for DB backup ?

    2.

    We also tried using an extended stored procedure xp_slssqlmaint under the Exec SQL Task as it contains a parameter for logging and reporting.

    We directly pasted the query from SSMS in the SQL Query input box but i think its not accepting the parameters of the procedure.

    If we run the query in SSMS, it runs fine and also creates a log file in text file but the same doesn't happen in SSIS SQL task.

    Issue : We are not able to execute the extended procedure Exec SQL Task in SSIS. The package runs successfully but dont perform any backup or logging.

    Kindly let us know the solution/procedure to run the procedure. Below is the procedure with the accepted parameters:

    execute master.dbo.xp_slssqlmaint N'

    -D "ABCDEF"

    -BkUpMedia DISK

    -DelBkUps 2DAYS

    -BkUpDB "C:\"

    -CrBkSubDir

    -BkExt "bak"

    -NOOPTOLR

    -Threads 3

    -Throttle 100

    -BufferCount 20

    -MaxTransferSize 1048576

    -CompressionLevel 5

    -Priority 0

    -Logging 1

    -Rpt "C:\Plan.txt"

    -Affinity 255

    -Default "%D_%T_%Y-%m-%d-%H%M%S.%EXT%"

    -Exclude Offline '

  • January 18, 2012 at 10:31 pm

    #1435649

    Guys, I am expecting a reply 🙂

  • January 22, 2012 at 1:59 am

    #1436868

    I am still expecting a reply from anyone !!!!!

    I think the problem seems to be a complex one 😛

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

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