ETL 4: SQL server integration services

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