Search

One of the most exciting aspects of any technology is not only what it does but how it does it. SSIS is all the more interesting because of the underlying parts it leverages to provide its services. Consider how it seamlessly integrates with the SQL Server 2005 engine.NET, and the operating system (OS)—all in a unified way previous SQL Server versions did not manage. Yet this new version maintains support for running legacy DTS packages (known as DTS 2000 packages). [ABSTRACT FROM AUTHOR]

Custom components are perhaps my favorite part of SSIS. The robust and scaleable architecture on which all SSIS components are constructed can fulfill even your most demanding business need. Custom components are the elixir of eternal life for SSIS, allowing the product to be extended far beyond original specification. Perhaps their most compelling aspect is how easy it is to produce them. This chapter is all about the design and creation of robust and professional custom components, concentrating on the Control Flow task and the Data Flow component. [ABSTRACT FROM AUTHOR]

This chapter, along with Chapter 15, goes black-ops on the programming aspects of SSIS. This chapter covers scripting in SSIS along with topics such as object-oriented programming (OOP).NET, debugging, and many more. Since it deals with the conceptual elements of programming with SSIS, it doesn't cover C# or Visual Basic .NET in any great detail. [ABSTRACT FROM AUTHOR]

To build the most efficient, performance-driven SSIS solution, you must perform a suitably complex mix of SQL-based and SSIS-specific optimizations. Some obvious optimization techniques should already be under consideration as part of schema or query design, but others are less obvious and rely on an understanding of the underlying SSIS architecture. (See Chapter 2 for an architectural overview.) [ABSTRACT FROM AUTHOR]

As the final piece of the ETL puzzle, Data Flow destinations form perhaps the most vital function of all—delivering data to its destination. These Data Flow end points can be targets such as a database table, a flat file, an XML document, or all sorts of other storage media. All destination components share a common structure in that they have inputs (usually a single input) and no outputs. [ABSTRACT FROM AUTHOR]

In this second part of the discussion of Data Flow transformations, I'll continue to make deep inroads into each component. This chapter covers the following advanced Data Flow transformations: • Aggregate• Data Mining Query• Fuzzy Grouping• Fuzzy Lookup• Pivot• Script Component• Slowly Changing Dimension• Term Extraction• Term Lookup• Unpivot [ABSTRACT FROM AUTHOR]

Transformations are the meat—or the mycoprotein—in the ETL sandwich. Although it's vitally important to ensure the data sources in a package are configured correctly, the actual handling of data via transformations takes pole position when it comes to package development. [ABSTRACT FROM AUTHOR]

Now we're talking. No more messing around with containers and the like—it's time for straightforward data manipulation! Now you will see the true power of the fully operational battle station that is SSIS. [ABSTRACT FROM AUTHOR]

In the main, the maintenance tasks included with SSIS are wrappers, presented in a graphical format, for T-SQL statements that perform maintenance tasks. What's nice about these components is that they encapsulate fairly complex statements for you, with all necessary parameters. As such the tasks form a pragmatic though essential toolset for performing maintenance tasks through SSIS. [ABSTRACT FROM AUTHOR]

As I discussed in Chapter 2, SSIS features a much more enterprise-ready architecture than was available through DTS. One feature of this architecture is the abstraction of the different logical layers one might find in any ETL or integration tool. To work as dynamically and with as much flexibility as possible, an ETL tool requires a number of constituent parts to work together in creating a whole. In SSIS, these parts are • Control Flow• Data Flow• Precedence constraints• Connection managers [ABSTRACT FROM AUTHOR]

Now that you're familiar with the development environment and with adding projects and packages, it's time to learn exactly what the conceptual and physical objects in an SSIS project are. Different types of project items—but not a great variety—can be added to an SSIS project. [ABSTRACT FROM AUTHOR]

A book on SSIS would be incomplete without a tour around the IDE provided with SQL Server 2005 for the development of BI projects. Microsoft has tied together the three non-database engine products that ship with SQL Server—SSIS, SSAS, and SQL Server Reporting Services (SSRS)—and provided a Visual Studio "lite" instance for developing projects for them: BIDS [ABSTRACT FROM AUTHOR]

Chapters 1 and 2 have given you an overview of SSIS and the core architecture of both SQL Server and SSIS. This chapter is a guide to transitioning from DTS to SSIS. It explains • How SSIS handles tasks you now do with DTS• How you can do the same things more efficiently using some of SSIS's new features• The best way to migrate your existing hard work with DTS into shiny new SSIS packages. [ABSTRACT FROM AUTHOR]

SQL Server 2005 is built on solid foundations. Microsoft has repositioned the product many times over its life cycle, adding new parts and facets over the years and removing others. Young technologies have matured and become a staple of the SQL Server experience. The database engine alone has evolved beyond recognition since the original release and provides a robust, incredibly scalable database platform. [ABSTRACT FROM AUTHOR]