Software

How to import an Excel file into SQL Server 2005 using Integration Services

Integration Services, which replaces Data Transformation Services (DTS) in SQL Server 2005, is a wonderful tool for extracting, transforming, and loading data. This article describes how you can use the new features of Integration Services to load an Excel file into your database.

Integration Services, which replaces Data Transformation Services (DTS) in SQL Server 2005, is a wonderful tool for extracting, transforming, and loading data. Common uses for Integration Services include: loading data into the database; changing data into to or out from your relational database structures; loading your data warehouse data; and taking data out of your database and moving it to other databases or types of storage. This article describes how you can use the new features of SQL Server 2005 Integration Services (SSIS) to load an Excel file into your database.

Note: There are several wizards that come with SQL Server Management Studio to aid you in the import and export of data into and out of your database. I will not look at those wizards; I will focus on how you can build a package from scratch so that you don't have to rely on the wizards. To begin the process, I open SQL Server Business Intelligence (BI) Development Studio, a front-end tool that is installed when you install SQL Server 2005. The BI Development Studio is a scaled down version of Visual Studio. Then I select New Integration Services Project and give the project a name. See Figure A. Figure A

Figure A

When the project opens, you will see an environment that may look familiar to you if you have used SQL Server DTS; some of the items of the toolbox are the same. For the purposes of this project, I am interested in dragging the Data Flow task item from the toolbar into the Control Flow tab. (The idea of a Data Flow task is one of the major differences between DTS and SSIS packages. In an SSIS package, you can control the manner in which your package logic flows inside of the Control Flow tab. When you need to manage the data aspects of your project, you will use the Data Flow task. You can have several different Data Flow tasks in your project -- all of which will reside inside the Control Flow tab.) See Figure B. Figure B

Figure B

Double-click the Data Flow task that you have dragged onto the Control Flow tab. The available options in the Toolbar have changed; I now have available Data Flow Sources, Data Flow Destinations, and Data Flow Transformations. Since I am going to import an Excel file into the database, I will drag the Excel Source item from the Toolbar onto the screen. See Figure C. Figure C

Figure C

The Excel Source item represents an Excel file that I will import from somewhere on my network. Now I need somewhere to put the data. Since my plan is to put the data into the database, I will need a Data Flow Destination. For the purposes of this example, I will choose SQL Server Destination from the Data Flow Destination portion of the toolbar and drag it onto my Data Flow tab. See Figure D. Figure D

Figure D

To designate which Excel file I want to import, I double-click the Excel Source item that I moved onto the screen. From there, I find the Excel file on the network that I want to import. See Figure E. Figure E

Figure E

I also need to designate the sheet from the Excel file that I want to import, along with the columns from the sheet that I want to use. Figures F and G depict these options. Figure F

Figure F

Figure G

Figure G

Now that I have defined my Excel source, I need to define my SQL Server destination. Before doing that, I need to indicate the Data Flow Path from the Excel file to the SQL Server destination; this will allow me to use the structure of the data defined in the Excel Source to model my SQL Server table that I will import the data into. To do this, I click the Excel Source item and drag the green arrow onto the SQL Server Destination item. See Figure H. Figure H

Figure H

To define the database server and database to import the data, double-click the SQL Server Destination item. I will define the server in which I will import the data, along with the database that the data will reside. See Figure I. Figure I

Figure I

I also need to define the table that I will insert the Excel data into. I will create a new table named SalesHistoryExcelData. See Figure J. Figure J

Figure J

Under the Mappings section, I define the relationship between the Input Columns (the Excel data) and the Destination Columns (my new SQL Server table). See Figure K. Figure K

Figure K

Once I successfully define the inputs and outputs, my screen will look like the one below. All I need to do now is run the package and import the data into the new table by clicking the green arrow in the top-middle of the screen, which executes my package. See Figure L. Figure L

Figure L

Figure M shows that my package has successfully executed and that 30,000 records from my Excel Source item have been transferred to my SQL Server destination. Figure M

Figure M

You can download the Excel file I used for this article.  

Tasks in SSIS packages

Importing and exporting data are some of the simplest, most useful tasks to accomplish in SQL Server. However, there are literally hundreds of other tasks that can easily be accomplished in SSIS packages that will take a significant amount of time to do by a different means. I plan to take a look at several more of these tasks in future articles.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

Get SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

46 comments
Luis Enrique
Luis Enrique

if have an field type date in Excel to destination sql server 2000 add append format dd/mm/yyyy hh:mm:ss

mcintg
mcintg

Thanks, a really well written guide , so much better than these video guides that people insist on making

nivedita09
nivedita09

Thanks for the post.it was really nice and helped me out... :-)

can i import the result set of a query as a table into database through ssis????

runa1
runa1

Thanks!! This is really useful. However I want to import excel file into a database on a remote server whereas this allows me to do it only on a local computer. Any ideas for that? Thanks.

olugbenja
olugbenja

Simple and very clear, Tim thank you very much.

sarah hh
sarah hh

so useful !!! thanks ! I can solve my problem by your article. be happy.

amy_dke
amy_dke

Hi, the integration of one excel file works very well! I think thats Great :) I'm new in SQL Server and i have some problems... I hope I get some helpful anwsers.... Now, i want to import maybe 10 files from different years of similar excel data (e.g. my ticket files have same column names but in some excel files are not allways the complete columns - some columns are missing...) But the first step is to integrate a set of excel files - so what do you think i could to? Have you any ideas? I'm thankful for all your anwsers. greatings amy :o)

muralekarthick
muralekarthick

Hi i am new to sql server, i have situation were i need to export data from excel to sql server, were in case it is linked with many tables with key relation ship, can you help me in suggesting how to achieve this, if incase data from excel sheet needs to go multiple tables which has key relationship.

ll.
ll.

we can do one more thing just eat & sleep.................. u will get grrrrrrrrrrrrrrrrrrrrrr................rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr patchak

mahe7887
mahe7887

source:Excel file Destination:SQL Server 2005

alarconspawn
alarconspawn

Thanks Tim for this article. I find this very helpfull, but I have a problem. How can I do for load excels files with a dynamic name??? it's for that I have to load files for a storage in SFTP but i don't know the name of the file, I onle know that there exist a file with extension .xls ... please help me???

Hugonne
Hugonne

Very nice article. How would this work on Project files? I already implemented this solution for Excel files, but I need to do the same for MPP files. Any thoughts? Thanks.

manub22
manub22

How can I integrate an excel source file whose name is not constant/not same? The excel file name changes over time... code please???

rk325
rk325

I created a package that does the following: - If table exists drop it and create it again. - Copies content form excel file to that table. The package runs well....I even see the message in the "Execution results": [DTS.Pipeline] Information: "component "OLE DB Destination" (410)" wrote 17 rows. But when I query the table, it is empty. Any ideas why? Oh yes, I have the correct names, typing, syntax, etc. I don't know what else to do. Thanks, your quick response will be VERY appreciated.

deepak_p
deepak_p

Hi, i have tried most of the solutions posted on the net to schedule or to atleast run the job from SQL Server Agent, nothing is working, i am just getting the error message mentioned below... 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:42:16 AM Error: 2009-02-25 04:42:28.61 Code: 0xC0202009 Source: DHS_MSP_INSP_LOAD Connection manager "DHS_MSP_LAPTOPS_PLAN" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2009-02-25 04:42:28.61 Code: 0xC020801C Source: IMPORT PLAN Excel Source [2311] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DHS_MSP_LAPTOPS_PLAN" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2009... The package execution fa... The step failed.

shiv_dsnair
shiv_dsnair

Thanks for posting this article. Its very clearly return for the new beigneers like me. I did tried this but it throughs an error message. Could you please help. I am getting the dataconversion issue. error attached below: =================================== Package Validation Error (Package Validation Error) =================================== Error at Data Flow Task [SQL Server Destination [14]]: The column "ReadingDate" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported. Error at Data Flow Task [SQL Server Destination [14]]: The column " HOURENDING" can't be inserted because the conversion between types DT_R8 and DT_UI1 is not supported. Error at Data Flow Task [SQL Server Destination [14]]: The column "FLOWATMETER" can't be inserted because the conversion between types DT_R8 and DT_NUMERIC is not supported. Error at Data Flow Task [SQL Server Destination [14]]: The column "TRANSLOSSES " can't be inserted because the conversion between types DT_R8 and DT_NUMERIC is not supported. Error at Data Flow Task [SQL Server Destination [14]]: The column "EXCESSMAX" can't be inserted because the conversion between types DT_R8 and DT_NUMERIC is not supported. Error at Data Flow Task [SQL Server Destination [14]]: The column "GRYPORTIONMISO" can't be inserted because the conversion between types DT_R8 and DT_NUMERIC is not supported. Error at Data Flow Task [SQL Server Destination [14]]: The column "SHORTFALL" can't be inserted because the conversion between types DT_R8 and DT_NUMERIC is not supported. Error at Data Flow Task [SQL Server Destination [14]]: The column "PPADISPATCH" can't be inserted because the conversion between types DT_R8 and DT_NUMERIC is not supported. Error at Data Flow Task [SQL Server Destination [14]]: The column "CEPORTION" can't be inserted because the conversion between types DT_R8 and DT_NUMERIC is not supported. Error at Data Flow Task [DTS.Pipeline]: "component "SQL Server Destination" (14)" failed validation and returned validation status "VS_ISBROKEN". Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration) ------------------------------ Program Location: at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

dbhudia
dbhudia

The older Import function in Enterprise Manager would create a new table based on the schema of the Excel file. It was a nice feature to move data into a staging, is there anything like this in SSIS? Or must i create a schema first, then map and load ?

coder.anil
coder.anil

got error : selected connection manager uses an earlier version of a SQL server provider. Bulk insert operations require a connection that uses a SQL server 2005 provider.

priya.10
priya.10

Wht if the excel sheet file name changes. Do we need to have the sme name everytime??

ptk921
ptk921

Alas, not quite what i'm looking for. I'm wondering if you can possibly point me in the right direction? I have an excel file with multiple sheets of data that are formatted for print, not for database import. I've written VBA scripts before inside excel automate the "cleanup" process to parse the data into a friendlier format, but i'm wondering if I can automate this inside of SQL management studio then dump the resulting data into my SQL server. Thanks! Philip

lbseong
lbseong

Good...is clear and systematic. Rgds, BSLEE (lbseong@gmail.com)

gabriel.defigueiredo
gabriel.defigueiredo

This procedure does not work if there are mixed types in a column, e.g. a Serial Number may be alf-numeric, or numeric. When the package encounters all numerics after a series of alpha-numerics in the column, it imports the column for the row as a NULL. It appears that the import routine relies on an underlying Jet 4.0 engine. One has absolutely no control on the types one wants to import, instead, Jet 4.0 thinks itself intelligent when it is really dumb. Oh why can't the programmer have some control? The above is the same issue as faced by me when I use C# with Jet 4.0. Even if IMEX=1 is used in the connect string, Jet 4.0 stupidly converts a long numeric string into scientific notation, or something that to Jet appears as a date (e.g. a serial number 02012005) to a date, when all I want is a string! Aargh!!

Benslein
Benslein

I found your blog very helpful in how to import and Excel file into SQL Server. However I am running into some trouble which I have unfortunately found no solution to on the internet! I am trying to transfer data from a flat file to a database and for some reason the process will not execute. I have been able to create a new table in the database with this data but cannot insert my data into a column of an already created table. If you have any ideas or words of encouragement I would very much appreciate it.

MyEmail
MyEmail

I am receiving this error message when trying to drag the data flow task onto the control flow tab, any recommendations?? Object reference not set to an instance of an object. (Microsoft.DataTransformationServices.Design)

danejol
danejol

horrey, its great!now we can work as easy as we need, simple conversion

artmontz
artmontz

Very nice. Some additional complexity and more variation of data types (ie: Date) would have been welcomed.

BOUND4DOOM
BOUND4DOOM

Great starting article and I look forward to several more, this is a very powerful tool and I have used it a lot. I walked through the article on a VM, and set everything up kind of like what I would do normally. One of the little gotchas that might hit a few people in this article if you use a login other than yourself like a normal SQL login, that user must be in the Security Group on the server for bulkadmin. They could be in sysadmin group as well but that would be plain silly to put a normal DB user in there.

bill.bowman
bill.bowman

This is great tip, very useful, but what provides the greatest benefit would be accomplishing this without manual intervention. I would like the table to be populated automatically. What's the best way to accomplish this, using triggers, stored procedures, etc... I'm not quite a newbie, but have only used stored procedures and manual import/exports.

iatanasov
iatanasov

It's good and clean explaination.

dspeer
dspeer

I run into the same problem, both with flat files and Excel files. It will import into a new table, but will not import into the existing table. I'd also love some help.

phuiberts
phuiberts

This is all nice, but when I receive a package from my neighbor, the SSIS packages have a habit of not working anymore... with the result that they become a black box and nobody remembers what and why it was constructed.

chapman.tim
chapman.tim

Thank you. ...plenty more complexity to come in my future SSIS articles.

chapman.tim
chapman.tim

Thank you. I typically write an article w/ the assumption that the person doing the example is on a dev box with admin privileges on the DB side. In a real world example, this situation would probably be handled by the DBA scheduling the import to run as a job (recurring) or by hand (one time) and it would run under their privileges.

chapman.tim
chapman.tim

Well, I think this might be a different situation then what you are looking for...or I am not understanding what you want. This was really just showing how to import a file into SQL Server from an outside source. Tell me a little more about what you are trying to accomplish and I can probably help you out.

slawrence
slawrence

I haven't done much with SSIS but with DTS you would manually create the package and then you could save and schedule the package to run periodically from within SQL Server.

tv_p
tv_p

Well explained article with working sample.

Grant Russel
Grant Russel

the best, simplest explanation of how to do an SSIS package I have seen. Thank you.

frosenberg
frosenberg

Hi and thanks for this article, a common task i have is to import excel worksheets into a table, but the excel file changes each week. It might be useful if you could show people how to script a call to the SSIS package, using the Excel file name as a variable. And possibly one further step--making the destination table name a variable. this would be useful for me, too. Thanks! Fred

chapman.tim
chapman.tim

You can still do the scheduling with SSIS and SQL Agent...its relatively simple to do.

chapman.tim
chapman.tim

Thank you. I'm glad you liked it. There will be several more in the future.

chapman.tim
chapman.tim

No problem at all. I will definetly add that to my list of future SSIS articles.

micaman
micaman

Can you explain how you do it? I have a SSIS package and I want it to run without any user intervention. Is it possible?