SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 338 of 365 Next >

I've been covering Business Intelligence using general topics in a series of tutorials. Within that series I covered the Extract, Transform and Load concepts that you need to keep in mind as you transfer the data from the Source Systems to the first part of the Analytical System. In this tutorial I'll put those concepts to work, using SQL Server 2005's Integration Services (SSIS). This software is available only for SQL Server 2005; in SQL Server 2000, you can use Data Transformation Services to do much the same thing. I'll cover that tool in another tutorial, in case you're using that version of the software.

SSIS deserves to be treated as a complete software package of its own. It has its own set of design tools, and can be used not only for the Business Intelligence process but any data movement or cleansing needs you have. In this tutorial I'll explain the basics behind the package and the primary tools you use to design and manage, and in future tutorials I'll explain much more about how you can use SSIS to perform all of the Extract, Transform and Load procedures you need to stock your Business Intelligence landscape with the data it needs.

Microsoft SQL Server Integration Services Concepts

Let's start with a few concepts. Most other Extract, Transform and Load (ETL) tools work by transferring data from the Source Systems to a set of staging tables. These tables are "wide open", in that they don't enforce any kind of integrity of the Source Data. They are more interested in moving the data from the Source System quickly and efficiently. This is the "Extract" part of the process.

Once the data arrives in the staging tables the system applies various transforms to the data. In subsequent steps the data is once again transformed, aggregated or split into smaller parts based on what the analysis needs are, moving it into a final format, at least for that step. This is the "Transform" part of the process.

Finally the data is moved from the cleansed tables into the final location in that part of the landscape. That's the "Load" part of the process. Because of this arrangement, other tools usually focus their interface in these three areas. You'll see a data path for the Extract process, some code or widgets to do the Transform, and the data path continues through to the end result, which is the "Load" part of the process. It's usually diagramed something like this:

Figure 171

Microsoft takes a different approach. Although SSIS allows for the use of a set of staging tables, you no longer have to use them. Microsoft has the idea of a "pipeline", which is a view of the data moving from the source files to the destination in one movement. It involves setting up one connection flow with multiple events in the line from start to finish. Looked at this way, the process comes out something like this:

Figure 172

Once again, you can still use a Staging Table arrangement; you just no longer need to. That brings up the two parts of the conceptual framework for SSIS: The Control Flow and the Data Flow. Both of these make up a Package, and multiple Packages make up a Project. Multiple Projects make up Solutions. You'll see all that come together when we explore the Business Intelligence Development Studio in a moment.

Control Flow

The Control Flow part of SSIS involves any steps you want to take prior or subsequent to connecting to the data, and then any Data Flow elements, which I'll describe next.

You can think of Control Flow objects as all of those things you want to do in addition to moving the data. Perhaps you need to download a file from an FTP location, send a trigger script, notify an operator or start a logging file. All of those items are placed in an outer container, which also includes one or more Data Flow objects. You bracket the data movement with tasks that you need to perform prior to and after the data movement.

You may have only one object inside your Control Flow container, a Data Flow object. If you're doing a simple data transfer from one database to another or from the database out, that might be all that you have. In more complex situations like your Business Information strategy you'll often see multiple

Data Flow

The Data Flow container has objects to connect to various data sources and destinations. Using those connections you add the sources and destinations, and define the inputs and outputs for each. You connect the output of the source to the input of the destination objects, making a basic data path.

You can also make the path more complex by adding various transformation objects in between the sources and destinations. These transformations can combine and aggregate, or split apart and separate data. They can perform numerical calculations; create new (derived) fields, and much more. You'll spend the bulk of your time working on these transformations.

You're not limited to working with a single source and destination. You can route the outputs of several sources to the inputs of a single destination, or a single source can feed multiple destinations. You can route the outputs of one source into the inputs of a data object that in turn outputs to another, each having a different transformation.

In times past, we used to draw all of these movements out on paper or using some electronic tool, which we then coded into the system using data statements or specialized tools. SQL Server Integration Services includes a special environment, called the Business Intelligence Development Studio that contains all of the elements and tools you need to design and implement the solution. Everything is a graphical component, but you can always get to code if you need to do more granular work.

The BI Development Studio isn't the only way that you can work with SSIS. SQL Server Integration Services is also completely programmable. The object model is exposed so that you can create and control the entire system with code.

In the next tutorial, we'll open up the Business Intelligence Development Studio and explore the interface. After that we'll spend some time creating various scenarios to load the data into the ODS.

Informit Articles and Sample Chapters

Although you might get data directly from your Source Systems, in many cases the data may come through a Business to Business (B2B) interface like Microsoft's Biztalk. Staying in the "family" allows direct feeds from Biztalk to Integration Services. You can read more in the book BizTalk Unleashed by Susie Adams et al, which you can find here.

Online Resources

There are a lot of resources that you can search on the web regarding Integration Services. The place you should start is on Microsoft's site. There are actually a few entry points into the product since it does so many things, but this is probably the best resource to begin with.

< Back Page 338 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address