Q: How do I set up automatic imports?

A: Automatic imports may be set up through SQL Server Management Studio (SSMS). This feature requires the full version of SQL Server (not SQL Express). You may import employees, training records, completions, or requirements. Employee and Training records may be imported or updated. Completion and Requirement records will only be imported.

Start the Import Wizard.

1. In SQL Server Management Studio, locate your database in the Object Explorer pane at the left hand side of the screen.
2. Right-click on the name of the database and choose: Tasks -> Import Data...
   
   
   Select a Data Source
   
3. Choose the type of file that contains your data, usually Microsoft Excel.
4. For an Excel spreadsheet you must say where the file is saved.
5. Different versions of Excel store their data in different ways so it's important to say which version your file is saved as. Excel 2007 and 2010 file types are grouped together as Microsoft Excel 2007.
6. Specify whether the first row of your data contains the column headings.
7. Click Next at the bottom of the dialog box.
   
   Choose a Destination
   
8. Choose the type of destination source for your data (SQL Server table).
9. Indicate which SQL Server to use.
10. Specify the authentication method to be used to connect to the server you've chosen.
11. Choose the database you want to send the data to.
12. Click the Next button at the bottom of the wizard.
13. Choose the tables, or in this case worksheets, you want to import the data from.
14. Choose which table the selected worksheet will be imported to.
15. Click Edit Mappings... for more options.
16. Choose what to do with data that is already in the table you are importing into. We will be appending the data.
17. Use the Mappings table to control the names and data types of the imported fields.
18. Click OK and then Next.
19. Do not run the import when the wizard ends.
    
    Create an SSIS Package
    
20. Select to save an SSIS Pack that contains all of the information required to run the import at a later time. Save the package onto the SQL Server or as a separate file on your computer.
21. Choose the level of protection for the package.
22. Click Next at the bottom of the dialog box.
23. Type in a recognizable name for the package - you'll need this later on when you choose to schedule the import steps. You can optionally type in a description here.
24. Choose the server or filename to save the package depending on the option you selected in the previous step of the wizard.
25. Click Next at the bottom of the dialog box.

Create a Job
To create a new job using SQL Server Agent:

1. Select the Steps page of the dialog box.
2. Click the New... button to add a new step to the job.
3. Enter a sensible name for this step of the job.
4. Choose the type of action to perform (execute an SSIS package).
5. Choose where the SSIS package is stored (select the package that you saved earlier).
6. Click the ellipsis (...) to choose the package you want to execute.
7. Click OK to return to the New Job dialog box.

Schedule the Job

1. Select the Schedules page of the dialog box.
2. Click New... to create a new schedule.
3. Complete the dialog box.
4. Click OK to return to the New Job dialog box.

Tags: auto imports, importing, schedule imports, automatic imports, importing records, importing data.