Unable to open Maintenance Plans in SSMS

Hi Friends,

A few days back, I got a backup failure alert regarding one of our most critical SQL Instance. I quickly connected to our jump-box and took a session of the server to diagnose further. From the job history I concluded that I need to modify the maintenance plan in order to fix it, so I located the respective maintenance plan (we have many maintenance plans on this instance) and when I double clicked I was welcomed with a strange unique error.

I was using SQL Server 2005 SP2 on this server. Here is a screenshot of the error i received.

1_SQL_Server_Unable_to_open_Maintenance_Plans_in_SSMS

The Error Says: <writing the error in text so that search engine could locate this entry and you can find it when needed>

Microsoft SQL Server Management Studio is unable to load this document:
SSIS object name is invalid. More specific errors would have been raised explaining the exact naming problem.

And I got a pop-up with the error:

Value cannot be null.
Parameter name: component (system.Design)

After clicking Ok I noticed the same first error is repeated in the error list without any description.

2_SQL_Server_Unable_to_open_Maintenance_Plans_in_SSMS

Now that was pretty strange and unique (to me at-least), so I started searching for similar kind of problems, after a lot of BINGing and GOOGLEing I came to know that there is a dts dll which sometimes doesn’t work or gets unregistered (reason mentioned but was not correct in my case so don’t want to mislead you all) and in order to fix this problem we can try to re-register the same dll.

Step i followed in order to resolve this:

  1. For registering dts.Dll you need to first locate the C:\Programs Files\ Microsoft SQL Server\100\DTS\Binn directory from Command prompt.

Note: The complete path may be different in your case. I am using c:\ drive since the SQL Instance is installed on C drive for this server.

   

3_SQL_Server_Unable_to_open_Maintenance_Plans_in_SSMS

2. Issue a command regsvr32 dts.dll

After pressing enter I got a pop-up confirming that the DTS.DLL has been registered.

4_SQL_Server_Unable_to_open_Maintenance_Plans_in_SSMS

That’s it, now open the SQL Server Management Studio and it will let you edit\view the SSIS packages. The problem I was having with backup was a different story all together, will tell you some other day.

In case you are still not able to open SSIS packages, you may also want to try the steps given below based on the SQL Engine you have. (The above solution worked for me like anything so never tried the additional option)

If the versions of the instances of SQL Server 2005 are not earlier than SQL Server 2005 SP2, run the following command at a command prompt to register the 32-bit Dts.dll file:

%windir%\syswow64\regsvr32 “%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll”

You may also need to manually register MsDtsSrvrUtil.dll, using

%windir%\syswow64\regsvr32 “%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll
If running 64-bit Integration Services, you may need to manually register the 64-bit dlls using these commands

%windir%\system32\regsvr32 “%ProgramFiles%\Microsoft SQL Server\90\dts\binn\dts.dll

%windir%\system32\regsvr32 “%ProgramFiles%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll

 

Regards

Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

16 Comments on “Unable to open Maintenance Plans in SSMS”

  1. I have not yet encountered such a problem, but now if the appearance I will know how to fix it. Thanks for your post.

  2. I am coming across the exact same issue but cannot resolve it with the above resolution. The Maintenance plan was initially designed in MSSQL2008 and can modify from MSSQL2008 but unable to via MSSQL2005 (SP4)

  3. Hi Graeme,

    Ideally it should fix the issue, pls. check if you registered the dll for correct install (32\64-bit)

    also try this out:

    regsvr32 oleaut32.dll

    regsvr32 actxprxy.dll

    Also check if the client tools are also updated on the box where SQL 2005 SP4 is installed.

    Thanks,

    Sarab

  4. Just for the clarity:

    The above issue should get fixed if the client tools of SQL 2005 are also updated with SP4 and you’ve registered the correct set of dlls.

  5. Hi sarab,

    i already registered the dts.dll but still we are facing the same issue.we are using sql server 2008 R2 sp1 . please let me know in case we can do any thing else.

    Regards,

    Pawan Singh

  6. when i run thebelow mention command it’s open the file in notepad format and nothing else happend

    C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn>MsDtsSrvrUtil.dll

    in binn folder MsDtsSrvrUtil.dll file not have any extention what can i do now?

  7. Pawan,

    You need to prefix regsvr32 with a space.

    Just copy paste the below command and see what happens. you are unable to see the extension since by default windows don’t show extension of files which are familiar.

    Regsvr32 MsDtsSrvrUtil.dll

    –> but before you run this command make sure you are in current directory which has this file.

    Hope this helps.

  8. Now command executed successfully but still i am not able to open the maintenance it’s given me the same error.

    Value cannot be null.
    Parameter name: component (system.Design)

  9. some aditional error also appears:

    Error loading ‘Maintenance plan name’ : While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E14 (Could not find stored procedure ‘msdb.dbo.sp_dts_getfolder’.). . server=servername;package=maintenance plan name\backup shared location;

  10. Hi Even i followed above step it does’t work for me..
    But it worked if you updated client tools. or if you don’t have option to updated in production
    you can open SSMS in updated version in you local.. then it will work perfectly fine..

  11. Sarabpreet – Thank you for creating this post. We spent far too much time troubleshooting an SSRS 2008R2 Reporting problem, where all of our Report Subscriptions were hung with a Pending status. When testing email functionality with a send db email command via a weekly Maintenance Plan, we noticed our Maintenance Plans wouldn’t open. After following the recommended steps that you outlined, both on the Server and Client SSMS, we were able to open all Maintenance Plans without issue. The root problem truly was an unregistered .dll. Once we registered the .dll, problem solved. We were then able to reinitialize and send all Pending SSRS jobs. Thanks again. You made for an easy night.

Leave a Reply

Your email address will not be published.