SSIS Lookup using a Script Component

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Sachin Dedhiya » SSIS Lookup using a Script Component

22 posts, Page 3 of 3

«««1 2 3

SSIS Lookup using a Script Component

Rate Topic

Display Mode

Topic Options

Author

Message

Zanoni Labuschagne-766625

Posted Monday, January 05, 2009 11:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 13, 2010 1:25 AM
Points: 5, Visits: 23

Hi Sachin

As requested, here is an example, but first, just a bit of waffling...

In the code, I create a dummy temp table, and put some random values in. My last column however is a calculated column, using checksum, which generates a key to do the joining on. It does not have to be a checksum - you can use whatever logic you like to build a key (Checksum has some flaws,in that there are cases where it may actually returns duplicates). Viz, in the example below, you could just as well have concatenated the strings, instead of using checksum.

The code doesn't have to be this simple either - you can write update statements to rather generate a key if the logic is quite complex. The main thing is, you must just be sure that the values you are using to create a key with, are unique. That way, you will always just have to join on one column, instead of many.

Here's the code (looks a bit better if you copy it into SSMS):

/* Check if the temp table already exists*/
if OBJECT_ID('tempdb..#temp') is not null
Drop table #Temp

/* Create a temp table */
Create table #Temp
(
RowID int identity (1,1)
,Comment varchar(200) NULL
,Column1 varchar(200) NULL
,Column2 varchar(200) NULL
,Column3 varchar(200) NULL
,Key_Column as Checksum(Column1, Column2 , Column3)
)
go

/* Insert Random Values */
Insert into #Temp
(
Comment
,Column1
)
Select
'Show example of value for column 1 only'
,'Some Value'

go

Insert into #Temp
(
Comment
,Column1
)
Select
'Show example of another value for column 1 only'
,'Some other value'

go

Insert into #Temp
(
Comment
,Column3
)
Select
'Show example of value for column 3 only'
,'Some value'

go

Insert into #Temp
(
Comment
,Column1
,Column2
,Column3
)
Select

'Show example of value for all columns only'
,'Some Value'
,'Another Value'
,'Yet Another Value'

go
/* Get the data */
Select *
from #Temp

_____________________________________________________________________
Select 'Remember, we were all newbies once'
From Truth
Order by Experience
_____________________________________________________________________

Post #630333

robertm

Posted Thursday, January 08, 2009 10:34 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 3:59 AM
Points: 210, Visits: 108

Great article.
Very nicely explained and a great concept.

Rob.

Post #632634

« Prev Topic | Next Topic »

22 posts, Page 3 of 3

«««1 2 3

Permissions