Transformation Link - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transformation Link

Author

Topic

cutever
Starting Member

32 Posts

Posted - 2002-05-29 : 05:25:36

I have a excel file called MemberExcel

ID MemberCode JoinDate RegionID RegionName

1 11111 12/01/2002 T K.TERENGGANU
2 22222 12/01/2002 K KUALA LUMPUR
3 33333 12/01/2002 A ALOR SETAR
4 44444 12/01/2002 S SELANGOR
5 55555 12/01/2002 M MELAKA
6 66666 12/01/2002 P PENANG
7 77777 12/01/2002 J JOHOR

I would like to transform the data from excel into SQL SERVER table. I have two (2) table tbl_Member and tbl_Region.

If I transform directly, tbl_Member.RegionID will keep the data as (T,K,A,S,M…..). I want all the Region Info keep in separate table and tbl_Member only keep the Region ID like (1,2,3,4….)

Do have any simple way to transform the above info into the result as following.

tbl_Member
----------------------
MemberID MemberCode JoinDate RegionID
-------------------------------------------------------------------------------
1 11111 12/01/2002 1
2 22222 12/01/2002 2
3 33333 12/01/2002 3
4 44444 12/01/2002 4
5 55555 12/01/2002 5
6 66666 12/01/2002 6
7 77777 12/01/2002 7

tbl_Region
------------------------
MemberID RegionName
----------------------------------------

1 K.TERENGGANU
2 KUALA LUMPUR
3 ALOR SETAR
4 SELANGOR
5 MELAKA
6 PENANG
7 JOHOR

Hope someone can help me. Thank You !

Ver

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-05-29 : 07:05:22

Basic Steps
1)Define An Excel connection for your spreadsheet
2)Define 2 SQL connections to your database
3)Create a transform task between Excel and one of your SQL connections
4)Choose tbl_Member as destination Table
5)On transformation tab delete all mappings and create a new Copy Column mapping for ID->MemberID,MemberCode->MemberCode and JoinDate->JoinDate
6)Goto the Lookup tab and create a lookup called GetRegion with a query of
SELECT RegionID
FROM tbl_Region
WHERE (RegionName = ?)
7)Go back to the transformation tab choose RegionName from the Source and Region ID from the Destination and click on New and choose ActiveX Script
8)Click on Properties on the first tab of this new task and it will show you the Script Editor
9)Change the script to read

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("RegionID") =DTSLookups("GetRegion").Execute( DTSSource("RegionName"))
Main = DTSTransformStat_OK
End Function

That's basically it. What you've done is define a lookup that accepts the RegionName as a parameter and gets the RegionId and sets this in the tbl_Member table

(And don't call your tables tbl_* this isn't Access

)

HTH
Jasper Smith

cutever
Starting Member

32 Posts

Posted - 2002-05-29 : 23:23:35

quote:

2)Define 2 SQL connections to your database

6)Goto the Lookup tab and create a lookup called GetRegion with a query of
SELECT RegionID
FROM tbl_Region
WHERE (RegionName = ?)

Why I should define 2 SQL Connection ?

I have many different Regions, so, this is very difficult for me to select one by one, by using the following method.

SELECT RegionID FROM tbl_Region WHERE (RegionName = ?)

Lookup Query also not accept it.

Please.....help !!!

Ver

cutever
Starting Member

32 Posts

Posted - 2002-05-29 : 23:49:11

I got it !!!

Thank You Very Much !!!!

Ver

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources