Error after migrating packages

Question

Error after migrating packages

OakDBA

Say Hey Kid

Points: 673
More actions
February 19, 2006 at 2:16 pm

#166990
Our plan to migrate to SQL 2005 involves beginning by creating SQL 2005 Dev. Ed. instances on our existing SQL 2000 dev servers. This has proceeded smoothly, and all of the databases have been migrated without issue. The problems have begun when attempting to migrate the DTS packages to SSIS. Using the Package Migration Wizard, all of the packages report to migrate successfully, however, I notice a couple of things when attempting to view the packages:
- When connecting to the Integration Service, if I type in SERVERNAME\INSTANCENAME, I receive an error message indicating that IS does not support mult-instance naming. When I type in just SERVERNAME, I connect without error.
- When I attempt to open the MSDB folder in the Integration Svc. to view the packages, I receive the following error: “Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) Client unable to establish connection. Encryption not supported on SQL Server. (Microsoft SQL Native Client)
In an effort to resolve this error, I have tried several things, none of which have worked.
- I changed Integration Svc. to use a domain admin account (it was the only service that was using a local system account).
- I added my login, which was already local admin and sysadmin, to the SQLServer2005DTSUser$SERVERNAME group.
- I enabled Named Pipes and TCP/IP in the client configuration.
- I noticed the port configured for TCP/IP was 1433, which is the port being used by the default SQL 2000 instance, so I changed it to the port being listened on in the SQL 2005 error log.
At this point, I don’t know if this is a security, configuration, or chair-keyboard interface error. I would like to find out if there are others who have encountered this issue and what steps they took to resolve it.

Also, as a general question, if you have multiple instances of SQL 2005 on the same physical server, will there only be one Integration Svc. instance for all Database Engine instances?

Thanks in advance,
Robert Haas
February 22, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

icata Hall of Fame Points: 3176 More actions · Answer 1

I have done some testing with migrating MSSQL 2k DTS packages to a MSSQL 2005 name instance, and supprisingly, you can not see that 2000 packages under MSDB unless you migrate them to the default 2005 instance (local) not the named one.

Angela Palmer Newbie Points: 1 More actions · Answer 2

You have to change the Integration Services ini file to reflect the servername/instance name. By default it points to the default instance. SQL 2005 Books online provides this solution under "Configuring Integration Services Service".