Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

package Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 9:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
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

Post #1351692
Posted Wednesday, August 29, 2012 10:24 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 27, 2014 7:41 AM
Points: 739, Visits: 518
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


"Be brave. Take risks. Nothing can substitute experience."
Post #1351760
Posted Wednesday, August 29, 2012 12:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 5,047, Visits: 11,797
Use a multicast to broadcast data from one source to multiple destinations.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1351814
Posted Wednesday, August 29, 2012 7:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:04 PM
Points: 35, Visits: 98
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.
Post #1351980
Posted Thursday, August 30, 2012 12:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 13,616, Visits: 10,504
You can split the name field in a derived column by using a clever combination of FINDSTRING and SUBSTRING.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1352023
Posted Thursday, August 30, 2012 6:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
hi,

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

each table has different column
Post #1352138
Posted Thursday, August 30, 2012 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 13,616, Visits: 10,504
Just map only the columns that you need in the OLE DB Destination.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1352140
Posted Thursday, August 30, 2012 7:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
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
Post #1352191
Posted Thursday, August 30, 2012 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 13,616, Visits: 10,504
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1352198
Posted Thursday, August 30, 2012 8:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
what do you mean by that,i need to do it in ssis,how can maintain reference integrity
Post #1352269
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse