Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Work with Sample Data in SQL Server Integration Services SSIS



By:
Overview

Like any ETL tool, Integration Services is all about moving and transforming data. In this tutorial, we’ll also want to extract data from a certain source and write data to another source. In many cases, either the source or the destination will be a relational database, such as SQL Server. In this tutorial, we’ll use the Wide World Importers sample database.

Explanation

We will setup databases that can be used for testing and learning more about SSIS.

Wide World Importers

Wide World Importers is an open-source sample database provided by Microsoft. Its main use is to showcase different features of SQL Server using data resembling a real-life company. You can use this database to test out functionality, but also to write your own scripts to test performance or just to get to know SQL Server and the T-SQL language a bit better. There is an OLTP database available and a data warehouse (which uses the OLTP database as a source). All of the material – database backups, scripts to generate data, applications et cetera – can be found on Github.

In this section of the tutorial, we will restore the Wide World Importers database from a backup, as it is the easiest option. If you want to learn more about Wide World Importers, you can check out these tips:

Restoring the Wide World Importers Backup

You can download a backup of the Wide World Importers database from here. This backup currently contains data from 2013-01-01 till 2016-05-31. Make sure you have a SQL Server instance available (installing and configuring SQL Server is not part of this tutorial). Since there are new features present in this backup, you need SQL Server 2016 or later. If you upgrade to SP1, you can use all features that were previously Enterprise edition only, such as compression or columnstore indexes. You can find more information on which features are present in which edition in this overview. You could also use SQL Server 2016 Developer edition, which is free and has all the features available.

We are going to restore the backup using SQL Server Management Studio (SSMS). Right-click on the Databases node and select Restore Database…

restore database context menu

Change the source to Device and click on the ellipsis.

choose device

In the Select backup devices menu, click on Add.

add backup media

Next, we have to choose the backup file we downloaded from Github. By default, the explorer shows the folder that has been configured as the default backup folder for the SQL Server instance. Either move your backup file to that folder, or navigate to the directory where you have saved the .bak file.

add backup file

Click OK twice until you’re back in the Restore Database menu. We have one more thing to do before we can restore the backup. Go to the Files pane.

restore database menu after selecting backup file

In Files, choose to relocate all of the database files to the default SQL Server folders (which you can configure during the SQL Server set-up).

relocate all files

Now you can click on OK to start the restore procedure. Depending on your machine, this might take some time. To restore the Wide World Importers data warehouse, you can follow the exact same steps. You can find the .bak file here.

If you have followed all the steps, you should now have two new databases in your SQL Server instance:

wwi databases
Additional Information

Last Update: 8/17/2017




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools