By: Koen Verbeeck
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:
- Installing new SQL Server sample databases: WideWorldImporters
- Install SQL Server 2016 Sample Database: Wide World Importers Data Warehouse
- Generate more data for the Wide World Importers sample SQL Server databases
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…
Change the source to Device and click on the ellipsis.
In the Select backup devices menu, click on Add.
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.
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.
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).
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:
Additional Information
- Check out these other SSIS tips
Last Update: 8/17/2017
