SSIS Components

So far, you’ve learned about SSIS objects and process control architecture. Now you will learn about the SSIS components that you use to design, test, deploy, manage, schedule, and execute SSIS packages. Some of the SSIS components reside on the SSIS server, whereas other components reside on your desktop workstation. A sample configuration scenario is shown in Figure 1-2.

Figure 1-2 Sample SSIS components configuration scenario

SSIS Development Studio

The Business Intelligence Development Studio (BIDS) is the desktop workstation component you use to design, develop, and test SSIS packages. BIDS provides you with a totally graphical-oriented development environment, allowing you to copy, maintain, and create new packages by using a menu and toolbox drag-and-drop method for development. BIDS is a comprehensive development platform that supports collaboration with source code management and version control; provides debugging tools such as breakpoints, variable watches, and data viewers; and includes the SQL Server Import and Export Wizard to jump-start package development.

Within BIDS, the SQL Server Import and Export Wizard allows you to generate SSIS packages to copy data from one location to another quickly and easily. The Import and Export Wizard guides you through a series of configuration editor pages that allow you to select the source data, select your target destination, and map source to target data elements. You might find this wizard helpful for creating a starting point for a package. Once a package is generated by the wizard, you can then further enhance the package by using BIDS. You will learn how to use BIDS in Chapter 2, “Building Your First Package.”

SSIS Runtime Services

SSIS Runtime Services manages storage of packages in .dtsx (SSIS package system file format) files or in the MSDB database and manages and monitors their execution. SSIS Runtime Services saves your package layout, applies configurations, executes packages, manages data source and destination connection strings and security, and supports logging for tracking and debugging. SSIS Runtime Services executables include the package and all its containers, tasks, custom tasks, and event handlers.

After you design, develop, and complete your testing of SSIS packages from your desktop BIDS, you will want to deploy and implement the packages for scheduled or on-demand processing to the SSIS Runtime Services server. In some companies, the deployment of finished packages is oftentimes performed by a production administrator or other authorized group. At other times, packages can be deployed by the developer. Either way, you can use the graphical interface or a command-line utility to configure and complete the package deployment.

SSIS Package Deployment

The SQL Server Management Studio (SSMS) is a desktop workstation component for the deployment and management of packages into production environments. SSMS connects directly to SSIS Runtime Services and provides access to the Execute Package utility, is used to import and export packages to and from available storage modes (MSDB database or SSIS Package Store), and allows you to view and monitor running packages.

There are also two command-line utilities that you can use to manage, deploy, and execute SSIS packages. Use Dtexec.exe to run a package at the command prompt. An alternative to SSMS, Dtutil.exe, provides package management functionality at the command prompt to copy, move, or delete packages or to confirm that a package exists. You will learn all about the roles of these services and other SSIS application deployment procedures later, in Part III of this book, “Managing Packages.”

Finally, a more advanced feature is the Integration Services Object Model that includes application programming interfaces (APIs) for customizing run-time and data flow operations and automating package maintenance and execution by loading, modifying, or executing new or existing packages programmatically from within your business applications.

< Back      Next >

 

 

© Microsoft. All Rights Reserved.