Who Needs SSIS?
In the forums I frequent I see quite a few posts asking if SQL Server Integration Services should be installed and if it is needed for a “typical” installation. SQL Server Integration Services (SSIS) is the replacement to SQL’s Data Transformation Services, DTS, and is Microsoft’s primary tool for extracting, transforming, and loading (ETL) data to or from SQL. Along with all its ETL responsibilities, SSIS is also responsible for all SQL 2005/2008 created or upgraded maintenance plans.
This fact may have escaped those who upgraded their SQL 2000 and earlier instances to SQL 2005/2008 and might seem irrelevant, as these maintenance plans still run as legacy objects in current versions of SQL. Unfortunately some of the tasks created in the non-SSIS maintenance plans are marked for deprecation in future versions of SQL the need to upgrade might be imminent:
If you upgraded from earlier version of SQL Server and wish to locate any of the legacy maintenance plans then you can navigate to the Legacy folder underneath Management in SSMS:
“Do I need to install SSIS with SQL server?”
There are several posts and support articles on this. A Microsoft support article shows:
“If you only want to design and to execute SSIS packages, you do not have to start the SSIS service. When the SSIS service is stopped, you can run SSIS packages by using the following utilities:
If you want to list and to monitor SSIS packages in SQL Server Management Studio, you must start the SSIS service. The SSIS service provides the following management capabilities in SQL Server Management Studio:
- The SQL Server Import and Export Wizard
- The SSIS designer
- The Execute Package utility (DTExecUI.exe)
- The DTExec.exe command prompt utility
- Retrieve an SSIS package from remote storage or from local storage. Then, the SSIS service can locally execute the SSIS package without using SQL Server Agent jobs.
- Stop the SSIS packages that are running locally or remotely without using SQL Server Agent jobs.
- Monitor the SSIS packages that are running locally or remotely without using SQL Server Agent jobs.
- Import and export SSIS packages.
Note You can also use the Dtutil.exe utility or the SSIS Deployment Wizard to save SSIS packages in SQL Server or in the file system.
- Manage the locations where an SSIS package can be saved.
- Customize the folders where an SSIS package can be saved.
- Stop the SSIS packages that are running when the SSIS service is stopped.
- View the events that are related to the SSIS service in the event log. “
Tibor Karaszi outlined an excellent reference on his blog, in the below post, as to the specific versions and service packs of SQL that require the installation SSIS to run a maintenance plan:
Who Has SSIS?
Another very common question that I have run across is from those who have installed SQL Server Workstation edition, created an SSIS package and attempted to deploy or run the package from SQL Server Workstation edition. Workstation edition does support maintenance plans and the Import/Export Wizard, but does not support “graphical extract, transform, and load (ETL) capabilities.”
The over simplistic answer is that the decision to install and/or use SSIS is completely dependent upon your organizations need(s) and the version and service pack that you may be using.