Wednesday 20 March 2013

SSIS Windows Service Failing


I encountered this issue with the SSIS windows service failing.
It was generating these errors at each attempt to run the service.


The SQL Server Integration Services service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

....

A timeout was reached (30000 milliseconds) while waiting for the SQL Server Integration Services service to connect.


Log Name:      Application
Source:        Microsoft-Windows-User Profiles Service

Event ID:      1530
Task Category: None
Level:         Warning
Keywords:      Classic

Description:
Windows detected your registry file is still in use by other applications or services. The file will be unloaded now. The applications or services that hold your registry file may not function properly afterwards. 




There were several possible reasons suggested here
http://connect.microsoft.com/SQLServer/feedback/details/125768/sql-server-integration-services-service-failed-to-start-after-installing-sp1

but for this case it was resolved by adding this new entry in the config files mentioned here http://support.microsoft.com/kb/918644 Method 1

    <runtime>       
    <generatePublisherEvidence enabled="false"/>   
    </runtime>

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\
    msdtssrvr.exe.config
    DTExec.exe.config
    Dtshost.exe.config
    DtsDebugHost.exe.config
    DTUtil.exe.config

Monday 18 March 2013

SQL Server Finding Timeouts


You need to create a table that contains your trace data.

You can import this from your .trc file using
Or if you used Sql Profiler save the trace data File -> Save As -> Trace Table

Running the following command you can identify the process that had not completed.

SQL Server 2008 R2 Install Complete Management Tools


To install the Complete  Managament Tools on an existing Sql Server 2008 R2 run the following command.

setup.exe /FEATURES=Tools /Q /INDICATEPROGRESS /ACTION=Install /INSTANCENAME=MyInstanceName /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT=”NT AUTHORITY\Local System” /IACCEPTSQLSERVERLICENSETERMS