In this lesson, you create a simple ETL package that extracts data from a single flat file source, transforms the data using two lookup transformations, and writes the transformed data to a copy of the FactCurrencyRate fact table in the AdventureWorksDW2012 sample database. As part of this lesson, you learn how to create new packages, add and configure data source and destination connections, and work with new control flow and data flow components.
Before creating a package, you need to understand the formatting used in both the source data and the destination. Then, you be ready to define the transformations necessary to map the source data to the destination.
Prerequisites
This tutorial relies on Microsoft SQL Server Data Tools, a set of example packages, and a sample database.
- To install the SQL Server Data Tools, see Download SQL Server Data Tools.
- To download all of the lesson packages for this tutorial:
- Navigate to Integration Services tutorial files.
- Select the DOWNLOAD button.
- Select the Creating a Simple ETL Package.zip file, then select Next.
- After the file downloads, unzip its contents to a local directory.
- To install and deploy the AdventureWorksDW2012 sample database, see Install and configure AdventureWorks sample database – SQL.
Look at the source data
For this tutorial, the source data is a set of historical currency data in a flat file named SampleCurrencyData.txt. The source data has the following four columns: the average rate of the currency, a currency key, a date key, and the end-of-day rate.
Here is an example of the source data in the SampleCurrencyData.txt file:Copy
1.00070049USD9/3/05 0:001.001201442
1.00020004USD9/4/05 0:001
1.00020004USD9/5/05 0:001.001201442
1.00020004USD9/6/05 0:001
1.00020004USD9/7/05 0:001.00070049
1.00070049USD9/8/05 0:000.99980004
1.00070049USD9/9/05 0:001.001502253
1.00070049USD9/10/05 0:000.99990001
1.00020004USD9/11/05 0:001.001101211
1.00020004USD9/12/05 0:000.99970009
When working with flat file source data, it’s important to understand how the Flat File connection manager interprets the flat file data. If the flat file source is Unicode, the Flat File connection manager defines all columns as [DT_WSTR] with a default column width of 50. If the flat file source is ANSI-encoded, the columns are defined as [DT_STR] with a default column width of 50. You probably have to change these defaults to make the string column types more applicable for your data. You need to look at the data type of the destination, and then choose that type within the Flat File connection manager.
Look at the destination data
The destination for the source data is a copy of the FactCurrencyRate fact table in AdventureWorksDW. The FactCurrencyRate fact table has four columns, and has relationships to two dimension tables, as shown in the following table.
| Column Name | Data Type | Lookup Table | Lookup Column |
|---|---|---|---|
| AverageRate | float | None | None |
| CurrencyKey | int (FK) | DimCurrency | CurrencyKey (PK) |
| DateKey | int (FK) | DimDate | DateKey (PK) |
| EndOfDayRate | float | None | None |
Map the source data to the destination
Our analysis of the source and destination data formats indicates that lookups are necessary for the CurrencyKey and DateKey values. The transformations that perform these lookups get those values by using the alternate keys from the DimCurrency and DimDate dimension tables.
| flat file Column | Table Name | Column Name | Data Type |
|---|---|---|---|
| 0 | FactCurrencyRate | AverageRate | float |
| 1 | DimCurrency | CurrencyAlternateKey | nchar (3) |
| 2 | DimDate | FullDateAlternateKey | date |
| 3 | FactCurrencyRate | EndOfDayRate | float |
Lesson tasks
This lesson contains the following tasks:
- Step 1: Create a new Integration Services project
- Step 2: Add and configure a Flat File connection manager
- Step 3: Add and configure an OLE DB connection manager
- Step 4: Add a Data Flow task to the package
- Step 5: Add and configure the flat file source
- Step 6: Add and configure the lookup transformations
- Step 7: Add and configure the OLE DB destination
- Step 8: Annotate and format the Lesson 1 package
- Step 9: Test the Lesson 1 package
Start the lesson
Step 1: Create a new integration services project
Jobs in SSIS
Conclusion
SSIS is a platform for data integration and workflow applications. It features a data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.
🎥 Your FREE eLEARNING Courses (Click Here)
Related Courses
Oracle DBA 11g /12c
Microsoft SharePoint Developer
Oracle HCM Cloud – Fusion Human Capital Management
ETL with Microsoft SQL Server Integration Services (SSIS)
KEY FEATURES
Register Now
