"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)"

In SQL Server Management Studio this error appears as:

"Cannot connect to ."

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)"

From sqlcmd, possible timeout errors include:

"SQL Network Interfaces: Error Locating Server/Instance Specified"

"Sqlcmd: Error: Microsoft SQL Server Native Client : Client unable to establish connection."

"Sqlcmd: Error: Microsoft SQL Server Native Client : Login timeout expired."

"Could not open a connection to SQL Server"

"An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."

resolution link:
============================
http://msdn.microsoft.com/en-us/library/ms190181.aspx

how many SSIS packages can run in parallel

http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx
http://msdn.microsoft.com/en-us/library/dd537533.aspx

best practices for SSIS package performance

http://blogs.msdn.com/michen/archive/2007/04/24/sqlperf-blog-on-ssis-performance.aspx

The performance turning videos here may also be helpful:

http://sqlcat.com/presentations/archive/2009/05/02/designing-and-tuning-for-performance-your-ssis-packages-in-the-enterprise-sql-video-series.aspx

Importing Excel Cell data via SSIS

check this blog

Dougbert.com blog post

(http://dougbert.com/blogs/dougbert/archive/2008/06/18/excel-in-integration-services-part-2-of-3-tables-and-data-types.aspx )

SSIS Packages Running slow

The workaround is to place this entry into the ‘hosts’ file on the Windows Server 2003 machine :

<<<<<
#Fix for slow running SSIS packages
127.0.0.1 crl.microsoft.com


If you're using 2005, this might have something to do with the certificate revocation list.

In 2008 its modified the dtexec configuration to not check the list. The customer could do the same for 2005 by manually editing the file.

See Michael's blog post for more information:

http://blogs.msdn.com/michen/archive/2008/03/14/configuring-net-for-running-ssis-packages-from-custom-applications.aspx

best practices surrounding team development with SSIS

http://blogs.conchango.com/jamiethomson/archive/2007/08/06/SSIS_3A00_-Team-Development-Experiences.aspx

"Exception from HRESULT: 0xC0010014" Foreach Loop Container SSIS

If we create a new Integration Services Project on the servers BI Visual Studio,
chose the Foreach Loop Container,
click edit on it
and click on the three dots on Expressions under Collection we get an error message saying:

"Exception from HRESULT: 0xC0010014".

Resolution:
============================
issue was resolved by registering ‘ForEachFileEnumerator.dll’ using regsvr32.exe:

c:\program files (x86)\microsoft sql server\90\dts\foreachenumerators\ForEachFileEnumerator.dll

As per this link:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8cf03f7d-f170-40db-98fa-af151d5b5269

Microsoft Connectors for Oracle and Teradata by Attunity

http://www.microsoft.com/downloads/details.aspx?FamilyID=d9cb21fe-32e9-4d34-a381-6f9231d84f1e&DisplayLang=en


Oracle Provider for OLE DB
http://www.oracle.com/technology/tech/windows/ole_db/index.html

"The timeout period elapsed prior to completion of the operation or the server is not responding"

SQL SERVER: 2005
when running a view or query with very long execution time, you may see the
following error message from Management Studio.

---------------------------
Microsoft SQL Server Management Studio
---------------------------
SQL Execution Error.
--
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
---------------------------
OK Help
---------------------------


From Query Analyzer:

Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation
or the server is not responding.

Resolution:
====================
1. Please check the Execution time-out Settings first. Make sure those are 0.
a. Open SSMS (SQL SERVER Management Studio). Tools->Options->Query Execution->SQL
SERVER->General: Execution time-out. Set to 0.
b. Open a new Query window from SSMS. Query->Query Options->Execution->General:
Execution time-out. Set to 0.
c. On SSMS, File->new->Database Engine Query->Options->[Connection
Properties]->Execution time-out. Set to 0.

d. Please reopen SSMS.
e. If issue persists - go to step 2:

2. Please open sqlcmd connection and try to execute the same query. Based on the
error message you receive there you may continue troubleshooting further. It may
involve collecting PSSDIAG / trace / dumps as per the requirement.

"no such interface supported" and SSIS

When we try to connect to SQL Server 2005 Integration services using SQL Server
Management Studio, we get an error -

Connect to SSIS Service on machine failed:
No such interface supported


Full technical Details:
=================

Cannot connect to MySSISServer.

===================================

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

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

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo,
Request request)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.Valida
teConnection(UIConnectionInfo ci, IServerType server)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

Connect to SSIS Service on machine "MySSISServer" failed:
No such interface supported
.


------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server,
String& serverVersion)
at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object
ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo,
Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo,
Request request)

===================================

Connect to SSIS Service on machine "MySSISServer" failed:
No such interface supported
.


------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String
bstrMachineName, String& serverVersion)
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server,
String& serverVersion)


Solution:
=========================
Root Cause:
==========
We may get the above error if the DLL MsDtsSrvrUtil.dll, located under Installation Folder>\90\DTS\Binn\MsDtsSrvrUtil.dll is not Registered successfully
on the machine (SSIS Server).

The most common cause of this error is -

SQL Server 2005 Service pack or CU setup failed to re-register the above DLL (we
can review the setup logs to verify)

During setup of a SQL Server 2005 Service pack or CU, setup tries to unregister and
re-register relevant DLLs on the machine and we have seen certain cases when this
DLL (and other DLLs) was not re-registered successfully.


Resolution:
==========

Re-register the DLL MsDtsSrvrUtil.dll and DTS.dll like below -

regsvr32.exe "\90\DTS\Binn\MsDtsSrvrUtil.dll"

We don't require SSIS service restart or to re-open SSMS. After re-registering it
starts working.

"SQL Server specified in SSIS Service configuration is not present"

PROBLEM: After connecting to SSIS (SQL Server Integration Services) from SSMS (SQL
Server Management Studio), when you expand MSDB under Stored Packages to see list
of stored packages, you may get below error.




ERROR MESSAGE:

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:
The SQL server 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 Server 2005 Books Online.

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.
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
(MsDtsSrvr)
------------------------------
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.
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
(Microsoft SQL Native Client)
------------------------------
BUTTONS:
OK
------------------------------

Resolution:
====================
CAUSE: This problem may happen because below multiple reasons.

1. Registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile" not
existing or not pointing to correct path of the file MsDtsSrvr.ini.xml. Default
path of this file is "%ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml".

2. The content of file MsDtsSrvr.ini.xml typically looks like below.


xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
true


MSDB
.


File System
..\Packages




If the Packages folder path specified between StorePath tags above is incorrect,
you may get the same error.

3. If the ServerName specified between ServerName tags above is incorrect, you may
get the same error. If the SQL is in cluster, specify the virtual name.

RESOLUTION: In our case, we replaced '.' with the netbios name of the server and it
resolved the issue.

SQL Server Management Studio to SSIS with 'Error loading type library/DLL'

TITLE: Connect to Server
------------------------------
Cannot connect to xxxx.
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Connect to SSIS Service on machine "xxxx" failed:
Error loading type library/DLL.
-----------------------------
Connect to SSIS Service on machine "xxxx" failed:
Error loading type library/DLL.
------------------------------
BUTTONS:
OK
------------------------------


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:
Library not registered. (Exception from HRESULT: 0x8002801D
(TYPE_E_LIBNOTREGISTERED)) (Microsoft.SqlServer.DTSRuntimeWrap)
------------------------------
Library not registered. (Exception from HRESULT: 0x8002801D
(TYPE_E_LIBNOTREGISTERED)) (Microsoft.SqlServer.DTSRuntimeWrap)
------------------------------
BUTTONS:
OK
------------------------------

Solution:
======================
We Need to Un-Register both 32Bit and 64Bit of Dts.dll and Un-Register both
32Bit and 64Bit of MsDtsSrvrUtil.dll and Re-Register both 32Bit and 64Bit of
Dts.dll and both 32Bit and 64Bit of MsDtsSrvrUtil.dll

==================
In another issue of 32-bit SQL Server installation on a 32-bit windows 2003, we were
getting exactly the same error.

Resolution: Resolution in that issue was also same as the above.

Registering the Dts.dll and MsDtsSrvrUtil.dll from %PROGRAM FILES%\Microsoft SQL
Server\90\DTS\Binn folder.

=================
Cause: During each service pack setup, we unregister the older versions of these
DLLs (along with a lot of others), copy the new ones and re-register them. The
registering phase may have failed on these files. The installation log of the
service pack can confirm us that.

"Failed to retrieve data for this request" "Error loading type library dll"

Component Registration
To resolve error loading typelibrary error when connecting to SSIS from Management
Studio we did following on both nodes of the cluster

1. Run following commands to make sure 32bit and 64bit versions of
MsDtsSrvrUtil.dll are registered properly.

regsvr32 /u C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvrUtil.dll
regsvr32 /u C:\Program Files (x86)\Microsoft SQL
Server\90\DTS\Binn\MsDtsSrvrUtil.dll
regsvr32 C:\Program Files (x86)\Microsoft SQL
Server\90\DTS\BINN\MsDtsSrvrUtil.dll
regsvr32 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvrUtil.dll

2. Run following commands to make sure 32bit and 64bit versions of DTS.dll are
registered properly.

regsvr32 /u C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll
regsvr32 /u C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTS.dl
regsvr32 C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTS.dll
regsvr32 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll

Enabling Users to Connect SSIS from Remote Computers
By default you cannot connect to SSIS service from a remote computer running
Management Studio. To be able to connect SSIS from remote computer, we did
following:

1. Open Component Services\Computers\My Computer\Properties\Default Properties,
make sure the option “Enable Distributed COM on this
computer” is checked.

2. Open Component Services\Computers\My Computer\DCOM Config\MsDtsServer
Properties. In the security page verify that under “Launch and Activate
Permissions”, Remote Launch and Remote Activation are enabled for all users who
will be connecting SSIS service remotely.

3. Open Computer Management\Local Users and Groups\Groups and make sure all users
who will be connecting SSIS service remotely are added into “Distributed COM users”
group.

4. If there’s a firewall between the client computer where you run Management
Studio and SSIS server you also need to make sure the firewall is configured for
DCOM. Refer to below article for more information on this.

Using Distributed COM with Firewalls
http://msdn.microsoft.com/library/en-us/dndcom/html/msdn_dcomfirewall.asp

Setting the backend SQL Server for SSIS
By default SSIS tries to use the local default instance as backend server. If your
SQL Server has a different name, you need to reconfigure SSIS to connect to your
SQL Server instance. We did following on both nodes of the cluster to configure
same SQL Server instance as the backend database for both SSIS service on both
nodes.

1. Open the configuration file C:\Program Files\Microsoft SQL
Server\90\DTS\Binn\MsDtsSrvr.ini.xml in notepad.

2. Find following piece of information in the file


MSDB
.


3. Change the server name value (between tags) to point to the actual
SQL Server name you will use.

4. Restart SSIS Service.

Clustering SSIS Service
By default SSIS service is installed on individual nodes and runs as a separate
service on both nodes. If you want clustering capabilities (failover etc.) you may
configure SSIS service to be a clustered resource. Please follow steps in the BOL
link below to configure SSIS on a cluster.

Installing SQL Server 2005 Components > Installing SQL Server Components How-to
Topics > Integration Services Installation How-to Topics > How to: Configure
Integration Services on a Cluster
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/223c3a59-84c3-4f60-be98-a8daf69e9473.htm