In this example we have a generic database server with SQL Server 2014. There are 6 instances.
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> xmlns:xsi="<a href="http://www.w3.org/2001/XMLSchema-instance">">http://www.w3.org/2001/XMLSchema-instance"></a> <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>
So what do you need to edit exactly? Just take the following lines… Copy & Paste, and edit them for each instance you want to manage.
<Folder xsi:type="SqlServerFolder"> <Name>MSDB MyInstanceName</Name> <ServerName>. MyInstanceName</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.
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.
Categories: Agent Jobs, Alerts, AlwaysOn, Automations, Backup & Restore, Big Data, Cluster, Command Prompt, Configuration, Database Mail, Database Mirroring, ELI5, Error Solutions, Failover, Failover Cluster, Featured, Folder System, Forensics & Auditing, High Availability, How To, Installations, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds