0

I have multiple SQL Server 2019 instances on the same server. I've created and exported the first instance's maintenance plan, edited its exported DTSX file to change the instance and plan names to avoid name conflicts, and imported the edited DTSX via SSIS (and DTUTIL) on the same server.

Problem: I see the the both the original and imported plan in SSIS under Stored Packages > MSDB > Maintenance Plans, but NOT under the second instance's Management > Maintenance Plans list in SSMS.

4
  • I believe only Windows server can have only one copy of SSIS installed. So even if you have seven instances of SQL on the same box, you'll only have SSIS devoted to one of them. SSIS will work for all them but will initially only be aware of one of them. I think the following link still holds the answer by modifying MsDtsSrvr.ini.xml to make SQL Server SSIS aware: mssqltips.com/sqlservertip/2061/…
    – Sting
    Jul 21, 2020 at 20:21
  • Thanks @Sting. That looks very promising. I'll give it a try and report back ASAP. Jul 21, 2020 at 22:29
  • That didn't take long at all! Worked perfectly. It's annoying that the SQLSrv installer doesn't update this file with new instances. Jul 21, 2020 at 22:37
  • If you'd answer, I'll happily accept. Thanks again! Jul 21, 2020 at 22:41

1 Answer 1

0

Here you go:

I believe only Windows server can have only one copy of SSIS installed. So even if you have seven instances of SQL on the same box, you'll only have SSIS devoted to one of them. SSIS will work for all them but will initially only be aware of one of them. I think the following link still holds the answer by modifying MsDtsSrvr.ini.xml to make SQL Server SSIS aware: https://www.mssqltips.com/sqlservertip/2061/how-to-manage-ssis-packages-stored-in-multiple-sql-server-database-instances/

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.