BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
If you frequently import data into a SQL Server database from the same source you'll probably be sick of going through the import wizard again and again. So why not learn how to schedule an automatic import of your data using SSIS packages and the SQL Server Agent? This blog explains how to do exactly that!
Posted by Andrew Gould on 27 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Scheduling Data Imports in SQL Server
Importing data into a SQL Server database isn't really that tricky: there's a straightforward wizard that you can follow to get information from Microsoft Excel, Access and even text files. But what if you want to perform the same import of data on a regular basis? Having to go through the wizard on a weekly, daily, or even hourly basis could become very tedious very quickly!
Fortunately, there's a way to set up a scheduled data import in SQL Server meaning that you don't have to go through the same process each time you want to import a set of data. There are two main steps involved in this process:
- Use the SQL Server Import and Export Wizard to create a SQL Server Integration Services (SSIS) package.
- Schedule a job which executes the SSIS package according the schedule you want.
This blog explains the process using SQL Server 2008 R2 and an Excel 2010 spreadsheet.
If you need background information on some of the techniques mentioned in this blog, you might want to familiarise yourself with our SQL Tutorial blog series first (or book look at our other SQL training resources).
For this blog we're going to import a set of data related to movies from an Excel spreadsheet into an existing SQL Server database.
|This Excel spreadsheet...||will be imported into this SQL Server database.|
Launching the Import Wizard
The first step in this process is launching the wizard that is used to import data. To do this:
- In SQL Server Management Studio, locate your database in the Object Explorer pane at the left hand side of the screen.
- Right-click on the name of the database and choose: Tasks -> Import Data...
Choose this option to start the import wizard.
The next part of this blog series explains the steps to follow in the import wizard in order to create an SSIS package.