SSIS - ETL tool

Brief:

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible 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.

Need:

SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. extract, transform and load data. While ETL processing is common in data warehousing (DW) applications, SSIS is by no means limited to just DW; e.g. when you create a Maintenance Plan using SQL Server Management Studio (SSMS) an SSIS package is created. At a high level, SSIS provides the ability to:

• Retrieve data from just about any source

• Perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.

• Load data into just about any source

• Define a workflow 

How It Works:

To create a new Integration Services project

1. On the Start menu, point to All Programs, point to Microsoft SQL Server, and click SQL Server Data Tools.
2. On the File menu, point to New, and click Project to create a new Integration Services project.
3. In the New Project dialog box, expand the Business Intelligence node under Installed Templates, and select Integration Services Project in the Templates pane.
4. In the Name box, change the default name to SSIS Tutorial. Optionally, clear the Create directory for solution check box.
5. Accept the default location, or click Browse to browse to locate the folder you want to use. In the Project Location dialog box, click the folder and click Select Folder.
6. Click OK.

By default, an empty package, titled Package.dtsx, will be created and added to your project under SSIS Packages. In Solution Explorer toolbar, right-click Package.dtsx.

To add a Flat File connection manager to the SSIS package 

1. Right-click anywhere in the Connection Managers area, and then click New Flat File Connection.
2. In the Flat File Connection Manager Editor dialog box, for Connection manager name, type Sample Flat File Source Data.
3. Click Browse.
4. In the Open dialog box, locate the SampleCurrencyData.txt file on your machine.
5. The sample data is included with the SSIS lesson packages. To download the sample data and the lesson packages, do the following.
6. Navigate to Integration Services Product Samples http://msftisprodsamples.codeplex.com/
7. Click the DOWNLOADS tab. 
8. Click the SQL2012.Integration_Services.
9.  Create_Simple_ETL_Tutorial.Sample.zip file.
10. Clear the Column names in the first data row checkbox.

To rename columns in the Flat File connection manager

1. In the Flat File Connection Manager Editor dialog box, click Advanced.
2. In the property pane, make the following changes:

               Change the Column 0 name property to AverageRate.
               Change the Column 1 name property to CurrencyID.
               Change the Column 2 name property to CurrencyDate.
               Change the Column 3 name property to EndOfDayRate.

To add and configure an OLE DB Connection Manager to the SSIS package

1. Right-click anywhere in the Connection Managers area, and then click New OLE DB Connection.
2. In the Configure OLE DB Connection Manager dialog box, click New.
3. For Server name, enter localhost. 
4. When you specify localhost as the server name, the connection manager connects to the default instance of SQL Server on the local computer. To use a remote instance of SQL Server, replace localhost with the name of the server to which you want to connect.
5. In the Log on to the server group, verify that Use Windows Authentication is selected.
6. In the Connect to a database group, in the Select or enter a database name box, type or select AdventureWorksDW2012.
7. Click Test Connection to verify that the connection settings you have specified are valid.
8. Click OK.
9. Click OK.
10. In the Data Connections pane of the Configure OLE DB Connection Manager dialog box, verify that localhost.AdventureWorksDW2012 is selected.
11. Click OK.

To add a Data Flow task

1. Click the Control Flow tab.
2. In the SSIS Toolbox, expand Favorites, and drag a Data Flow Task onto the design surfaceof the Control Flow tab.
3. On the Control Flow design surface, right-click the newly added Data Flow Task, click Rename, and change the name to Extract Sample Currency Data.
4. Right-click the Data Flow task, click Properties, and in the Properties window, verify that the LocaleID property is set to English (United States).

To add a Flat File Source component

1. Open the Data Flow designer, either by double-clicking the Extract Sample Currency Data data flow task or by clicking the Data Flow tab.
2. In the SSIS Toolbox, expand OtherSources, and then drag a Flat File Source onto the design surface of the Data Flow tab.
3. On the Data Flow design surface, right-click the newly added Flat File Source, click Rename, and change the name to Extract Sample Currency Data.
4. Double-click the Flat File source to open the Flat File Source Editor dialog box.
5. In the Flat file connection manager box, select Sample Flat File Source Data.
6. Click Columns and verify that the names of the columns are correct.
7. Click OK.
8. Right-click the Flat File source and click Properties.
9. In the Properties window, verify that the LocaleID property is set to English (United States).

To add and configure the Lookup Currency Key transformation

In the SSIS Toolbox, expand Common, and then drag Lookup onto the design surface of the Data Flow tab. Place Lookup directly below the Extract Sample Currency Data source.

1. Click the Extract Sample Currency Data flat file source and drag the green arrow onto the newly added Lookup transformation to connect the two components.
2. On the Data Flow design surface, click Lookup in the Lookup transformation, and change the name to Lookup Currency Key.
3. Double-click the Lookup CurrencyKey transformation to display the Lookup Transformation Editor.
4. On the General page, make the following selections:  1.  Select Full cache.  2.  In the Connection type area, select OLE DB connection manager.
5. On the Connection page, make the following selections:  1. In the OLE DB connection manager dialog box, ensure that localhost. AdventureWorksDW2012 is displayed.  2. Select Use results of an SQL query, and then type or copy the following SQL statement:

select * from (select * from [dbo].[DimCurrency]) as refTable
where [refTable].[

CurrencyAlternateKey] = 'ARS'
OR
[refTable].[CurrencyAlternateKey] = 'AUD'
OR
[refTable].[CurrencyAlternateKey] = 'BRL'
OR
[refTable].[CurrencyAlternateKey] = 'CAD'
OR
[refTable].[CurrencyAlternateKey] = 'CNY'
OR
[refTable].[CurrencyAlternateKey] = 'DEM'
OR
[refTable].[CurrencyAlternateKey] = 'EUR'
OR
[refTable].[CurrencyAlternateKey] = 'FRF'
OR
[refTable].[CurrencyAlternateKey] = 'GBP'
OR
[refTable].[CurrencyAlternateKey] = 'JPY'
OR
[refTable].[CurrencyAlternateKey] = 'MXN'
OR
[refTable].[CurrencyAlternateKey] = 'SAR'
OR
[refTable].[CurrencyAlternateKey] = 'USD'
OR
[refTable].[CurrencyAlternateKey] = 'VEB'

On the Columns page, make the following selections:

1. In the Available Input Columns panel, drag CurrencyID to the Available Lookup Columns panel and drop it on CurrencyAlternateKey.
2. In the Available Lookup Columns list, select the check box to the left of CurrencyKey.

Click OK to return to the Data Flow design surface.
Right-click the Lookup Currency Key transformation, click Properties.
In the Properties window, verify that the LocaleID property is set to English (United States) and the DefaultCodePage property is set to 1252.

To run the Lesson 1 tutorial package

1. On the Debug menu, click Start Debugging.
2. The package will run, resulting in 1097 rows successfully added into the FactCurrency fact table in AdventureWorksDW2012.
3. After the package has completed running, on the Debug menu, click Stop Debugging.