563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Scheduling Data Imports in SQL Server
Part two of a three-part series of blogs
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!
Once you have opened the import wizard there are several steps to follow in order to create an SSIS package. This part of the series explains what you need to do.
Before we get started, if you've opened the wizard and you see a welcome message, just click Next to get to the useful part!
If you see this message, just click Next.
The first real step of the wizard asks you where your data comes from.
Tell the wizard where your data is stored. The numbered steps are explained below.
The next step is to tell the wizard which database you want the data to be imported to.
Specify where the data should go when it is imported. The numbered steps are explained below.
In this step you can specify exactly which data you want to import from the data source you selected in step 1.
Here we're going to select all of the data from the Excel spreadsheet, rather than write a separate query to get the data. Click Next to move on to the next step.
This step lets you choose which tables the data you are importing will end up in, and also what should happen to the existing data in those tables.
Choose what will be imported and where it will be imported to. The numbered steps are described below.
Use this dialog box to customise the individual fields in the imported data.
In this step you can choose whether to run the import immediately or to save the import steps for use later on.
Choose when you want the import to happen.
Users of SQL Server Express Edition should be aware that they won't be provided with the option to save the import steps as an SSIS package and so can only choose to execute the import steps immediately.
If you chose to save the package in the previous step you can specify the name and location here.
Save the SSIS package according to the options you select here.
The final step summarises the choices you have made and simply asks to you to confirm everything you have done by clicking Finish at the bottom of the dialog box.
Simply click Finish to confirm the settings you have applied.
If everything works properly you should see a list of tasks being performed in a separate dialog box.
Success! Now all you need to do is click Close.
You can view your SSIS package in one of two different ways, depending on whether you saved it as part of your computer's file system or on the SQL Server.
If the package is saved as a file on your computer you can simply browse for it in a Windows Explorer window.
The file will have a dtsx extension.
In order to view a package saved in SQL Server you must first connect to an SSIS server:
Click Connect at the top of the Object Explorer window and choose Integration Services...
You will be able to see your SSIS package by expanding the sequence of folders shown below:
The SSIS package that we created earlier is shown here.
You can even run the package by right-clicking on it and choosing Run Package.
Simply click Execute to run the import steps you saved earlier.
Although we can run the SSIS package by right-clicking on it, it's much more useful to set up a schedule on which to to execute it. Read the next part of this series to find out how to do this, or have a look at some of the other training we offer in SQL.
|Parts of this blog|
25 Aytoun Street