Advertisements

Archive

Posts Tagged ‘SSIS’

SQL Server Integration Services Issue (Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum))

January 27, 2010 Leave a comment

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.
Named Pipes Provider: Could not open a connection to SQL Server [2].  (MsDtsSrvr)

——————————

I have used SSIS for ETL process few times; but on stand alone instances only.  Never tried to do SSIS in a Cluster environment.  This particular issue can happen in a standalone environment also because SSIS services by default cannot handle named instances.

SSIS also is not cluster-aware and is single instance application (i.e. only one copy of SSIS can run on server at a time).  I read the reference articles [1] and [2] to investigate my options.  I decided to do following to make SSIS Packages management and controlled per SQL instance in cluster and named instance environment.  Current configuration is Active-Active 2-Node Cluster with both instances using Named Instances.

1. Go to C:\Program Files\Micrsoft SQL Server\90\DTS\Binn\ directory.
2. Open MsDtsSrvr.ini.xml file.

You will something similar to following:

<?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>

We need to do couple things in the file for it to work with our cluster:

  • Add the information for each SQL Instance in your Cluster (e.g. for me SQLCluster01p and SQLCluster02p)
  • Update it to make sure it referring to Named Instance instead of one default instance (e.g. for me Instance01 and Instance02)

So I modify the file to following:

<?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_SQLCluster01p_Instance01</Name>
         <ServerName>SQLCluster01p\Instance01</ServerName>
      </Folder>
      <Folder xsi:type="SqlServerFolder">
         <Name>MSDB_SQLCluster02p_Instance02</Name>
         <ServerName>SQLCluster02p\Instance02</ServerName>
      </Folder>
      <Folder xsi:type="FileSystemFolder">
         <Name>File System</Name>
         <StorePath>..\Packages</StorePath>
      </Folder>
   </TopLevelFolders>
</DtsServiceConfiguration>

If you notice I changed the first instance and added a 2nd instance.  Now when you launch SSIS on either cluster you will see something like this in your SSMS:

Please note even though we did change the name of the SQLServer folder; the information is still being stored in MSDB database on the instance entered in the ServerName property.

Now I did it this way to allow me to manage the SSIS packages remotely and monitor their execution.  But if you don’t need to worry about stopping SSIS packages; then you can simply store it on a network location or in SSIS Package Storage.

References:
[1] Configuring the Integration Services in Cluster Environment, Link.
[2] Configuring the Integration Services Service, Link.
[3] Different ways to execute a SQL Server SSIS package, Link.
[4] Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service, Link.

Advertisements

"No description found" Error when modifying Maintenance Plans

February 19, 2009 Leave a comment

When you are trying to modify a maintenance plan; sometimes you might get “No Description Found” error and you cannot do any work in SSIS when that happens.

Everything was working properly for me until I had to install Microsoft Visio 2003 and I couldn’t update my maintenance plans any more.

In the reference link it explains because of MSXML DLL issue in Visio 2003 RTM edition it causes the issue with SSIS packages. Installing the newest Service Pack for Visio resolves the issue after reboot.

So this is not an issue with SQL Server Egine or SSIS Engine but just the Sql Server Management Studio (SSMS).

I also read an post on SQL Server Central where someone stated they got it to work just after running following commands also:

Regsvr32 “C:\WINDOWS\system32\msxml.dll”
Regsvr32 “C:\WINDOWS\system32\msxml3.dll”

You still have to reboot computer; I wanted to get the updates for Visio anyhow, so I didn’t get chance to try that.

Ref Link: http://support.microsoft.com/kb/922546

%d bloggers like this: