Tuesday, May 22, 2007

SSIS problem when you have both SQL2000 & SQL2005

Last week I try to start working with SSIS (SQL Server Integration Services).
When I try to work with SSIS I received this error
"failed to retrieve data for this request. (microsoft.sqlserver.smoenum)"

After some searching I find that SSIS can only connect to one instance of SQL server, and when you try to connect to SSIS from SSMS (SQL Server Management Studio) you don't have the option to choose the instance to connect to (It connect to the default instance).

So if you have SQL server 2000 installed as default instance and SQL 2005 installed as named instance you will not be able to work with SSIS unles you do the following:
-Open this file "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml"
-Change the value of "ServerName" node to be the SQL2005 instance name (ex:ServerName\SQL2005)
-Then go to SQL Server Configuration Manager and restart the service
-Go to SQL Server Configuration Manager Right click on SQL Server Integration Services. Select properties and in the Log On tab change it to be "LOCAL SYSTEM"
Then restart the service


You can check this post it may help
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1532139&SiteID=1

No comments: