Chapter 17: Using SSIS with External Applications

Overview

SQL Server 2005 Integration Services accepts data from nearly any source and presents output, including ADO.NET datasets and SSIS datareaders, that are consumable by external applications. These features allow SSIS to sink and source external applications with ease. In this chapter, you will take a look at three examples of external applications that utilize SSIS. This chapter is not intended to exhaust all possible combinations of external interface with SSIS but rather to provide a sampling of some available functionality.

SSIS is flexible and configurable, so there are many ways to approach interaction with external applications. This book is rife with examples, including the following:

• Sources and Destinations — Implicit objects inside SSIS that provide connectivity to data sources and destinations. See Chapter 4 for more information.

• Scripting — Arguably provides the most flexibility when interacting with external applications. See Chapter 16 for an example and more information.

Because interface scenarios can vary, it is difficult to define best practices. That said, generally accepted software development practices apply, including the following:

• Employ a methodology — Chapter 18 provides an introduction to Software Development Life Cycles (SDLCs). A development methodology is not a prescribed recipe; it is a frame-work that assists you in creating the proper recipe for your software development project.

• Debug — Execute your SSIS package in Debug Mode in either the Business Intelligence Developer Studio or the Visual Studio Integrated Development Environment (IDE).

• Test — Wherever possible, obtain a sample of actual ("live") data and execute your package against this data. In the absence of access to a copy of live data, populate tables with dummy data and execute your package against them.

In the first example, SSIS will read data from an RSS feed and output results to a Reporting Services report. This demonstration was inspired by Kamal Hathi, who presented an MSDN TV demonstration of this SSIS capability. Mr. Hathi's presentation shows that this is feasible — the example in this chapter demonstrates configuration, functional considerations, and pitfalls.