SQL Server 2008 Integration Services Tasks
SQL Server Integration Services (SSIS) is a Business Intelligence tool used by database developers or administrators to perform extract transform & load (ETL) operations. SSIS has come long way since the early days of Data Transformation Services (DTS) which was initially introduced by Microsoft in SQL Server 7.0. Most database developers and administrators had their first interaction with DTS when using the Import and Export Wizard. The primary objective of DTS was to transform data from an OLEDB data sources to another destination. In SQL Server 2008 Integration Services, there are around 28 different control flow tasks and 11 Maintenance Plan Tasks. This article is the first in a series that will cover all the Control Flow Tasks and Maintenance Plan Tasks in SQL Server 2008 Integration Services. Beginning with an overview of the Control Flow Tasks and Maintenance Plans in SSIS 2008.
Overview of Control Flow Tasks in SSIS
The Control Flow Tasks within SQL Server Integration Services (SSIS) can be grouped into 9 major categories based on usage. The different categories for control flow tasks are listed below:
- Container Tasks
- Data Flow Tasks
- Data Preparation Tasks
- Workflow Tasks
- SQL Server Tasks
- WMI Tasks
- Scripting Tasks
- Backward Compatibility Tasks
- SQL Server Analysis Services Tasks
Container Tasks: – container control flow tasks within SSIS can be used by database developers or database administrators when providing a structure to the package and to repeat control flows within the package. It can also be used to group tasks and containers within a package into a meaningful unit of work. Different types of container tasks are For Loop Container, Foreach Loop Container, Sequence Container and Task Host. The Task Host container is not visible within the control flow toolbox, it is designed to provide service to a single task. The container tasks within SSIS are shown in the image below.
While designing SSIS packages to group two or more tasks together; select the tasks , right click on them and select the Group option from the drop down. This will group the tasks together as a group as shown in the image below.
Data Flow Tasks: – Data flow tasks within SSIS can be used to move data from a source to destination and at the same time use certain transformation tasks to clean and modify the data while it is transferred. By adding a data flow task to the package control flow the package can be made to perform data extraction, transformation and load (ETL) operations. The data flow tasks available within SSIS are shown in the image below.
Data Preparation Tasks: – data preparation tasks within SSIS can be used to retrieve data or validate it to determine the quality of data. The different types of data preparation tasks available in SSIS are File System Task, FTP Task, Web Service Task, XML Task and Data Profiling Task. The list of data preparation tasks within SSIS are shown in the image below.
Workflow Tasks: – Workflow tasks within SSIS can be used to send email messages or to communicate with other processes to run other packages, run some other programs or batch files. Use Message Queue Task to send and receive messages between packages. The workflow tasks within SSIS are shown in the snippet below.
SQL Server Tasks: – SQL Server tasks within SSIS can be used to copy, modify and delete SQL Server objects and data. Use the Bulk Insert Task to insert data from a text file or a flat file into an SQL Server database table the same way that the BULK INSERT statement or the BCP.EXE command line tool is used. The Execute SQL Task can be used to execute any TSQL code such as truncating a staging table, running queries, stored procedures etc. The Transfer Database Task can be used to transfer database objects from one database to another. The Transfer Error Message Task can be used to transfer user defined error messages from one instance of SQL Server to another. The Transfer Job Task can be used to transfer SQL Server Agent Job between instances of SQL Server. The Transfer Login Task can be used to transfer logins between instances of SQL Server. The Transfer Master Stored Procedure Task can be used to transfer user defined stored procedures between the MASTER databases on instances of SQL Server. The Transfer SQL Server Objects Task can be used to transfer tables or stored procedures between instances of SQL Server.
WMI Tasks: - Windows Management Instrumentation (WMI) Tasks within SSIS can be used by to read WMI data and watch for WMI events. Different types of WMI tasks are WMI Data Reader Task and WMI Event Watcher Tasks. The WMI Data Reader Task can be used to run WQL queries against the windows management instrumentation to read the event logs, determine hardware information etc. The WMI Event Watcher Task empowers SSIS to wait for certain WMI events and then respond to the event. The WMI Tasks within SSIS are shown in the image below.
Scripting Tasks: – Scripting tasks within SSIS can be used to perform certain functions which are not fully supported by the existing tasks in SSIS.
Backward Compatibility Tasks: – Backward compatibility tasks within SSIS can be used to execute legacy application scripts with the SSIS packages. The different types of backward compatibility tasks are ActiveX Script Task and the Execute DTS 2000 Package Task. The ActiveX Script Tasks is deprecated and is included in SSIS 2005 and SSIS 2008 for backward compatibility with DTS 2000. The Execute DTS 2000 Package Tasks can be used to execute DTS packages (SQL Server 2000) from within an SSIS 2005 or SSIS 2008 packages.
SQL Server Analysis Services Tasks: – SQL Server Analysis Services Tasks within SSIS can be used to create, modify, delete and process Analysis Services objects. The different types of Analysis Services tasks are Analysis Services Execute DDL Task, Analysis Services Processing Task and Data Mining Query Tasks. The SQL Server Analysis Services Execute DDL Task is much similar to Execute SQL Task; however use of Analysis Services Execute DDL Task can issue Data Definition Language statements against an Analysis Services system. The DDL statements can be used to create cubes, dimensions, KPI’s or any other analytical processing OLAP objects. The Analysis Services Processing Task can be used to process analysis services objects such as cubes, dimensions and mining models. The Data Mining Query Task can be used to run prediction queries based on data mining models built in analysis services. A prediction query creates a prediction for new data by using the mining models chosen.
Overview of Maintenance Plan Tasks in SSIS
Within SQL Server 2008 Integration Services there are around 11 Maintenance Plan Tasks available. Different types of Maintenance Plan Tasks are shown in the image below.
Database Administrators can create database maintenance plans either by using Maintenance Plan Wizard or by using SSIS designer. By using the Maintenance Plan Wizard a very basic maintenance plan can be created for all the system and user databases. However, to create an enhanced workflow it is advised to create maintenance plan using SSIS designer. To learn more about creating a Maintenance Plan, refer to the earlier article titled Overview of Maintenance Plans in SQL Server 2008.
Back Up Database Task: – Back Up database task different types of database backups to be performed; such as Full, Differential or Transactional Log backups based on the Recovery Model of the System or User databases. To learn more about the different recovery models in SQL Server refer to the article titled “Database Recovery Models in SQL Server”.
Check Database Integrity Task: – Check Database Integrity Task can be used to check the allocation and structural integrity of all the user and system tables within a database. This task also has an option to check the allocation and structural integrity of all the indexes available within a database. This task internally executes DBCC CHECKDB statement.
Execute SQL Server Agent Job Task: – Execute SQL Server Agent Job Task can be used to run SQL Server Agent Jobs that are created on the SQL Server Instance.
Execute T-SQL Statement Task: – Execute T-SQL Statement Task can be used to execute some Transact SQL queries against databases. This task is only available when creating Maintenance Plans using SSIS designer.
History Cleanup Task: - History Cleanup Task deletes the historical data related to database backups and restore activities; SQL Server Agent Job history, database maintenance plan history etc. This task basically uses sp_delete_backuphistory system stored procedure to clean up the history prior to the number of days, weeks or months from the current system date.
Maintenance Cleanup Task: – Maintenance Cleanup Task can be used to remove the older files like maintenance plan execution reports, database backup files etc. Use this task when creating maintenance plans it will remove old files which are not required.
Notify Operator Task: – Notify Operator Task can be used to send messages to the SQL Server Agent Operator when a task has successfully completed or failed. The operator can be notified by an email, pager or by net send method.
Reorganize Index Task: – Reorganize Index Task is used to defragment & compact clustered and non-clustered indexes on tables and views. The index reorganise is best suited when the index are not heavily fragmented. This process usees fewer system resources compared to rebuilding an index. If the indexes are heavily fragmented then it is best to rebuild indexes using the Rebuild Index Task.
Rebuild Index Task: – Rebuild Index task can be used to organise data on the data and index pages by rebuilding indexes. This helps to improve the performance of index seeks and scans. This task also optimises the distribution of data and free space on the index pages, thereby allowing faster future growth. If this task is used to rebuild indexes in a single database, it allows selection of views and tables to rebuild the index. This task also has options such as “Sort results in tempdb” and “Keep index online while reindexing”. However these operations require sufficient disk space in TempDB database.
Shrink Database Task: – Shrink Database Task can be used to reduce the disk space which is consumed by the database and log files by removing the empty data and log pages. Using this task the space gained after shrinking the database can either be returned to the operating system or it can be retained within the database for its future growth. This task executes the DBCC SHRINKDATABASE statement.
Update Statistics Task: – Update Statistics task ensures that the query optimiser has up-to-date information about the distribution of data values within the tables. This allows the optimizer to make better judgments about the data access strategies it needs to choose. The Update Statistics task basically executes UPDATE STATISTICS statement.
This introductory article has contained an overview of the 28 different control flow tasks and 11 Maintenance Plan Tasks which are available in SQL Server 2008 Integration Services. The next articles in this series will walk through the steps to configure and use all the above mentioned Control Flow and Maintenance Plan Tasks.