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.
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.
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:
- 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.
2. Issue a command regsvr32 dts.dll
After pressing enter I got a pop-up confirming that the DTS.DLL has been registered.
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
Note:
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.
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.
Regards
Sarabpreet Anand