Importing the  Excel file into SQL Server via SSIS - Excel data is not in table format

Posted on 2010-01-06
Medium Priority
Last Modified: 2013-11-10
Hi Experts,
I am new to the SSIS.  
Could you please point me a right direction as I do not know what resources or ways are available to solve the issue.  

I am trying to import the Excel data ( not table format) into the SQL Server via SSIS.
The error pop up message shows up as, "External table is not in the expected format".  
Do I need to change the Excel data writing code in VB(A) before implementing the SSIS importing?

Question by:taromor
  • 3
  • 2
LVL 15

Expert Comment

ID: 26197425
LVL 30

Expert Comment

by:Reza Rad
ID: 26197492
did you configure data source and data destination correctly?
I mean did you select table data in source?  and did you select an excel sheet in destination ?
if it doesn't helped provide screen shots from configuration of source and destination or upload your package here?

Author Comment

ID: 26197819
Hi reza rad,
Thanks for your comment.  

Yes I configure the data souce and data destination correctly.  
I tested with the data includes a column heading in row1 and data from row2 onward.  In this case, I could succesfully import the Excel data into the SQL Server table.  (Please see the xls attachement "Testing Case_OK" worksheet.

the second worksheet case, "Case with not table formatted" - the table is not beginning from Range("A1").  I want to skip the row 1 to row 4.  


What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 30

Accepted Solution

Reza Rad earned 1000 total points
ID: 26198951
If you want to select specific rows you have two option:

1- write a query instead of selecting sheet name in excel data source like this:

SELECT        F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12
FROM            ['Case with not table formated$']

in this way you need to specify your rows by a where clause, in this example all rows that has F2 not null was fetched. but you must find a way to specify your data in where clause.

2- use an OLE DB Data source instead of excel, and connect to sql server
write a query with OPENROWSET() and fetch data from excel.
like this:

remember you can add row_number() to your query results and then select any row_number you want from the results.

Does it make sense to you?


Author Closing Comment

ID: 31673806
Thanks for you time and pointing me the directions.  
Great comments with clear explanations.  
LVL 30

Expert Comment

by:Reza Rad
ID: 26207320
Glad to help

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question