Data import from Excel 2003 file

Question

Data import from Excel 2003 file

jacksonl.connect

Grasshopper

Points: 22
More actions
January 20, 2011 at 4:13 pm

#230696

Hi
I would like to know how to import data from an Excel file into a table that has a primary key of type uniqueidentifier and is auto generated using the newid(). Following is the create table:
CREATE TABLE [dbo].[Schools](
[SchoolId] [uniqueidentifier] NOT NULL CONSTRAINT DEFAULT (newid()),
[SchoolName] [nvarchar](556) NOT NULL,
[RegistrationNumber] [nvarchar](50) NULL
)
In my excel file, I have two columns: SchoolName and RegistrationNumber. There are about 5000 records to read.
I tried using SSIS but could not figure out a way to reference the SchoolId field. Is there any working example that I can use as a reference with SSIS to do the job.
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

The ID autogenerates - you should just be able to leave it blank and map to the other two columns, in the knowledge that it will look after itself. Why did you think you needed to 'reference' it?

jacksonl.connect Grasshopper Points: 22 More actions · Answer 2

I did ignore the id column but it says the conversion is wrong when i debug.

Following are my mapping:

NOTE: I have added more fields.

Following are the errors when i try to debug

Please advise how i can fix this.

Thanks.

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

You have to use the standard 'Derived Column' transformation to transform the source columns into the appropriate destination column types.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

CozyRoc (1/27/2011)
You have to use the standard 'Derived Column' transformation to transform the source columns into the appropriate destination column types.

Or if you have creating expressions in the Derived Column editor, you can also use the Data Conversion component. That is what it's for.

The errors that you get don't have anything to do with the SchoolID, but with DistrictID, PhysicalAddress and PostalAddress, as the data types don't match.

Oh yeah, don't use the TEXT data type in SQL Server, it is deprecated.

As a side notice: you can use the SQL Server destination only if SSIS and the SQL Server database are on the same server.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP