Today one of the members of the Project DBA Team installed SQL Server Integration Services (SSIS) 2005 on one of the production servers. The installation was successful and the first thing anyone would do to confirm is connect to SSIS through SSMS. Surprise!
The error message reads “Connect to SSIS Service on machine “ServerName” failed: Error loading type library/DLL.” and there is no mention of the name of the DLL which is having issues.
The next option is to check if we are able to connect to the SSIS from a remote server. Yes, we were able to connect to this instance of SSIS from some other server. Now it was confirmed that the problem did not exist with the SSIS installation as such. It had to do something with the DLLs located on the same box.
Since SSIS is having issues, the first DLL that would come to mind is the DTS.dll. This file is usually located in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn folder. While I was navigating to this folder, I observed that a folder named 100 was also residing in the same location as that of 90 folder. This hinted that someone had tried some of the components of SQL Server 2008 and it could have possibly messed up some of the files related to SQL Server 2005.
Hence I planned to register the DTS.dll again. Just opened the DTS.dll using C:\WINDOWS\SYSTEM32\regsvr32.exe in Windows Explorer and it said “Registered successfully”. This can also be done using the command line regsvr32.exe.
Again opened up SSMS and as predicted we were able to connect to the Integration Services.
Yesterday, I responded to a post in which the Maintenance Plans were failing after the cluster resources failed over. The user doubted that on the passive node (now active after failover), the Service Pack installation was not done properly and it had to do anything with SSIS installation on that node.
Since maintenance jobs were failing, I was thinking of trouble shooting this with the help of the error messages in the job logs. Meanwhile an angel knocked on my head and reminded “Hey, SQL Server 2005 does not depend on SSIS”.
Oh Yes! SQL Server 2005 SP2 and SQL Server 2008 SP1 CU3 onwards, the Maintenance Plans do not depend on SSIS. Microsoft articles here and here confirm that fact.
Now that the SSIS dependency is ruled out the job failures experienced by the user has to do something with the Service Pack 2 installation or some other factor. I am eagerly waiting for the Job Error Logs to see what is causing the job failures.
Yesterday, I talked about the Backup Task not being editable in the Maintenance Plan. The workaround was also discussed. After posting that topic, I worked more on that and found an easy solution. Here it is.
- Open SSMS and open the Maintenance Plan for editing
- Click on the Backup Task and Press F4
- This will open up the Properties window for that task
- Navigate to “ExpireDate” property
- Remove the entry in that property and leave it blank
- Save the Maintenance Plan
Voila! the Backup task is ready to be edited. Sometimes the easiest of solutions come out after the issue has been resolved. Isn’t it?
Today came across an issue on a SQL Server 2005 Named Instance. The requirement was to edit one of tasks in a Maintenance Plan. After opening the Maintenance Plan, when double clicked on the task to be edited, SSMS threw the below error message.
Value of ‘7/9/2010 12:00:00 AM; is not valid for ‘Value’. ‘Value’ should be between ‘MinDate’ and ‘MaxDate’.
The problem was that the “Backup Set will Expire” was set to today i.e. 7/9/2010 in the Maintenance Plan (not sure how !). I could not see it since the step was not opening up, so this is an assumption. To workaround this problem, the following tasks were performed. First opened the Maintenance Plan outside SSMS for editing.
- Opened Business Intelligence Development Studio on the Server
- Created a new Project
- Right click on the SSIS package and selected “Add Existing Package”
- In the next dialogue, selected SQL Server and connected to the SQL Instance
- Clicked on the “Backup Database” task and navigated to the “ExpireDate” value
- Deleted the value ‘7/9/2010’ and left the field blank
- Saved the package to disk locally
Now the package was edited and ready to be imported back into the Maintenance Plan. So, fired up SSMS and connected to the Integration Service on the Server. Navigated to Stored Packages and clicked on MSDB. But after a long wait, got an error message which indicated that SSMS was unable to connect to the instance. At that time I remembered that the instance was a Named Instance and SSIS was trying to connect to the MSDB database of the Default Instance (which never existed). The next step was to modify SSIS to connect to the Named Instance’s MSDB. Here is what was done.
- Navigated to C:\Program Files\Microsoft SQL Server\90\DTS\Binn folder on the server
- Opened MsDtsSrvr.ini.xml in Notepad
- The value of MSDB database was
Now the SSIS is ready to import the modified package. The file was imported back to Maintenance Plans as below.
- Connected to SSIS using SSMS
- Navigated to Stored Packages –> MSDB
- Right clicked on Maintenance Plans and selected Import Package
- Selected File System in the next window and pointed to the package stored locally on disk
- Selected Overwrite when asked for confirmation to overwrite the existing package in Maintenance Plan
Now headed back to the Maintenance plan and double clicked on the “Backup Database” task and it was ready to be edited !