Datasheet

Chapter 1: SSIS Solution Architecture
9
Choosing the Right Tool
This book is about applying SSIS. You are probably reading it because you assume that SSIS is the right
tool for the job. That ’ s probably the case. However, be sure to consider what you are doing, and ensure
that using SSIS is in line with what you are doing.
Think about all the different types of data-processing needs that you have across your organization:
Data synchronization between systems
Data extraction from enterprise resource planning (ERP) systems
Ad hoc reporting
Replication (both homogeneous and heterogeneous)
PDA data synchronization
Legacy system integration
Data warehouse ETL processing
Vendors and partner data files integration
Line-of-business data processing
Customer and employee directory synchronization
As you may know, when it comes to data processing, a lot of tools are out there. Some are created for
specific situations (such as folder synchronizing tools), whereas other tools are designed to perform a
variety of functions for different situations. So, the traditional question often posed is which tool can best
meet the business and logical requirements to perform the tasks needed?
Consider the host of tools found in the ever - evolving Microsoft toolset. You can use Transact SQL (TSQL)
to hand - code a data load, Host Integration Server to communicate with a heterogeneous data source,
BizTalk to orchestrate messages in a transactional manner, or SSIS to load data in batches. Each of these
tools plays a role in the data world.
Although overlaps exist, each tool has a distinct focus and target purpose. When you become
comfortable with a technology, there ’ s always the tendency to want to apply that technology beyond its
intended “ sweet spot ” when another tool would be better for the job. You ’ ve no doubt heard the phrase
“ when you ’ re a hammer, everything looks like a nail. ” For example, C# developers may want to build an
application to do something that SSIS could potentially do in an hour of development time. The
challenge everyone faces entails time and capacity. There is no way everyone can be an expert across the
board. Therefore, developers and administrators alike should be diligent about performing research on
tools and technologies that complement each other, based on different situations.
For example, many organizations use BizTalk for a host of purposes beyond the handling of business - to -
business communication and process workflow automation. These same organizations may be perplexed
as to why BizTalk doesn ’ t scale to meet the needs of the organization ’ s terabyte data warehousing ETL.
The easy answer is that the right tool for bulk Business Intelligence (BI) processing is an ETL tool
such as SSIS.
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
c01.indd 9c01.indd 9 9/24/09 11:26:31 AM9/24/09 11:26:31 AM