Wiley Expert SQL Server 2005 Integration Services 978-0-470-13411-5 User Manual
Product codes
978-0-470-13411-5
the right situation, there may be a specific tool to handle such a function. Think about all the different
types of data processing needs that you have across your organization:
types of data processing needs that you have across your organization:
❑
Data synchronization between systems
❑
Data extraction from ERP systems
❑
Ad hoc reporting
❑
Replication (both homogeneous and heterogeneous)
❑
PDA data synchronization
❑
Legacy system integration
❑
Vendors and partner data files integration
❑
Line of business data
❑
Customer and employee directory synchronization
❑
Data warehouse ETL processing
As you may know, when it comes to data processing, there are a lot of tools 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?
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 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.
to hand-code a 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 there can be overlaps, 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 chal-
lenge 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.
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 chal-
lenge 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 per-
plexed 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 BI processing is an ETL tool such as SSIS. In fact, as
shown in Figure 1-1, SSIS provides an excellent platform for leveraging its high-performance data
pipeline.
to-business communication and process workflow automation. These same organizations may be per-
plexed 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 BI processing is an ETL tool such as SSIS. In fact, as
shown in Figure 1-1, SSIS provides an excellent platform for leveraging its high-performance data
pipeline.
2
Chapter 1: Getting Started
04_134115 ch01.qxp 4/24/07 6:40 PM Page 2