Using SQL Server Integration Services (SSIS) tools in business scenarios

Using Integration Services tools in business scenarios

The lifecycle of an Integration Services solution typically has three phases: development and testing, deployment to the test or production environment, and administration in the production environment. The cycle is iterative; packages require modification and are exported to the development environment or retrieved from a storage location, updated in Business Intelligence Studio, and then again deployed to the test or production environment. This section provides answers to questions from commonly occurring scenarios in each phase of the life cycle.

• Common environment configuration scenarios
• Common package development scenarios
• Common package deployment scenarios
• Common package management scenarios

This section includes information about common tasks that you do to configure Business Intelligence Development Studio.

• How to customize the Toolbox
• How to configuring Integration Services designers
• How to work offline
• How to incorporate source control in the development environment

How to customize the toolbox

By default the Toolbox lists all the control flow and data flow items that Integration Services includes. To populate the Toolbox with Integration Services items, you must open a package in SSIS Designer. When the Control Flow tab is active, the Toolbox lists control flow items, the tasks and containers Integration Services provides, and any custom tasks you have added. Similarly, when the Data Flow tab is active, the Toolbox lists sources, transformations, and destinations. The package must include a Data Flow task before you can access the Toolbox populated with data flow items.

To customize Toolbox content:

1. Right-click the Toolbox and click Choose Items.
2. In the Choose Toolbox Items dialog box, click the SSIS Control Flow Items, SSIS Data Flow Items, or Maintenance Tasks tab and then clear the check box by any item you want to omit from the Toolbox.
3. Click OK.

To reset the Toolbox, right-click the Toolbox and click Reset Toolbar.To sort items, right-click the Toolbox and click Sort Alphabetically.

How to Configure Integration Services Designers

The Options option on the Tools menu opens the Options dialog box, which provides pages to configure the behavior of SSIS Designer. To access the pages for Integration Services, expand the Business Intelligence Designers node, and then expand Integration Services Designers. On the General page (see Figure 16-27), set options for digital signatures and accessibility. By default, the three values of precedence constraints are shown by colors: blue for completion, red for failure, and green for success. To include labels on the design surface that describe the values, select the Show precedence constraint labels check box.

Figure 16-27 The options on the General page for digital signatures and accessibility.

Click Control Flow Auto Connect to open the page to configure the behavior of control flow items, tasks, and containers when you add them to the control flow designer. You can specify whether the new item is automatically connected to a selected shape, the value of the precedence constraint, and the placement of the item on the control flow design surface. Likewise, click Data Flow Auto Connect to configure the behavior of data flow items. You do not use precedence constraints in data flows, and the Data Flow Auto Connect page has only options to specify whether data flow items are automatically connected and their placement on the data flow design surface.

How to work offline

You can configure an Integration Services project to be offline. If you are working in an environment disconnected from the data sources and other resources that packages use, you can make your life easier by setting the project mode to offline. This way, you can avoid package validation, which inevitably generates errors. For example, connections cannot be verified because the data sources to which they connect to are not available. To work offline, click the Work Offline option on the SSIS menu (see Figure 16-28).

Figure 16-28The SSIS menu shows the Work Offline option. If a package is open in SSIS Designer, this option is listed with other SSIS menu options such as Logging and Variable.

The option to work offline applies to the entire Integration Services project; you cannot mix online and offline modes within a project.

How to Incorporate Source Control in the Development Environment

Integration Services projects can be enrolled in source control. If you have source control software installed on your computer, you have the option to add the project to source control when you first create the project. To enroll the project in source control, select the Add to Source Control check box (see Figure 16-29).

You can also enroll existing projects in source control. From the Source Control option on the File menu, you can add a project to or from source control, exclude selected items from source control, and specify the source control software to use (see Figure 16-30).

Figure 16-29 The New Project dialog box shows the option to add source control to the project.

Figure 16-30The Source Control submenu shows the options available to work with source control.

Use the following table of contents to navigate to chapter excerpts, or click here to view Inside Integration Service Tools in its entirety.

Inside SQL Server Integration Services Tools
  Home: Introduction
 Part 1: Integration Services overview
 Part 2: Integration Services tools overview
 Part 3: Using Integration Services tools in business scenarios
 Part 4: Common package development scenarios
 Part 5: Common package deployment scenarios
 Part 6: Common package management scenarios

About the book

Microsoft SQL Server 2005's high-powered management tools can dramatically improve DBA productivity and effectiveness. Now there's a comprehensive guide to SQL Server 2005's toolset, straight from the Microsoft team that created it. This book covers the entire toolset in unprecedented depth, guides database professionals in choosing the right tools, and shows them how to use various tools collectively to solve real-world problems. Purchase Inside SQL Server 2005 Tools from Addison-Wesley.

About the author

Lead author Michael Raheem is a senior product manager in the SQL Server Marketing team at Microsoft. Michael currently leads the SQL Server enterprise marketing efforts, including high availability, scalability, performance, and SQL Server Always On Technologies. Prior to joining the marketing team, he led the design and implementation of several SQL Server 2005 tools such as Management Studio, Upgrade Advisor, Database Mail and Surface Area Configuration. Michael has spoken at several conferences, including TechEd, TechReady, PASS and SQL Connections. Additionally, he has contributed to the Answers from Microsoft column in SQL Server Magazine and has over 13 years of experience in designing and developing solutions with Microsoft SQL Server.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Relational Database Management Systems (RDBMSes) Federated databases Metadata Basics Oracle RAC troubleshooting advice and application migration tips Oracle Database 11g tutorial Systems products to pay attention to Reasons to upgrade to SQL Server 2008 SQL Server security: Enhancements in encryption, authentication and auditing SQL Server security: Auditing SQL Server security: Authentication Which databases underpin the applications to be virtualized? Data Analytics Bridging the IT/business gap in business intelligence projects Business intelligence consulting: Problems and solutions SSIS brings business intelligence services prospects Systems products to pay attention to Stay competitive with Software as a Service business intelligence services RDBMS performance monitoring tools Integrating business intelligence software with Microsoft Excel SQL Server capacity planning SQL Server 2005 business suite and Microsoft Office integration Business intelligence's (BI) new predictive analytic tools

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary