Tuesday, April 5, 2016

Issue in expanding MSDB packages in SSIS


When you are trying to expand MSDB folder in Integration services. if you are facing below error.




TITLE: Import Package
------------------------------

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in %SQL_PRODUCT_SHORT_NAME% Books Online.

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2].  (MsDtsSrvr)

------------------------------
ADDITIONAL INFORMATION:

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Server Native Client 11.0)


Solution : you need to configure Integration Services service 

you will have to modify the configuration file if your packages are stored in a named instance or a remote instance of Database Engine.

Configuration file : MsDtsSrvr.ini.xml

Location :  %ProgramFiles%\Microsoft SQL Server\120\DTS\Binn