Or in other words, what do I need to install during SQL Server setup in order to end up with a fully operational SSIS server with a minimal surface area configuration? I came up with this blog post after I participated in the following thread at the MSDN forum: "What's the minimum configuration to run dtexec on windows 2003 server?" DTEXEC is the command line utility used behind the scenes to run SSIS packages. The question asker thought only the client tools needed to be installed, but I was not sure of this. Time to investigate!
Methodology
I created two easy packages, called SimpleTest and AdvancedTest. SimpleTest reads a flat file, adds a timestamp and writes the results to another flat file.
The AdvancedTest package reads the same flat file, but uses a term extraction component to do a term based analysis and it writes the results to a flat file. This package is used to see if there’s any difference when an Enterprise-only component is used (see Features Supported by the Editions of SQL Server 2008 R2).
The server is a Windows 2008R2 64-bit server. The edition of SQL Server being installed is SQL Server 2008R2 Developer edition.
To test if DTEXEC is installed and if it works correctly, the following commands are used in a command window:
dtexec /F “c:SimpleTest.dtsx” > SimpleLog.txt
and
dtexec /F “c:AdvancedTest.dtsx” > AdvancedLog.txt
These commands execute a package located on the file system and output the results to a text file.
Client Tools only
The first install is the client tools, with which I mean BIDS (Business Intelligence Development Studio) and SSMS (SQL Server Management Studio). Later on I learned the question asker meant everything listed under Shared Features, which is a whole lot more than just the client tools. First I installed the Management Tools only.
I took a look in the Program Folders, but I only found a 32-bit DTEXEC. Is this enough to run a package?
When the DTEXEC statements are executed in a command window, we are greeted with the following error message:
Description: To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher.
So no luck with this method. When DTEXECUI is launched, everything is grayed out and another straightforward error message tells us we really do have to install Integration Services.
However, the Import/Export wizard is also installed. When I import a flat file to another flat file – the workflow used by the SimpleTest package – the wizard executes successfully:
The only reason DTEXEC was installed is to run the Import/Export wizard. Installing BIDS as well doesn’t solve the issue, as expected. You can run packages within BIDS, but I guess nobody is excited about staying up all night in order to manually kick off SSIS packages. So basically we have installed a lightweight development environment, but not a server.
SQL Server only
Stubborn as I am, I ignore the logical error messages I got earlier and I install the SQL Server database engine, without any shared feature whatsoever. This time the 64-bit DTEXEC is installed. We're making progress. When I start the SimpleTest package, the log shows the following:
The component "(DER) Add timestamp" (38) cannot run on installed (64-bit) of Integration Services. It requires Standard Edition (64-bit) or higher.
The error doesn't make much sense, since I haven't installed Integration Services at all and Developer Edition is a higher edition than Standard (in functionality, not in price). Running the AdvancedTest package results in the following:
Warning: 2013-01-16 14:49:02.56 Code: 0xC0048000
Source: (DFT) Throw data around (DFT) Throw data around (SSIS.Pipeline)
Description: The registry key "SOFTWAREClassesCLSID{119D450D-E2A3-4DB0-A7BC-ACDE2536673E}DTSInfo" cannot be opened.
End Warning
Warning: 2013-01-16 14:49:02.56 Code: 0x8004801E
Source: (DFT) Throw data around (DFT) Throw data around (SSIS.Pipeline)
Description: Cannot find the "CurrentVersion" value for component {119D450D-E2A3-4DB0-A7BC-ACDE2536673E}. The CurrentVersion value for the component cannot be located. This error occurs if the component has not set its registry information to contain a CurrentVersion value in the DTSInfo section. This message occurs during component development, or when the component is used in a package, if the component is not registered properly.
End Warning
Error: 2013-01-16 14:49:02.56 Code: 0xC0048020
Source: (DFT) Throw data around (DFT) Throw data around (SSIS.Pipeline)
Description: The version of component "(TEX) Extraction FirstName" (77) is not compatible with this version of the DataFlow.
End Error
Error: 2013-01-16 14:49:02.56 Code: 0xC0048020
Source: (DFT) Throw data around SSIS.Pipeline
Description: The version of component "(TEX) Extraction FirstName" (77) is not compatible with this version of the DataFlow.
End Error
...
Error: 2013-01-16 14:49:02.56 Code: 0xC0048021
Source: (DFT) Throw data around (TEX) Extraction FirstName [77]
Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "".
End Error
Error: 2013-01-16 14:49:02.56 Code: 0xC0047017
Source: (DFT) Throw data around SSIS.Pipeline
Description: component "(TEX) Extraction FirstName" (77) failed validation and returned error code 0xC0048021.
End Error
First we get a whole bunch of warning related to the registry, followed by errors related to the Term Extraction component, leading to a validation failure at the end. The Enterprise-only component leads to different errors, but the result is the same: we cannot run SSIS packages using DTEXEC.
So why is DTEXEC installed? First of all, to run the 64-bit version of the Import/Export wizard, but also to support maintenance plans, who use SSIS behind the scenes. Since SQL Server 2005 sp2 or SQL Server 2008 CU1/sp1, it is not necessary to install SSIS to create maintenance plans. Read more about this in the blog post Do maintenance plans require SSIS? by Tibor Karaszi (blog | twitter).
Integration Services only
Now let's try to do the sane thing here and install SSIS, without any other option specified.
DTEXEC is installed, as expected of course, alongside the Integrations Services service. Running DTEXEC through the command line gives us the result we were hoping for:
Success at last! DTEXECUI isn’t installed, indicating this is a client component only. This is OK, because you probably won’t ever use it on a server.
One final question remains: do we need the SSIS service? The answer is short: no. According to Microsoft, the SSIS service only extends the functionality of SSMS, in the sense that it manages the storage of SSIS packages and that it monitors the running packages. So you can safely stop the service. However, you need to disable it as well; otherwise the service is started again once you run a package.
Conclusion
Only when you want to set up a dedicated SSIS Server will you need to install Integration Services on its own, and if you do this you'll need to store your packages on the file system. You can disable the Integration Services service; it is not needed to execute a package. You also might want to install the client connectivity tools as well and any other component you might need to connect to your sources.
