ETL 4: SQL server integration services

Source: Internet
Author: User
Tags ssis

ETL 4: SQL server integration services

SSIS is Microsoft's upgrade to DTS on SQL server2005. It has to be said that Microsoft has spent a lot of effort on Bi, including providing multiple tools such as SSIs, SSAs, and SSRs, from creating a data warehouse to extracting data from metadata, to creating dimensions and mining structures, mining models, training, report model design, report design, and publishing, It is very convenient and powerful. Start with ETL and see how to use these tools to associate metadata with a data warehouse. The following is a conversion from msdn:

In this lesson, you will create a simple ETL package that extracts data from a single flat file source and converts the data using two query and conversion components, write the data to the factcurrencyrate fact data table in adventureworksdw. In this lesson, you will also learn how to create new packages, add and configure data source and target connections, and use new control flow and data flow components.

Important:
This tutorial requires the adventureworksdw sample database. For more information about installing and deploying adventureworksdw, see: .

 

 

Understand package requirements

Before creating a package, you must fully understand the format used in the source and target data. After learning about the two data formats, you can define the conversion required to map the source data to the target data.


View Source

In this tutorial, the source data is a set of historical currency data contained in the flat file samplecurrencydata.txt. The source data includes the following four columns: Average currency exchange rate, currency key, date key, and today's exchange rate.

The following is an example of the source data contained in the samplecurrencydata.txt file:

1.00010001 ARS 0.99960016

1.00010001 ARS 1.001001001

1.00020004 ARS 0.99990001

1.00020004 ARS 1.00040016

1.00050025 ARS 0.99990001

1.00050025 ARS 1.001001001

1.00050025 ARS 1

1.00010001 ARS 1.00040016

1.00020004 ARS 0.99990001

1.00020004 ARS 1.001101211

 

When using flat file source data, it is important to know how the flat file Connection Manager interprets flat file data. If the flat file source is unicode encoded, flat file connection management defines all columns as [dt_wstr]. The default column width is 50. If the flat file source is ANSI encoded, the column is defined as [dt_str]. The default column width is 50. You may have to change these default settings to make the string column type more consistent with the data used. To do this, you need to view the data type of the target data to be written, and then select the correct type in the flat file Connection Manager.

View target

The final goal of the source data is the factcurrencyrate fact data table in adventureworksdw. The factcurrencyrate fact table has four columns and is related to two dimension tables, as shown in the following table.

Column name Data Type Search tables Search Columns

Averagerate

Float

None

None

Currencykey

INT (FK)

Dimcurrency

Currencykey (PK)

Timekey

INT (FK)

Dimtime

Timekey (PK)

Endofdayrate

Float

None

None

Map source data to target compatibility

The Analysis of source and target data indicates the need to find the currencykey and timekey values. Convert the search results to obtain the currencykey and timekey values by using the backup keys in the dimcurrency and dimtime dimension tables.

Flat file Column Table Name Column name Data Type

0

Factcurrencyrate

Averagerate

Float

1

Dimcurrency

Currencyalternatekey

Nchar (3)

2

Dimtime

Fulldatealternatekey

Datetime

3

Factcurrencyrate

Endofdayrate

Float

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.