How do I create Data Source Data Marts on SQL Server instances without a default Instance? (NETIQKB72245)

  • 7772245
  • 21-Apr-2010
  • 09-Dec-2010

Environment

NetIQ Analysis Center 2.7
NetIQ Analysis Center 2.7.5

Situation

Add Data Source Fails when the Data Mart Server Contains Only a SQL Named Instance.
Analysis Center may display an error message when trying to add a Data Source
Add Data Source Wizard notification stating the following, "Default Instance not found on this Data Mart SQL Server."
Pre-check data source is not yet created
How do I create Data Source Data Marts on SQL Server instances without a default Instance?

Resolution

By default, Integration Services packages for the ETL Data Source Data Mart expect to install on the MSDB of the SQL default instance.  To circumvent this limitation, while entering the Data Mart server name, ensure that SQL Server Integration Services is installed and running on this Data Mart SQL Server, and change the appropriate xml tag in the MsDtsSrvr.ini.xml file.

Non-Clustered Environments

If the default instance of SQL Server is not found on the Data Mart SQL Server, to create a single Data Mart on an instance of the Data Mart SQL Server, perform these steps:

  1. Change the xml tag in the MsDtsSrvr.ini.xml file located in the Microsoft SQL Server\90\DTS\Binn folder using the following format:

    <ServerName>Server\Instance<\ServerName> - Instance refers to the instance on which you are going to create the Data Mart.

  2. Restart the SQL Server Integration Services.

To create more than one Data Mart on different instances of the Data Mart SQL Server:

  1. Copy & paste another instance of the following xml tags into the MsDtsSrvr.ini.xml file located in the Microsoft SQL Server\90\DTS\Binn folder:

    <Folder xsi:type="SqlServerFolder">
    <Name>MSDB</Name>
    <ServerName>SERVER\INSTANCE1</ServerName>
    </Folder>

  2. Change the content of the <Name> and <ServerName> tags in the following format:

    <Name>Folder name<\Name> - Folder refers to the SSIS packages folder.
    <ServerName>Server\Instance<\ServerName> - Instance refers to the instance on which you are going to create the Data Mart.

  3. Restart the SQL Server Integration Services.

After adding this Data Source, edit the command in all of the ETL SQL Job steps for the associated Data Mart.
Replace MSDB with the Folder name in the following format: dtexec /DTS "\Folder name\..


Clustered Environments

Steps to create a single Data Mart on an instance of the Data Mart SQL Server are:

  1. Install SQL Server Integration Services and change the xml tag in the MsDtsSrvr.ini.xml file located in the Microsoft SQL Server\90\DTS\Binn folder using the following format:

    If the Data Mart Virtual SQL Server is a named instance: <ServerName>Virtual SQL Server\Instance name</ServerName>
    If the Data Mart Virtual SQL Server is a default instance name: <ServerName>Virtual SQL Server Instance name</ServerName>

    Instance refers to the instance on which you are going to create the Data Mart.

  2. Restart the SQL Server Integration Services.

Cause

This is a known issue related to the configuration of Microsoft SQL Server Integration Services on a server that doesn't contain a default instance of SQL Server.  By default unless otherwise specified the SQL Server Integration Services packages required for the ETL job are wanting to be installed in the default instance of the MSDB database

 

Additional Information

Formerly known as NETIQKB72245

Perform these steps in all the nodes where the Data Mart SQL Server will failover.

To create multiple Data Marts on different instances of the Data Mart SQL Server, install SQL Server Integration Services:

  1. Copy and paste another instance of the following xml tag into the MsDtsSrvr.ini.xml file located in the Microsoft SQL Server\90\DTS\Binn folder:

    <Folder xsi:type="SqlServerFolder">
    <Name>MSDB</Name>
    <ServerName>.</ServerName>
    </Folder>

  2. Change the content of the <Name> and <ServerName> tags in the following format:

    <Name>Folder name</Name>.

    Folder refers to the SSIS packages folder.

    If the Data Mart Virtual SQL Server is a named instance:

    <ServerName>Virtual SQL Server\Instance name</ServerName>

    Instance refers to the instance on which you are going to create the Data Mart.

    If the Data Mart Virtual SQL Server is a default instance name:

    <ServerName>Virtual SQL Server Instance name</ServerName>

    Instance refers to the instance on which you are going to create the Data Mart.

  3. Restart the SQL Server Integration Services.

After adding this Data Source, update all the ETL Job steps for the associated Data Mart and replace MSDB with the Folder name in the following format:

dtexec /DTS "\Folder name\..