Regularly import data from multiple Excel to SQL database

Scheduling Data Imports in SQL Server

importing data into a SQL Server database is ' t really that Tricky:there's a straightforward wizard so you can follow T  o 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? Have to go through the wizard on a weekly, daily, or even hourly basis could become very tedious very quickly!

Fortunately, there's a-to-set up a scheduled data import in SQL Server meaning so you don ' t has to go through the s  AME process each time you want to import a set of data. There is both main steps involved in this process:

1. Use the SQL Server Import and Export Wizard to create a SQL Server integration Services (SSIS)package.
2. Schedule a job which executes the SSIS package according the Schedule you want.

Launching the Import Wizard

The first step in this process is launching the wizard, used to import data. To does this:

1. In SQL Server Management Studio, locate your database in the Object Explorer pane at the left hand side of the SC Reen.
2. Right-click on the name of the database and choose: 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.

Using the Import Wizard in SQL Server

Once you have opened the Import Wizard there is several steps to follow in order to create a SSIS package. This part of the series explains "What are 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 U Seful part!

If you see the This message, just click Next.

Step 1-choosing a Data Source

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 is explained below.

1. Choose the type of file that contains your data.  Here we ' ve chosen Microsoft Excel. The option you select here affects the subsequent options on this page of the wizard.
2. For a Excel spreadsheet you must say where the file is saved.
3. Different versions of Excel store their data in Different ways so it's important to say which version your file is saved a S. Excel and file types is grouped together as Microsoft Excel.
4. Specify whether the first row of your data contains the column headings.
5. Click Next at the bottom of the dialog box.

If the following error occurs:

Title:sql Server Import and Export Wizard

The operation could not being completed.

ADDITIONAL information:the ' microsoft.ace.oledb.12.0 ' provider is not registered on the local machine. (System.Data)

Components required for download: https://www.microsoft.com/en-us/download/details.aspx?id=23734

Step 2-choosing a Destination Database

The next step is for the wizard which database you want the data to being imported to.

Specify where the data should go is imported. The numbered steps is explained below.

1. Choose the type of destination source for your data. The option you select here affects the other options on the This page of the wizard.
2. As we selected a SQL Server destination for Part 1, we need to say which SQL Server we want to use here.
3. This option specifies the authentication to is used to connect to the server where you have chosen.
4. Choose the database you want to send the data to, or click New ... to create a new database.
5. Click the Next button at the bottom of the wizard.

Step 6-saving the SSIS package

If you chose-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.

1. Type in a recognisable name is the Package-we ' ll need this later on while we choose to schedule the import steps.
2. You can optionally type in a description here.
3. Choose the server or filename to save the package depending on the option you selected in the previous step of the wizard.
4. Click Next at the bottom of the dialog box.

Step 7-completing the Wizard

The final step summarises the choices you has made and simply asks to your confirm everything you had 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 the tasks being performed in a separate dialog box.

success! Now all you need to does is click Close.

Viewing an SSIS package Saved as a File

You can view your SSIS package in one of the different ways, depending on whether your saved it as part of your computer ' s File system or on the SQL Server.

If the package was saved as a file on your computer you can simply browse for it in a Windows Explorer window.

The file would have a dtsx extension.

Viewing an SSIS package Saved in SQL Server

In order to view a package saved in SQL Server must first connect to an SSIS server:

Click Connect at the top of the Object Explorer window and choose integration Services ...

Need to use Run as administrator to open

You'll be able to see your SSIS package by expanding the sequence of folders shown below:

The SSIS package, the We created earlier is shown here.

You can even run the package by right-clicking on it and choosing run.

Simply Click Execute to run the import steps you saved earlier.

Step 3-choose which Data to Import

In this step, you can specify exactly which data, 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 in to the next step.

Step 4-configure the Tables to which the Data would be imported

This step lets your choose which tables the data you is importing would end up in, and also what should happen to the exist ing data in those tables.

Choose what would be imported and where it'll be imported to. The numbered steps is described below.

1. Choose the tables, or in this case worksheets, you want to import the data from.
2. Choose which table the selected worksheet would be imported to.
3. Click Edit Mappings ... for more options, as described below.

Use this dialog box to customise the individual, the imported data.

4. Choose-to-do with data, is already in the table, importing into. Here we ' re creating the destination table with each time.
5. We ' ve also chosen to drop and re-create the destination table each time we run the import.
6. Use the Mappings table to control the names and data types of the imported fields.
7. Click OK and then Next.

Step 5-choose when to Run the Import Steps

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.

1. We ' ve chosen to not run our import when the wizard ends.
2. We ' ve also chosen to save a SSIS package This contains all of the information required to run the import at a la  ter time. We ' ve selected to save the package onto the SQL Server, rather than as a separate file in our computer.
3. Choose the level of protection for the package. Here we ' ve chosen not to save sensitive data in the package, meaning that if and people attempt to use this package they 'll is prompted to fill in the missing information.
4. Click Next at the bottom of the dialog box.

Step 7-completing the Wizard

The final step summarises the choices you has made and simply asks to your confirm everything you had 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 the tasks being performed in a separate dialog box.

success! Now all you need to does is click Close.

Viewing an SSIS package Saved as a File

You can view your SSIS package in one of the different ways, depending on whether your saved it as part of your computer ' s File system or on the SQL Server.

If the package was saved as a file on your computer you can simply browse for it in a Windows Explorer window.

The file would have a dtsx extension.

Viewing an SSIS package Saved in SQL Server

In order to view a package saved in SQL Server must first connect to an SSIS server:

Click Connect at the top of the Object Explorer window and choose integration Services ...

You'll be able to see your SSIS package by expanding the sequence of folders shown below:

The SSIS package, the We created earlier is shown here.

You can even run the package by right-clicking on it and choosing run.

Simply Click Execute to run the import steps you saved earlier.

Scheduling a Job in SQL Server

The final stage of this blog series are to create a scheduled job to execute the SSIS package on a regular basis.

The SQL Server Agent

You can schedule jobs using the SQL Server Agent. You should find this at the bottom of the list of objects in any database server so you ' ve connected to in SQL Server Ma Nagement Studio:

The SQL Server Agent appears at the bottom of the list of objects in a database server.

Creating a Job

To create a new job using SQL Server Agent:

Right-click the Jobs folder and choose New Job ...

You can then use the dialog box to set up the job for you want to create. The steps we need to follow in order to schedule our SSIS package execution is described below.

Step 1-enter a Name for the Job

The first step is to give the new job a sensible name, as shown below:

In the category, enter a descriptive name for the job.

Step 2-create the Job Steps

Next, you can create the steps. Our job should has only one step and here's how to create it:

1. Select the Steps page of the dialog box.

Build a list of steps for the job using this page of the dialog box.

2. Click the new ... button to add a New step to the job.

Use this dialog box to specify the settings for this job step.

3. Enter a sensible name for this step of the job.
4. Choose the type of action to perform. Here we want to execute the SSIS package.
5. Choose where the SSIS package is stored. Here we've selected the package, we saved earlier on our SQL Server.
6. Click the ellipsis (...) to choose the package for your want to execute.
7. Click OK to return to the New Job dialog box.

The job should now consist of a single step:

Our entire job consists of a single step, but we could always add more to this by clicking the New ... button at the bottom of the dialog box.

Step 3-creating the Job Schedule

To ensure this job runs at a specific time you need to specify the schedule for the job. To does this:

1. Select the Schedules page of the dialog box.

Use this page to set up the job schedule.

2. Click New ... to create a new schedule.
3. Complete the dialog box as shown below:

The options in this page of the dialog box is self-explanatory. The options we have selected here ensures the job is carried out each week at 9am on a Monday morning.

4. Click OK to return to the New Job dialog box.

Step 5-creating the Job

When you had finished applying all of the settings listed above, you can create the job by simply clicking OK on The New Job dialog box.

Your new job would appear in the Jobs folder within SQL Server Agent.

After you continue adding the second excel,task--> Import data, follow the steps below to add schedule, and set the first step success then go to the next step.

Regularly import data from multiple Excel to SQL database