Can't connect to SSIS

Hi,

I'm evaluating SQL server 2005 for planning a upgrade of our datawarehouse from SQL 2000 to 2005.

We use a lot of DTS pacakages so that's my primary focus right now.

I get this strange error when trying to connect to SSIS -> Stored Pacakges -> MSDB:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

BUTTONS:

OK

The install is on a x64 2003 with a x64 enterprise edition, with two instances. So what msdb is it trying to connect to since I can't seem to see this anywhere in management studio?

Initially installed the two instances without named pipe support (only tcpip), but the naitive client was configured for named pipe support. I have now enabled named pipe support for both instances as well and afterwards restarted the server, but the problem persists.

I've also checked the two instaces to verify that they allow remote connections, as well as doubling the query timeout period to 1200.

I managed to move via DTS backup (non ms tool) a package from a test server onto the 2005 instance under Management -> Legacy -> DTS. Then I ran a migration of the imported pacakge and that ran smoothly as well.

So now I would just like to see the result of the converted pacakage.

Any clues to what I do wrong?

Best regards

SUN

There is a file called 'MsDtsSrvr.ini.xml' in the 'Program Files\Microsoft SQL Server\90\DTS\Binn' folder on each machine with SSIS installed that tells the SSIS service what SQL Server instance to connect to for enumerating packages stored in the MSDB. By default, it is set as '.' which means the local server name, default instance.

In your case, you probably have either a named instance, or are trying to connect to a non-default instance. Basically, open that file, and change the ServerName element under the MSDB element to the name of the instance to connect to (in a clustered server case, use the virtual SQL instance name)...so, the file looks something like the following probably now:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

So, the part that I highlighted is what needs to be modified...just overwrite the '.' with the appropriate instance name and see if that works for you.

|||

Hi,

Thanks for the reply, Yes I installed both instances as named ones. So this seems to be the right solution. I'll test this out and see if it works on Monday. Thanks.

//SUN

|||

Tried the resolve and all worked well. Just make sure you type the instance with the server name ie:

{servername}\{instance name}

Typing just the instance name failed.

|||

More importantly after you make the change to the XML file. You have to make sure you stop and restart the IIS service(Control Panal->Administration Tools->Services). Only after doing this it starts working.

|||

Thanks for the info -

Why isn't this a config property through the management console or config manager?

|||

Having the same issue.

Tried to add server name, and server/instance to the file as suggested. Restarted IIS each time as suggest.

Still having problem connecting.

I can "see" and import into BIDS packages created by the Import/Export Wizard.

If SSIS doesn't support instances as suggested when trying to connect to SSIS, how to handle this. I have two instances of the DB, Development and Test. From there we would promote to production. But on the dev box, how would you suggest we have development and test versions of our SSIS packages?

|||

Thanks for the information.