Thursday, March 31, 2011

The SSIS Data Pump - Step 2 of the Stairway to Integration Services


As we all know that SQL Server Integration Services is one of the most popular subsystems in SQL Server. In allows us to Extract, Transform, and Load (ETL) data between a variety of data sources and programmatically change data in any manner you can think of and script in C#.

In our previous Part 1 – BIDS we learned our way around Business Intelligence Development Studio and we are now itching to build an SSIS package that moves real data!

This article explains:
  • The relationship between Connection Managers and adapters (OLE DB Source and OLE DB Destination)
  • Role and some features of Data Flow Paths that connect Data Flow Task components to each and represent the "data flow pipeline"
  • Design-time validation and how to view Errors and Warnings.
  • Some cool time-saving features of the Adapter Editors - we will fall in love with these New buttons!
  • Formatting components on the Data Flow canvas so they're aligned.
  • Take a peek under the hood of the Data Flow Task, viewing the Data Flow Path metadata.
  • How this metadata can be used by the OLE DB Destination to build a CREATE TABLE statement.

Happy Learning

No comments: