SSIS and SQL Server Instances

by Guest 9 Jun 2005 14:00

This is something you may run into, especially if you're running Integration Services on a machine with multiple instances and at least one of them is a SQL 2K instance.

The Integration Services server does not support instances, but is aware of SQL Server instances and can be configured to reference a particular instance. Here's how.

There is a file called MsDtsSrvr.ini.xml in the %PROGRAM FILES%\Microsoft SQL Server\90\DTS\Binn folder. If you open that file you'll see something like this:

<?xml version="1.0" encoding="utf-8"?>
<DTSSERVICECONFIGURATION xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<STOPEXECUTINGPACKAGESONSHUTDOWN>true</STOPEXECUTINGPACKAGESONSHUTDOWN>
<TOPLEVELFOLDERS>
<FOLDER xsi:type="SqlServerFolder">
<NAME>MSDBlt;</NAME>
<SERVERNAME>.\YUKONB3</SERVERNAME>
</FOLDER>
<FOLDER xsi:type="FileSystemFolder">
<NAME>File System</NAME>
<STOREPATH>..\Packages</STOREPATH>
</FOLDER>
</TOPLEVELFOLDERS>
</DTSSERVICECONFIGURATION>

There are a few interesting settings here, but this one determines which server the IS server will reference for enumerating packages in MSDB:

<SERVERNAME>.\YUKONB3</SERVERNAME>

This tells the server to reference the local server instance named "YUKONB3". It can just as well be something like:

<SERVERNAME>.</SERVERNAME> or <SERVERNAME>(local)</SERVERNAME> or <SERVERNAME>MYSERVERNAME</SERVERNAME>

If you make a change to this configuration file, make sure you restart the service in SQL Server Configuration Manager or the services control panel app.

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Widget Amazon Book not found.

Object reference not set to an instance of an object.X

RecentComments

Comment RSS