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
