I'm trying to improve what is currently a manual process to create a bunch of maintenance plans with a new SQL server instance (identical database). I'm importing/exporting with dtutil since these servers don't have SSIS running. This example is just a backup to disk, targeting the local server:

# export packages to file
dtutil /sourceserver Server01 /SQL "\Maintenance Plans\Full Backup (System Databases)" /encrypt file;".\Full Backup (System Databases).dtsx";3;$PASS

# import packages to server
dtutil /Decrypt $PASS /FILE ".\Full Backup (System Databases).dtsx" /destserver Server02 /COPY SQL;"\Maintenance Plans\Full Backup (System Databases)"

# optionally convert plan back to sql server encryption mode
dtutil /Decrypt $PASS /sourceserver Server02 /destserver Server02 /SQL "\Maintenance Plans\Full Backup (System Databases)" /encrypt SQL;"\Maintenance Plans\Full Backup (System Databases)";5 /Q

I also script to import the SQL agent jobs. This almost works okay, but I'm struggling with the following:

  • The server connection of the new task uses the source server name Local Server Connection - Server01(!), and fails to connect with error description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.
  • Even if I fix the server connections in the maintenance task editor, jobs will continue to fail with Login failed for user 'DOMAIN\SERVER02$'
  • Same for recreating the subplan jobs entirely by updating the SSIS package

Is there a way I can generalize the SSIS packages when exporting them? Can I script their creation? All can be created from the maintenance plan wizard with the default connection string


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.