Creating a Central SSIS Server
This is another section that applies only to the package deployment model. If you can, you should be changing your SSIS methods to take advantage of the project deployment model.
Many enterprises have so many packages that they decide to separate the service from SQL Server and place it on its own server. When you do this, you must still license the server just as if it were running SQL Server. The advantages of this separation are that your SSIS packages will not suffocate the SQL Server’s memory during a large load and you have a central management location. The disadvantages are that you must license the server separately and you add an added layer of complexity when you’re debugging packages. You have a fantastic way to easily scale packages by adding more memory to your central server, but you also create an added performance hit because all remote data must be copied over the network before entering the Data Flow buffer.
To create a centralized SSIS hub, you only need to modify the MsDtsSrvr.ini.xml file and restart the service. The service can read a UNC path like \ServerName\Share, and it can point to multiple remote servers. In the following example, the service enumerates packages from two servers, one that is local and another that is a named instance. After restarting the service, you will see a total of six folders to expand in Management Studio. We cover the Management Studio aspect of SSIS in much more detail later in Administering SSIS Topic.
<? xml version=”1.0″ encoding=”utf-8″ ?>
<Name>Server A MSDB</Name>
<Name>Server B MSDB</Name>
<Name>Server C MSDB</Name>
<Name>Server A File System</Name>
<Name<Server B File System</Name>
<Name>Server C File System</Name>
To schedule packages when using a centralized SSIS hub as in this example, you have two options. You can schedule your packages through SQL Server Agent or through a scheduling system like Task Scheduler from Windows. Because you’re already paying for a SQL Server license, it’s better to install SQL Server on your server and use Agent, because it gives you much more flexibility, as you will see later in Administering SSIS Topic. Keep in mind that packages run from the machine that executes the package. Therefore, if you have a package stored on Server A but execute it from Server B, it will use Server B’s resource. You can also store configuration tables and logging tables on this SQL Server to centralize its processing as well. Both scheduling mechanisms are covered later in Administering SSIS Topic.