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 «««123

SSIS Lookup using a Script Component Expand / Collapse
Author
Message
Posted Monday, January 5, 2009 11:47 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:49 PM
Points: 5, Visits: 41
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
Posted Thursday, January 8, 2009 10:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:04 AM
Points: 210, Visits: 115
Great article.
Very nicely explained and a great concept.

Rob.



Post #632634
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse