Excel To Sql Server 2005

HI Friends,

i was created on xls file in my dektop name (student) with 2 columns

sno sname marks

1 a 10

2 b 20

3 c 30

4 d 40

these records added to excel file only

Now : i created a table in sql server 2005

sno :numeric(18, 0)

sname :varchar(50)

marks :numeric(18, 0)

NOW in ssis package

1) i place excel datasource (selected the student excel sheet$1)

2) i placed a lookup controle and selected the server student table

Question : when we map the excel sno= server sno

ERROR : data type mismatch to any of the column ?

please give me the related steps

You need to use a data convertor component in the data flow and convert the columns to be the same as the destination. SSIS does not allow implicit conversion.|||

Hint: If you use the editor correctly there is no need to create the table in SQL.

Add a Excel source

Use Excel Editor to look for your xls file

Add an SQL Server Destination -->Use an OLE DB Provider

Establish the path from Excel to SQL Server

Confgiure destination using the OLE DB Provider

Use the SQL Destination Editor to generate the table -->important step

You are done.

Excel To DataBase (Duplicate Recoards)

for example i exported student table with sno as (PK)

how to handle the upload twise (som of the records already in the data base )

1) How to ignore the existing records in the database

2) How to modify the records which is already exist in the database

1) EXCEL DATA SOURCE(STUDENT.XLS)

2) DATA CONVERSION

3) DESTINATION DATABASE (SQL SERVER 2005 STUDENT TABLE)

this is i have please tell me how to ignore/update the exisitng records

koti

It may be a good idea to load the excel data into a staging table first. This would give you a lot more flexibility than trying to run sql statements on the excel file.

To ignore duplicate records, you could then just do a look up from the staging table to the destination table and only load the rows that fail the lookup.

To update the records is a bit more tricky, as you have to use the OLE DB Command and write the SQL statement yourself.

Hope this helps,

Chris

|||

from the 1st page of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

EXCEL -SL SERVER 2005

Hi friends

student table contain the 3 columns : SNO SNAME MARKS

by using these controles i can able to upload the records (which is not exist in the database)

Excel Source 1 --student.xls

Data Conversion 1 --for destination datatype convertion

Fuzzy Lookupmap with database student table with sno inner join

Conditional Split (if simularity =1 then ignored the record) else inserted the database

OLE DB Destination save the new records

+++++++++++++++++++++++++++++++++++NOW ++++++++++++++++++++++++++++++++++++

in student.xls containt 7 records (1-7)

in student table(server) containt 7 records (1-7)

but marks is diffrent from excel sheet NOW

i want to update the marks ,field only (may be tomorow more than 1 column i have to update)

== SIMULTANIOUSLY how to insert a new record and EXISTING RECORD update only marks ========

REGRADS

KOTI

Basically you want to update the row if it already exists in the target table. If that is the case, then this thread will help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1