Sequal Server.Com

After SQL 2000 to SQL 2008 upgrade in cluster environment, SSIS service didnt install automatically. Upgrade wont install SSIS package while upgrade. It should be installed manually on both node manually. Install standalone SSIS service on both node and set auto start.

As per Microsoft recommendation, SSIS service should not be in cluster resource but we can add resource.

After installation, i had faced the below issues.

Issue 1

failed to retrieve data for this request. microsoft.sqlserver.management.sdk.sfc

After installation, i had faced issues while trying to connect the SSIS package in cluster environment.

By default, SSIS package is always trying to connect the default instance in the node. I had cluster environment so we need to modify configuration file in SSIS package configuration file.

you may also get below errors


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

The SQL Server specified in Integration Services 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 Server 2008 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, 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]. (MsDtsSvr).

Solution

1. Navigate this location \Program Files\Microsoft SQL Server\100\DTS\Binn\
2. Edit MsDtsSrvr.ini.xml in notepad and change server name "." to Cluster name.

< xsd="http://www.w3.org/2001/XMLSchema" xsi="http://www.w3.org/2001/XMLSchema-instance">
<>true < /stopexecutingpackagesonshutdown>
<>
< type="SqlServerFolder">
<>MSDB < /name>
<>ServerName\InstanceName < /servername>
< /folder>
< type="FileSystemFolder">
<>File System < /name>
<>..\Packages < /storepath>
< /folder>
< /toplevelfolders>
< /dtsserviceconfiguration>

3. Save XML file and restart SSIS service


Issue 2
"Connect to SSIS Service on machine failed: Access is denied"

While trying to connect the SSIS package from client machine. I can able to connect the SSIS service in node.

We need to add below steps in Local group policy and windows component service.

1. Add users in Distributed COM local windows Users group
2. Start -> Run -> %windir%\system32\Com\comexp.msc to launch Component Services
or program files -> Administrative tools -> Component Services
3. Expend Component Services\Computers\My Computer\DCOM Config
4. Right click on MsDtsServer node and select properties
5. Navigate Security page menu
6. Click Edit button in “Launch and Activation Permissions” section.
7. Add users and provide all permissions. Press ok to complete.
8. Click Edit button on “Launch Permissions” section
9. Add users and provide all permissions. Press ok to complete.
10. Restart SSIS Service