SSIS Tutorial: Creating a Simple ETL Package
Microsoft SQL Server Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing. SSIS includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, executing SQL statements, and sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service, the Integration Services service for administering package execution and storage; and application programming interfaces (APIs) for programming the Integration Services object model.
In this tutorial, you will learn how to use SSIS Designer to create a simple Microsoft SQL Server Integration Services package. The package that you create takes data from a flat file, reformats the data, and then inserts the reformatted data into a fact table. In following lessons, the package is expanded to demonstrate looping, package configurations, logging and error flow.
When you install the sample data that the tutorial uses, you also install the completed versions of the packages that you will create in each lesson of the tutorial. By using the completed packages, you can skip ahead and begin the tutorial at a later lesson if you like. If this is your first time working with packages or the new development environment, we recommend that you begin with Lesson1.
This tutorial is intended for users familiar with fundamental database operations, but who have limited exposure to the new features available in SQL Server Integration Services.
To use this tutorial, your system must have the following components installed:
SQL Server with the AdventureWorksDW2012 database. To enhance security, the sample databases are not installed by default. To download the AdventureWorksDW2012 database, see Adventure Works for SQL Server 2012.
When you attach the database (*.mdf file), SQL Server Management Studio will by default search for an .ldf file. You must manually remove the .ldf file before clicking OK in the Attach Databases dialog box.
For more information about attaching databases, see Attach a Database.
Sample data. The sample data is included with the SSIS lesson packages. To download the sample data and the lesson packages, do the following.
Navigate to Integration Services Product Samples
Click the DOWNLOADS tab.
Click the SQL2012.Integration_Services.Create_Simple_ETL_Tutorial.Sample.zip file.