SSIS offers a user-friendly integrated environment called Business Intelligence Development Studio (BIDS), which can be used to create, debug, and deploy SSIS packages. This tool leverages the Visual Studio .NET GUI foundation to offer features such as tabbed windows, solution and project management, enhanced designer interfaces, debugging, SourceSafe integration, help integration, and so on. BIDS combines a graphical editor to design packages and several tools and templates to create and debug packages. Several built-in tasks are available in BIDS to allow you to create complex packages without writing a single line of code. The SQL Server Management Studio tool, on the other hand, offers an environment to manage the existing packages.
Let's now look at some built-in containers and tasks that you can use while designing SSIS packages.
Containers provide structure and meaning to a package by grouping various tasks and containers together. Containers can include other containers in addition to tasks. Packages use containers for purposes such as to group tasks and containers that must succeed or fail as a unit, to repeat tasks for each element in a collection (such as files in a folder), and to repeat tasks until a specified expression evaluates to false.
SSIS provides four types of containers for building packages: the Foreach Loop container, the For Loop container, the Sequence container, and the Task Host container. Let's look at each of these types in little more detail.
The Foreach Loop Container
The Foreach Loop container allows a package to loop repetitively, based on an enumerator. The following different enumerator types are available:
The For Loop Container
The For Loop container evaluates a specified expression, and the repetitive flow is continued until the expression evaluates to false. The loop is defined by an initial expression that is optional, an evaluating expression that should result in false for the loop to stop, and an optional iterative expression to increment or decrement the loop counter.
As shown in Figure 12.3, the initial expression sets the variable @var to 0. The tasks in the For Loop container are executed in a loop until @var is less than 10. AssignExpression increments the @var variable each time the loop is run.
Figure 12.3. The For Loop container defines a repeating control flow in a package.
The Sequence Container
While designing SSIS packages, often it is necessary to run multiple tasks in parallel or to group together several tasks in a collection such that the collective property of all the tasks in the collection affects the next task downstream. Such tasks and containers can be grouped in the Sequence container. The Sequence container creates a subset of the package control flow to allow you to manage properties on multiple tasks by grouping them in a single container and allows debugging the tasks as a single unit of work. In addition, you can define scope on the variables over the entire Sequence container. An example of the Sequence container is shown in Figure 12.4.
Figure 12.4. Sequence containers group a package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow.
The Task Host Container
The Task Host container essentially encapsulates a single task. Setting the properties of the encapsulated task automatically sets the properties of the Task Host Container. This container is useful when you're creating SSIS tasks programmatically.
Tasks define the units of work to be performed as part of a package for process communication and data flow. The tasks in SSIS can be divided into the following categories:
Let's now look at each of these task types in a little more detail.
The different control-flow tasks can be further divided into SQL Server tasks, process-oriented workflow tasks, data-oriented tasks, Analysis Services tasks, and scripting tasks.
SQL Server Tasks
SQL Server tasks allow access, configuration, and administration of SQL Server objects. SQL Server tasks include the following:
The other SQL Server tasksincluding the Transfer SQL Server Objects task, the Transfer Database task, the Transfer Error Messages task, the Transfer Jobs task, the Transfer Logins task, and the Transfer Master Stored Procedures taskallow you to transfer different SQL Server objects to different database instances.
Process-Oriented Workflow Tasks
The process-oriented workflow tasks include the following:
Data-oriented tasks include the File System task, the File Transfer Protocol task, the Web Services task, and the XML task. These tasks are used for copying and modifying files, downloading files from different locations, executing web methods, and working with XML documents.
Analysis Services Tasks
Analysis Services tasks include the Analysis Services Processing task, which allows processing of Analysis Services objects such as cubes, dimensions, and mining models; the Analysis Services Execute DDL task, which allows creating, dropping, and altering of mining models and multidimensional objects such as cubes and dimensions; and the Data Mining Query task, which allows running of prediction queries based on data mining models built in Analysis Services.
Scripting tasks allow you to program code to extend the functionality of a package. The two tasks that allow you to program code for better functionality are the Script task and the ActiveX Script task. The ActiveX Script task allows you to code in scripting languages such as VBScript or JScript. To code in .NET languages such as Visual Basic. NET or C# .NET, SSIS has introduced a new task called the Script task, which allows you to perform complex tasks by writing VB .NET or C# code.
Data Flow Tasks
Data flow tasks control the flow of data from the source to the destination and allow you to cleanse and transform data and redirect portions of the data to different destinations.
The data flow tasks are divided into source adapters, transformations, and destination adapters.
Source adapters allow you to connect to different data sources, using different connection managers, and to extract and read data. The different connections that you are allowed to connect to are OLE DB, flat files, Excel files, raw files, XML files, and data reader.
Different transformation can be applied to cleanse, modify, and transform data. These are the different transformations:
Destination adapters allow you to load processed data into different destinations. The different destination adapters available in the Toolbox are the Flat File destination adapter, the OLE DB Destination adapter, the Raw File destination adapter, the Recordset destination adapter, the SQL Mobile destination adapter, and the SQL Server destination adapter.
Database Maintenance Tasks
Maintenance tasks, such as backing up the database, monitoring index fragmentation, checking database integrity, and executing custom scripts to monitor database performance and high availability, are some of the common tasks that database administrators perform on a regular basis. SSIS offers several useful database maintenance tasks for DBAs to include in a maintenance plan without writing custom scripts or coding. The different database maintenance tasks available in the Toolbox include those described in the following sections.
The Backup Database Task
The Backup Database task allows you to back up a single or multiple databases. You can perform a full backup, a differential backup, or a transaction log backup based on the recovery model. In addition, this task also allows you to create a file and filegroup backup for a database with multiple filegroups and data files. The backup device can be specified or created in this task, and it contains all the properties associated with backup creation using T-SQL or SQL Server Management Studio.
The Reorganize Index Task
You use the Reorganize Index task to defragment fragmented indexes in a single or multiple databases. The task also includes an option to defrag large object data such as text, ntext, varchar(max), nvarchar(max), varbinary(max), or xml data. To configure defragmentation of large objects, you check Compact Large Objects in the task dialog box or set the CompactLargeObjects option to true in the task properties box. This option is off by default.
The Check Database Integrity Task
The Check Database Integrity task encapsulates the DBCC CHECKDB T-SQL command to check the allocation and structural integrity of all the objects in a single or multiple databases. To check the integrity of indexes, you select Include Indexes in the dialog or the IncludeIndexes custom property in the properties box.
The Rebuild Index Task
The Rebuild Index task allows you to rebuild indexes on SQL Server objects such as tables and views. The task contain different custom properties, such as allocation of free space, FillFactor percentage, PAD_INDEX to allocate free space specified by the fill factor, SORT_IN_TEMPDB to store intermediate results in TempDB, and IGNORE_DUP_KEY to allow multiple-row index operations. In addition, it provides the option to release table locks by setting the ONLINE option to ON.
Other database maintenance tasks available in the Toolbox include the following:
Business Intelligence Development Studio
Business Intelligence Development Studio (BIDS) is an integrated environment for designing, building, and deploying business intelligence applications. BIDS integrates the features previously available in Analysis Manager and DTS in SQL Server 2000. BIDS contains windows described in the following sections.
The Solution Explorer provides a solution- and project-based organized view of packages, data sources, data source views, and other files. You can use it to view the various packages and access their properties. In addition, the Solution Explorer allows versioning of packages using Microsoft Visual SourceSafe.
The SSIS Designer Window
The SSIS designer window provides a nice graphical view of packages. It allows you to create, modify, debug, and save packages. The designer contains the following tabs:
The Toolbox Window
The Toolbox contains all the built-in tasks, arranged in proper order and categorized by functionality. The Toolbox category and items change depending on the settings on the Control Flow and Data Flow tabs. For instance, if you are in the Control Flow tab, the Toolbox shows maintenance plan tasks and control flow items; however, if you are in the Data Flow tab, the Toolbox lists data flow source, transformation, and destination tasks.
The Properties Window
The Properties window contains the properties of each task selected. You can specify the custom properties associated with each task in this window.
The Debug Windows
The Debug windows give you the ability to set breakpoints and debug the SSIS package and watch the data as it flows through the package workflow. This provides tremendous value in catching and fixing errors and in troubleshooting SSIS packages. The following sections describe some of the debug windows available in BIDS.
The Breakpoint Window
A breakpoint suspends package execution at the point where the breakpoint is defined. Breakpoints can be set on the different tasks in the SSIS designer to suspend a package on the different event handlers specified in Table 12.1. You can customize breakpoint behavior by setting the hit count type. The hit count type can be specified as follows:
The enabled breakpoint can be viewed in the breakpoint window. It also displays the various supported breakpoints and allows you to enable or disable the breakpoints.
The Command Window
The Command window allows you to specify execution commands by bypassing the menu system.
The Immediate Window
The Immediate window allows you to debug, evaluate expressions, print variables, and so on.
Call Stack Window
The Call Stack window lists the SSIS containers that are currently running.
The Locals Window
The Locals window provides the status on the current executables and variables. It lists all the variables within the scope of the current context.
The Output Window
The Output window displays various status messages during the course of package compilation and execution. It displays validation errors when a package is opened, build errors when a package is compiled, and progress/status messages on execution of a package.
The Watch Window
The Watch window is used for viewing, editing, and saving variable values and expressions. It displays various variables and expressions with its associated data types and evaluated expressions.
SSIS uses two methods to report the progress of a package during the execution:
The Progress tab displays the order of the task execution and the start and finish times of each task. In addition, it displays any errors encountered during the execution of the package.
Color-coding is also used to display the status of package execution. Depending on color, you can distinguish whether a package is running or whether it has successfully completed or failed. This color-coding is displayed only during package execution. Table 12.2 describes the color-coding.
SQL Server Management Studio
SQL Server Management Studio allows you to manage existing packages. It allows importing and exporting of packages saved from one kind of storage format to another. It allows storing of a package to either the file system in XML format, SQL Server, or the SSIS package store. Management Studio cannot be used for designing or debugging packages, but it can be used to execute a package, view running packages, and import and export packages. When the SSIS service is running, you can use Management Studio to connect to an SSIS instance in the Object Explorer window to see existing and running packages.
SQL Server 2005 includes several command-prompt utilities that you can use to manage and execute the SSIS packages. They include the following:
The following sections describe these utilities in more detail.
You can invoke this utility from the command prompt by typing DTExecUI (see Figure 12.6). It opens up a user interface where you can set the properties to execute the package.
Figure 12.6. The DTExecUI utility can be used to run an SSIS package.
The DTExecUI utility allows execution of SSIS packages stored in SQL Server, the file system, or the SSIS package store. You can specify the configuration file to be loaded to set the object properties of the package during runtime, or you can set values of individual objects. In addition, you can specify the commands to load, and in what order, during runtime. You can set the connection properties if the package needs to connect to an external data source. You can also set different execution options, such as setting validation warnings or enabling package checkpoints. Log files can be specified to log the execution of a package or set package verification. Alternatively, you can choose to report execution information to the console, and you can specify the level of information to display.
DTExec is similar to DTExecUI except that it does not contain a user interface. All the settings are passed as command-line parameters. Based on the exit code, the user can determine whether the command was executed successfully. Table 12.3 lists the return values for DTExec.
The DTUtil command-line utility allows you to manage existing packages by allowing you to perform operations such as copying, deleting, moving, and signing a package. You can also use DTUtil to verify whether a package exists. Table 12.4 lists the return values for DTUtil.