asked on

Populate data from excel file into sql server using SSIS

I have 1 question. I will devide that into 2. I have exel file called supplier.xls.
I have table on sql server 2000 called enc.suppliertable. I also have sql server
2008 on another box. Link servers are definded.

1. I want to populate some data from that excel file into sql server 2000
database called enc.supplieartable

2. Polulate data from other tables.

At present I am doing through coding but I want to do this using SSIS.
I am begginer of SSIS. Please help me.

1. Fields in enc.supplieartable which I want to populate from supplier.xls

---------------------------------------------------------------------------------------------------
Fields on ELECBILLDATAREC                  Fields on xls Sheet
Table
-------------------------------------------------------------------------------------------------------
supplier_acc_no                        Account ID
invoice_date                              Invoice Date
invoice_no                              Invoice No
period_from                              Start Date
period_to                              End Date
estimate                              Read type
curr_reading1                    Start Read
curr_reading1_2                        Start Read
curr_reading1_3                        Start Read
curr_reading2                        Start Read
curr_reading2_2                        Start Read
curr_reading2_3                        Start Read
curr_reading3                        Start Read
curr_reading4                        Start Read
curr_reading5                        Start Read
prev_reading1                        End Read
prev_reading1_2                        End Read
prev_reading1_3                        End Read
prev_reading2                        End Read
prev_reading2_2                        End Read
prev_reading2_3                        End Read
prev_reading3                        End Read
prev_reading4                        End Read
prev_reading5                        End Read
max_demand                        NA
availability                         NA
power_factor_change                        NA
other_charges                        NA
supplier_total                         Value
supplier_vat                              Value
supplier_total_inc_vat                         Value

2. Fields in enc.supplieartable table which should be populated from other table.
=========================================================
Fields                   How to Populate
=========================================================
edf_ref_no            It is auto generated number
udb_ref_no            It is auto generated number
customer_id            Can be populated from customer_id in CUSTOMER table
location_id             Can be populated from ELECTRICDETAILS Table
detail_id            Can be populate from ELECTRICDETAILS table
supplier_id            Can be populated from SUPPLIER table
Create_date            Current date
amount_b_fwd      NA
Create_user            Need to insert manually
Last_update_date
Last_update_user      Need to insert manually
standing_charges      Price
sc_period_from      Start date
sc_period_to            End date

Reza Rad

use DataFlow task,
add excel source for you excel data
add oledb destination,
and let me know where do you have problem exactly? your question is not obvious to me

lotusboy

ASKER

Question is how can i populate data of excel file in sql server table. For this I want to use SSIS.

What is DataFlow task ?
From where can I add excel source and olddb destination.
what will be the first step ?

ASKER CERTIFIED SOLUTION

Reza Rad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lotusboy

ASKER

Looking step by step soluction