Can I Use Bids to Upload Excel Data to Sql Server?

Inserting the data in the database table tin be hard and can take a long time. Especially today when the databases condign bigger and bigger. Automated data importing is possible using different methods, depending on source file blazon and data organisation within

The easiest way to import the information into the SQL database is using the Import Data magician from SQL Server Management Studio. Following the steps through the wizard, user can choose the source file, and the destination table to import the data. Withal, importing the data this fashion narrows the process and won't allow user to execute more than complex tasks with dissimilar kind of the data, and multiple files. The SSIS packages offering solution for this kind of problem. The SSIS package represents tool for the ETL (Extract-Transform-Load) processing, and it can exist used not merely to import the data into the database, but to transform, filter, group the information and many other tasks. This article will give explanation how to import the simple Excel worksheet into the SQL database

Before creating an SSIS package, the user needs to create new project in BIDS by choosing the Integration Services from the Business Intelligence list from the left, navigate to the Integration Services project , and define name for the project like shown in the paradigm below

Importing from an MS Excel worksheet

To be able to import the information from the Excel worksheet into the SQL database, the user first needs to define the Data Flow Task, dragging from the SSIS Toolbox into the Command Flow area similar shown in the image beneath

Double-clicking the Information Menstruation Chore volition open the Data Flow tab. Before selecting and configuring the source and the destination, connections for both of them must be created. Since the Excel worksheet is the source, the Excel Connectedness will be created, and for the destination, the OLE DB connection. Both of these connections can be created by right click on the Connectedness Director in the Solution Explorer, and choosing the New Connection Managing director from the drop down list, like shown in the image below

From the listing of the connections in the New Connectedness Manager dialog, the Excel connection will be used to connect the external Excel worksheet with the project. Clicking the Browse button, Excel file can be connected with the task like shown in the epitome below

Note: The external Excel worksheet must take the "xls" extension.

Connectedness with the destination, in this case SQL Server database, can be established through the OLE DB connection, choosing it from the listing in the New Connection Director window.

In the Connection Manager window, Native OLE DB\SQL Server Native Client xi.0 must be selected from the Provider drop downwards list. In the Server Proper noun filed, if the server is local, information technology tin can be defined past entering "." (without quotes). Otherwise, proper server name must be typed

If the server name is correct, the user will be able to choose i of the databases from the server, where the information from the Excel worksheet will exist imported, like shown in the image below

When the connections with the source and the destination is created, the Data Period Tasks can be defined and configured in the Data Menstruation window. From the SSIS Toolbox, the Excel Source, and the OLE DB Destination tasks must exist imported into the Data flow window, equally shown in the image beneath

Double click on the Excel Source task will open the Excel Source Editor. Selecting the Excel connection from the Connection manager listing allows user to choose the Name of the Excel sheet from the listing, similar shown in the image below

In the Cavalcade tab, the user can choose what columns from the excel file will exist imported by checking the boxes for desired columns, and rename output columns, as shown in the image below

To configure the OLE DB Destination it must be connected to the Excel Source outset. Double click on the OLE DB Destination volition open the editor shown beneath

In the OLE DB connection managing director list, previously defined connection must be selected, in this case, connection is established with the AdventureWorks2012 database on the local server. From the table list, desired destination table tin exist chose, or new table can be created clicking the New push and entering the SQL syntax

After the destination database table is selected, the columns must exist mapped before importing the data. That could be done from the Mappings tab like shown in the prototype below

Not all the columns from the source need to exist imported. Mapping is a way of filtering the input data, and pointing to the specific columns in the database table, where the column from the source will be imported. Choosing the <ignore> from the input cavalcade listing will exclude it from importing

After the columns are mapped, the SSIS package can be executed past correct click on it from the Solution Explorer, and choosing the Execute Packet selection from the drib down list like shown in the image beneath

After executing the package all the data from the Excel worksheet will be imported in selected database table. The image on the correct side represents imported data into the SQL database, from the Excel worksheet to the left

Using this method, data from the same excel sheet can be imported in the selected tabular array until there is no modifications in excel with data types. If the data is inserted or overwritten with new ones, importing process will be successfully accomplished, and the data will be added to the table in SQL database

Useful resource

• Import data from excel to SQL server
• SSIS packages for information importing

• Author
• Recent Posts

Marko is an It and technical education teacher, who likes movies, video games, and heavy metal music.

He uses his spare time to play guitar, ride a bicycle and hang out with his friends. During winter, he likes skiing the near, simply all other snow activities, as well.

He is besides author of various SQL Shack articles about SSIS packages and knowledgebase articles about ApexSQL Md.

View all posts by Marko Radakovic

danielsaetherins.blogspot.com

Source: https://www.sqlshack.com/using-ssis-packages-import-ms-excel-data-database/