Integration Services on a cluster

Question

Post reply

Integration Services on a cluster

Summer90

SSC-Dedicated

Points: 32864
More actions
August 1, 2012 at 7:27 am

#392871

I have a default instance on a WIn2008R2\SQL2008R2 cluster and I want to save and use an Integration Services package from a data import as a job. I created the job as a Integration Services package but when I go to open up Integration Services and click on the MSDB folder I get a time out error saying it hasn't been configured. In doing some research it appears I have to configure this file msdtssrvr.ini.XML to see both nodes of the cluster. Does anyone have any experience with this?

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

AlexSQLForums SSChampion Points: 14256 More actions · Answer 1

Markus (8/1/2012)

I have a default instance on a WIn2008R2\SQL2008R2 cluster and I want to save and use an Integration Services package from a data import as a job. I created the job as a Integration Services package but when I go to open up Integration Services and click on the MSDB folder I get a time out error saying it hasn't been configured. In doing some research it appears I have to configure this file msdtssrvr.ini.XML to see both nodes of the cluster. Does anyone have any experience with this?

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

GO TO:

Follow Steps (If not already setup) in "To configure Integration Services as a cluster resource"

and then "To configure the Integration Services Service and Package Store"

After all the changes your xml file should look like this:

<?xml version="1.0" encoding="utf-8"?>

<ServerName>YOURVIRTUALSERVERNAME</ServerName>

</Folder>

<Name>File System</Name>

<StorePath>YOURDRIVE:\Packages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

Alex S

sestell1 SSChampion Points: 10230 More actions · Answer 2

SSIS isn't really cluster aware. There's a work around using the config files to set it up on each node of the cluster so that it sees packages from all nodes in the cluster. This way no matter what node your instance is running on, it will be able to find your package on the node that it is saved in, but you probably will still have issues if the particular node the package is on is not available.

The easiest solution would be to instead store the SSIS packages on your SAN, and set up the jobs to execute them from that file system location.

We created a file share to keep the packages on, and created an SSIS proxy account that had access to that location. This made it easy to manage, but we occasionally had issues with the job reading the SSIS package xml when the network was under heavy load.

Summer90 SSC-Dedicated Points: 32864 More actions · Answer 3

Summer90

SSC-Dedicated

Points: 32864

August 1, 2012 at 8:24 am

#1520310

Thanks for both replies. I got it figured out before your replies.