An Overview of SSIS Features - SQL Server 2008

In SQL Server 2008, SSIS continues to be the Swiss army knife of the ETL world by enabling you to perform many tasks, including the following:

  • Importing and exporting data between SQL Server instances and heterogeneous data sources
  • Applying sophisticated transformations against data
  • Automating SQL Server maintenance tasks and business processes
  • Executing scripts
  • Sending e-mail notifications
  • Cleansing, duplicating, and profiling data (to ensure data quality)
  • Interacting with message queues and FTP sites
  • Launching Analysis Services processes
  • Submitting data-mining queries
  • Processing and manipulating XML files
  • Querying your computer using Windows Management Instrumentation (WMI) queries and responding to events accordingly

Many of us have spent the past several years converting or rewriting our packages from DTS into SSIS. It is probably pretty safe to say that most of us had mixed emotions about our first experiences with SSIS; things seemed so easy with DTS. However, after spending a few days getting used to the SSIS development environment, the separation of control flow from data flow, the greatly expanded list of built-in transformations, and the advanced package configurations, most people have found that they are much more productive with SSIS than they were with DTS. Some of the added sophistication comes with the fact that SSIS is truly an enterprise ETL tool. Moving forward, you’ll be happy to know that most, if not all, investment of time that you have made in SSIS will transfer nicely into the SQL Server 2008 version of SSIS.

When Can You Use SSIS?

With SQL Server 2008, Microsoft has added several new features, improved existing features, and removed problem areas. Database administrators (DBAs) and developers alike can use SSIS to address a wide variety of business solutions. The following are some scenarios in which SSIS can be used:

  • A real estate company is building a property search web site that allows users to search for properties by city and county. It receives several denormalized flat files from third-party multiple listing services. Each of the third-party providers produces flat files differently, so the solution must be flexible. Each of these files must be imported, scrubbed, and put into the same normalized tables in the database. SSIS is used to import the files via FTP, scrub them using transformations, and load them into the production tables.
  • A manufacturing company trains new plant managers on how to use a parts-tracking maintenance application. During training, trainees are allowed to connect to their own test database, where they can modify the data without doing harm to the production data. Nightly, an SSIS package is scheduled to refresh the data in the training databases, based on real data from a production database.
  • A financial services company uses legacy systems to track an individual investor’s transactions to his or her savings plan. These legacy systems allow simple OLE DB connectivity to the raw transactional data. A reader-friendly reporting database isn’t available. Executives want to be able to view aggregated transaction data grouped by investment and time period. SSIS is used to extract data from the legacy systems into a SQL Server database.
  • An information technology company wants to automate the extraction and reporting on the condition of its SQL Server environment. The company wants to maintain a central reporting database in SQL Server that will contain various statistics about all SQL Server machines (SQL Server Agent jobs, SQL Error and Agent logs, database file locations, and the last time each database was backed up). SSIS is used to query each server and extract the data to the centralized reporting database.
  • A human resources (HR) department in a small regional office receives daily “new employee” files from corporate headquarters. Each file contains a single record of data for each new employee. Included in this record is a BLOB image containing the photo of the new employee. The HR department uses SSIS to import the character data into one table and output the image BLOB into a separate image file (using the Export Column transformation).

What’s New in SSIS?

The foundational components of SSIS haven’t changed from SQL Server 2005 to 2008. However, there have been many enhancements made to ensure that SSIS can scale to meet the needs of the most advanced data warehousing scenarios, to ensure that SSIS continues to be easy to use and a very productive environment, and to expand the number of data sources supported out of the box. Some SQL Server 2008 feature highlights include the following:

  • An enhanced lookup operator supports more flexible levels of caching.
  • Improvements in thread utilization ensure that machine resources are put to their best use and that the maximum number of pipeline components are running in parallel.
  • The new Change Data Capture feature and merge (upsert) capabilties are supported within the core SQL Server 2008 relational engine. The introduction of these capabilities has many ETL designers very excited, because they will greatly simplify what, in the past, has been a complex problem to solve.
  • New data profiling (data quality) functionality provides advanced algorithms for identifying patterns within data values.
  • Visual Studio Tools for Applications (VSTA) support has replaced the Visual Studio for Applications (VSA) engine and provides more advanced .NET scripting capabilties.
  • Improvements in package logging and diagnostics make debugging package problems and production issues much easier.
  • Full ADO.NET support has been added, along with Office 2007 ACE provider support and LOB adapters for Oracle, Teradata, and SAP.
  • Enhancements to the Import/Export Wizard include more intuitive error messages, which are helpful to those who aren’t intimately familiar with SSIS.
  • Full support is provided for DTS packages running within SQL Server 2008. More advanced upgrade functionality will further assist those individuals with dozens of DTS packages to convert to SSIS. Often, very complex DTS packages become fairly trivial within SSIS because of the separation of control flow from data flow and the inclusion of advanced transformations. We’ll discuss these new features and improvements throughout this chapter.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics