Import excel file to sql server table

Question

Import excel file to sql server table

keywestfl9

SSCertifiable

Points: 5215
More actions
January 23, 2008 at 8:56 am

#127521

i want to append rows from excel file to sql server table
Table has a identity column.
How do i achieve it thru SSIS or thru DTSWizard? I get error if i the identify column with null value in excel sheet.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

Check the 'Keep Identity' checkbox off in the Destination Editor (it's the FastLoadKeepIdentity property in the Properties box, which must be false).

Peter

Powichrowski Marek Hall of Fame Points: 3423 More actions · Answer 2

keywestfl9 (1/23/2008)
i want to append rows from excel file to sql server table
Table has a identity column.
How do i achieve it thru SSIS or thru DTSWizard? I get error if i the identify column with null value in excel sheet.

Identity column has null value? Impossible.

KennyClarck Ten Centuries Points: 1239 More actions · Answer 3

remove your identity column output before the import operation

//Ravi SSCrazy Points: 2612 More actions · Answer 4

Hi,

I do this all day. I don't know what's your knowledge level on SSIS, so I would assume it's basic.

use data flow task in SSIS. Under "Data Flow" tab, drag Excel source and OLE DB destination. Make connection to your excel file and database in Connection Manager. Make sure you are using excel 2003 sheet, It doens't recognize Excel 2007. In Data Flow tab, double click on excel source, slect appropriate connection, and then sheet. Click on Columns and check the columns you want to transfer. Take the green arrow and attach it the database destination task. Double click database destination task. Here is what's important, make sure you check "keep null values." Select correct database connection and the table. Its better to create the table here, becuase it creates the table by taking column names in your excel file. Also, make sure your column with null values isn't set as primary key. Then, execute the ask and you should be golden.

Ravi.

------------
🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Importing data into a live table isn't generally good practice unless it's a repetitive process and problems have been sorted on previous imports.

Instead, import into a staging table, which should be a full or partial copy (columnwise) of the target table with no constraints. Check the new data once it's in the staging table, then import into the live table. Then purge or delete the staging table. Much safer, much easier to check.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ab_abhisingh SSC Journeyman Points: 88 More actions · Answer 6

hi ;

i m working on vista operating system and i m connected with a remote server(sql server 2000) and i m also connected with sql sever 2005 on my own system.

i m able to import excel sheet in sql server 2005 by 'openrowset' but

i m unable to import a excel sheet on remote server (sql server 2000)

from my system....

pls help me .....

//Ravi SSCrazy Points: 2612 More actions · Answer 7

What DB engine are you using to connect to SQL 2000??

------------
🙂

ab_abhisingh SSC Journeyman Points: 88 More actions · Answer 8

actually i use sql server 2005 and i hv connected sql server 2000 which is on server..

and when i import an excel sheet which is on my own desktop to remote server by openrowset method then i face problem.......

pls tell me....

kescott Valued Member Points: 67 More actions · Answer 9

kescott

Valued Member

Points: 67

July 21, 2008 at 1:49 am

#845586

Is it possible to SQLBULKCOPY

gv_pradeep Valued Member Points: 71 More actions · Answer 10

Hi,

I faced a similar problem. I was using DTS to import data from excel to SQL. It was giving error because of an Identity column in my table. It got solved when i unchecked the "Use Fast Load" in my Options Tab of the Transform Data Task Properties 🙂

snappr761 Old Hand Points: 335 More actions · Answer 11

Hi,

I have an excel file that I need to keep dumping into a sql database as I add more records to the excel file. Instead of appending, I've been deleting relationships and refreshing all the data and then re-adding the relationships. I was hoping to find an append option in Business Intelligence Studio. Is this possible? I tried setting the KeepNulls option and FastLoadKeepIdentity to true. It doesn't want to append. Instead it says it violates the primary key rule. Which would be true because it's trying to re-load everything, not just the new records. Has anyone tried to do this? Thanks!

Phil Parkin SSC Guru Points: 246961 More actions · Answer 12

Add a look-up to check whether the PK from your source already exists in your target data. If not, do the create, otherwise ... nothing.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

snappr761 Old Hand Points: 335 More actions · Answer 13

thanks! I'm learning this software so I'm not sure what a lookup is...is that another control to drag and drop? I'll research on google as well...thank you!

Phil Parkin SSC Guru Points: 246961 More actions · Answer 14

In your Dataflow, there's a Data Flow Transformation called 'Lookup' - that's the one I was referring to. Have fun learning 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin