package

Question

package

harri.reddy

SSCommitted

Points: 1957
More actions
August 29, 2012 at 9:11 am

#261190

hi,
i have 1 excel file which has like id, name,address0, address1, address3
now i need to make package that take data from this flat file and in my database i have 3 table.
i need to place name,id in 1 table and address table should have all the address.
what componenet do i need, how can place some column to 1 table and another 2 another table.
Morever , i have name fileld like girg jow ,m
first ,last and middle name in1 column,how can i break that in ssis package.
plz help me

Viewing 15 posts - 1 through 14 (of 14 total)

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

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 1

My preferred method is to import the MS Excel file into a SQL table for verification. I usually set all the columns to a wide NVARCHAR value

You can create a stored procedure to insert the data from the verification table into into your live tables as required.

You can also write logic in the SSIS package to do the same.

Here a link to get you started in import data from MS Excel to SQL Server using SSIS. Come back to us if you need more assistance.

Good luck!

http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

Phil Parkin SSC Guru Points: 246959 More actions · Answer 2

Use a multicast to broadcast data from one source to multiple destinations.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

n79799 SSC Eights! Points: 804 More actions · Answer 3

Use multicast on the excel......load id and name cols(from source 1 of multicast) to table 1 and all the addresses in col1, col2, col3(from source 2 of multicast) to table 2.

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

You can split the name field in a derived column by using a clever combination of FINDSTRING and SUBSTRING.

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

harri.reddy SSCommitted Points: 1957 More actions · Answer 5

hi,

but my table has different structure,how can i use muticast.

each table has different column

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Just map only the columns that you need in the OLE DB Destination.

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

harri.reddy SSCommitted Points: 1957 More actions · Answer 7

thanks,

now what if i have foreign key with the table.

exalple

product

product id identity(1,1) primary key,

name varchar(50))

item

itemid identity(1,1) primarykey,

itemname varchar(5))

productitem

(productid datatype reference product(productid),

itemid datatype referenc item(itemid))

how can i insert value in productitem from multicast.

if both are identity column, they will insert automatically

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

In that case it's better to load the initial load from Excel into a staging table and do everything with TSQL from then on.

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

harri.reddy SSCommitted Points: 1957 More actions · Answer 9

what do you mean by that,i need to do it in ssis,how can maintain reference integrity

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

Load the Excel file as-is to a table. --> dataflow

Populate the destination tables using TSQL statements --> Execute SQL Tasks

For the record, it would be appreciated if you stated all your requirements in your original question, instead of halfway through.

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

harri.reddy SSCommitted Points: 1957 More actions · Answer 11

harri.reddy

SSCommitted

Points: 1957

August 30, 2012 at 2:55 pm

#1532139

but what should i do for referential integrity

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

Insert data in one table first, get the inserted primary keys, use these to insert in the second table and so forth.

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

harri.reddy SSCommitted Points: 1957 More actions · Answer 13

can u give me example with what component do i need to use

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 14

Sure! The Execute SQL Task.

Although the Execute TSQL Task may be an option as well, but it's interface is slightly worse than the other.

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