• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 710
  • Last Modified:

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
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_user      Need to insert manually
standing_charges      Price
sc_period_from      Start date
sc_period_to            End date

  • 2
  • 2
1 Solution
Reza RadConsultant, TrainerCommented:
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
lotusboyAuthor Commented:
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 ?

Reza RadConsultant, TrainerCommented:
OK, I got it, you need tutorial(import excel data into sql server using ssis):

lotusboyAuthor Commented:
Looking step by step soluction
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now