• 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
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






0
lotusboy
Asked:
lotusboy
  • 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
0
 
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 ?





0
 
Reza RadConsultant, TrainerCommented:
OK, I got it, you need tutorial(import excel data into sql server using ssis):
http://blogs.techrepublic.com.com/datacenter/?p=205
http://www.accelebrate.com/sql_training/ssis_tutorial.htm


0
 
lotusboyAuthor Commented:
Looking step by step soluction
0
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