Agent Jobs

Using SSIS for multiple SQL Instances in SQL 2014

In this example we have a generic database server with SQL Server 2014. There are 6 instances.

DEFAULT

SQLSHARE01

SQLSHARE02

SQLSHARE03

SQLSHARE04

SQLSHARE05

Now; according to SQL 2014; you are still unable to install SSIS on a Multi-Instance SQL Database Server. Ok this is all well and good, but most of the time you’ll have least some databases across the instances which requires the SSIS Service to handle it’s ETL automations.

Ok; so what do you do in this case?

It’s no problem to have a single SSIS install which drives the SSIS processes across all other instances, but how do you manage the SSIS packages in each MSDB?

The classic answer to this ( which is still current today as it was in former SQL versions ) is to edit the config file for SSIS. In my case it’s located here:
D:Program FilesMicrosoft SQL Server120DTSBinn

The actual file name is called this: MsDtsSrvr.ini You’ll notice it’s basically an XML file with extension .ini

In any event; here’s what you’ll need to edit:

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

<DtsServiceConfiguration xmlns:xsd="<a href="http://www.w3.org/2001/XMLSchema"">http://www.w3.org/2001/XMLSchema"</a&gt; xmlns:xsi="<a href="http://www.w3.org/2001/XMLSchema-instance">">http://www.w3.org/2001/XMLSchema-instance"></a&gt;

<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

<TopLevelFolders>

<strong><Folder xsi:type="SqlServerFolder"></strong>

<strong> <Name>MSDB</Name></strong>

<strong> <ServerName>.</ServerName></strong>

<strong> </Folder></strong>

<Folder xsi:type="FileSystemFolder">

<Name>File System</Name>

<StorePath>..Packages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

So what do you need to edit exactly? Just the following lines from the over all code.

<Folder xsi:type="SqlServerFolder">

<Name><strong>MSDB MyOtherInstnaceNameHere</strong></Name>

<ServerName><strong>. MyOtherInstnaceNameHere</strong></ServerName>

</Folder>

As you can see I just used MSDB SQLSHARE01, …02, …03 etc. This perfectly denotes where to find the MSDB database packages in the other instances.

Then simply paste it back into the original script like the following: ( I gave additional edits to represent every instance in my server ).

Once you do this; you’ll be able to manage each package set from each database instance. This is still classified as a work around by the way. It’s not the end-all-be-all solution until Microsoft makes each instance capable have carrying it’s own SSIS Service independent of the other.

You’ll see the folders for each MSDB under the b-tree folder structure under the SSIS portion of Management Studio.

Anyway; hope this is helpful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s