Discussion on:

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

It's good and clean explaination.

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

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

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.

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.

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

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?

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.

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

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.

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

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

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)

Very nice article.

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.

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!!

Good...is clear and systematic.

Rgds,
BSLEE (lbseong@gmail.com)

The protocol is excellent and very helpful

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

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

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.

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 ?

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)

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.

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.

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

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.

Very good one, same steps can be used in SSIS 2008 also. Thanks Tim.

Kind Regards,
Dinesh
http://halpage.wordpress.com

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???

source:Excel file
Destination:SQL Server 2005

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

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.

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)

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

be happy.

Simple and very clear,
Tim thank you very much.

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.