A couple of days ago one of my client asked me what needs to be install on the ETL server in order to execute SSIS Packages. My first though was to install the SSIS instance but after some other thoughs i decided to look into it in more details.
Here is a summary of my research on the subject – the goal was to install the minimum of component as possible as we are in a PCI environnent which must be highly secure.
dtexec is the command prompt utility that needs to be install to run your SSIS packages.
dtexec is installed when you install SQL Server DB Engine. However this is *NOT* the full version of dtexec. It is only install in order to use:
– Import and export data wizard (within SQL Server)
– Support maintenance plans (Since SQL Server 2005 sp2 or SQL Server 2008 CU1/sp1, it is not necessary to install SSIS to create maintenance plans)
=> So You *cannot* run your own SSIS packages with this version of dtexec
In order to fully use dtexec you need to install « Integration services » feature. Installing this feature install the necessary binaries (and also the service) to run your own packages.
– dtexecui is not install when installing only Integration services (it’s a client component tool)
– You can stop the SSIS Service to run packages – the SSIS service only extends the functionality of SSMS
– You also might need to install the « client Tools connectivity » feature tools as well and any other components you might need to connect to your sources.
Client Tools includes components for communication between clients and servers, including network libraries for DB-Library, OLEDB for OLAP, ODBC, ADODB, and ADOMD+
In a 64-Bit computer you need to install BIDS or SSMS in order to also get dtexec in 32-Bit
So when installing SSIS in a 64-bit computer to ensure a 32-bit installation is also required, you must install SSIS and BIDS. Installing SSIS and not BIDS will only install the 64-bit engine.
If you have a 64-Bit computer with both 32-Bit and 64-Bit dtexec installed:
– when using dtexecui it will use the 32-Bit version (it’s a 32-Bit tool)
– when using the dtexec it will use the 32-Bit by default (because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version)
– when using SQL Agent it will use the 64-Bit by default ( because SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility)
You do need to install the « Integration services » feature in order to run SSIS packages. This feature is enough to be installed on its own to run packages and you can even stop the related service. (you do not need the DB Engine services feature).
However, if you have a 64-Bit computer and you need to run packages in 32-Bit mode then you also need to install BIDS or SSMS (which install dtexec 32-Bit)
Blog – When is DTEXEC installed?: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4f29e3a3-fc8a-465c-8acc-12ecedfcb24c/whats-the-minimum-configuration-to-run-dtexec-on-windows-2003-server-32bit?forum=sqlintegrationservices
MS – SQL Server installation features: https://technet.microsoft.com/en-us/library/ms143786.aspx
MS – 64 bit Considerations for Integration Services: https://msdn.microsoft.com/en-us/library/ms141766.aspx
MS – Description of SSIS Services: https://support.microsoft.com/en-us/kb/942176?wa=wsignin1.0