YT YouTube2Text

YouTube2Text → Transcripts → SQL Server Integration Services (SSIS) Part 9 - Lookup Transforms

SQL Server Integration Services (SSIS) Part 9 - Lookup Transforms — Transcript

by WiseOwlTutorials · 4,682 words · 664 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00welcome to this tutorial on using
0:02Lookout transforms in integration
0:05Services here's what we'll learn on the
0:07tutorial we'll Begin by having a look at
0:10our example package which is to take a
0:12set of xfactor finalists and look up the
0:15details of each of their mentors in a
0:17separate table we'll have a look at what
0:19the package should do and then have a
0:21look at the package itself in
0:23ssis what we'll then go on to do is look
0:25at creating lookup transforms
0:28themselves and then we'll look at how to
0:30deal with the output from them so
0:31firstly how to cat with the output where
0:33there's non-matching rows where we
0:34didn't find a corresponding mentor and
0:37then have how to copee with the fact
0:38when we have found a corresponding
0:41Mentor we'll then go on to finish the
0:43package using a union query and finally
0:46we'll go on to look at ways to speed up
0:47processing the package either by using
0:49full or partial caching with an
0:51explanation of what that means or by
0:53using cach
0:55transforms so let's get
0:58started welcome to this tutorial on
1:00using lookup transformations in SQL
1:03Server integration
1:04Services we've got quite a lot to cover
1:07and it's important I think that I
1:08explain clearly the example we're going
1:10to try to solve at the beginning so you
1:12can see where we're
1:13going I've got here the familiar Excel
1:16workbook of UK xact contestants and what
1:19we're going to do is import these into a
1:21SQL Server table now the two contestants
1:24I want you to particularly concentrate
1:26on are One Direction a little known boy
1:28band and new Vibe if you look at the men
1:32to uh assigned to each of those Simon
1:36cowl is assigned to One Direction and
1:38TSA contostavlos is assigned a new
1:41Vibe now when we come to import these
1:43into our SQL Server table what we're
1:45going to do is look up the mentor's name
1:48in a separate SQL Server table of
1:49mentors and here it is so we've got the
1:52mentor ID and the mentor name when we
1:55look up Simon cowl there will be no
1:58problem SQL Server will will detect the
2:00fact that the mentor ID is eight and
2:02store that as a mentor ID against the
2:04corresponding
2:06finalist but the problem will come with
2:08t Lisa conoa whatever her name is
2:11because it will then look up her Mentor
2:13name in the list and fail to find it and
2:16what we're going to do then is store the
2:18not known Mentor ID 9 against her
2:22name if we look at the finist table
2:24which will be imported One Direction
2:27will come in with the mentor ID 8
2:29corresponding to Simon cowl and we'll
2:31set some notes to say it's imported okay
2:34whereas new Vibe will have a mentor ID
2:36assigned to it of nine meaning not known
2:39and notes will say that TSA wasn't
2:42found this is a very standard thing to
2:44do when importing data and integration
2:46Services you have a set of um
2:50data let me just go back to my Excel
2:52workbook you have a set of data with a
2:54lookup field which is more or less
2:55populated but inevitably there'll be new
2:57values in that list and what a look C
3:00transformation allows you to do is to
3:02detect where there's a new value and
3:04store a sensible corresponding number
3:07next to
3:09it so let's have a look at the
3:12integration Services package which will
3:14achieve what we want to achieve on the
3:16control Flow side is extremely simple
3:18there's only two tasks the first one is
3:21an execute SQL task and if we have a
3:24look at the SQL assigned to it all it
3:26will do is truncate the table of TBL
3:28finalist so basically it will remove all
3:30the old records ready for the new ones
3:31to come in the only other task is a data
3:35flow task and if we have a look at that
3:37in more detail this is of course where
3:39the complexity
3:41lies we'll begin with taking Excel
3:43source of the finalists using an Excel
3:46Connection Manager and we'll feed that
3:48into a lookup
3:50transform and what this will do is spit
3:53two streams of data out if it can't find
3:56the corresponding Mentor name in the SQL
3:58Server table the ray will flow down the
4:00lookup no match output and go to this
4:04transform whereas if it can find a
4:06mentor it will go down the lookup match
4:08output and come into this
4:10transform what will happen then it
4:13depends on whether which path it went
4:15down if it was unable to find a match
4:17for the mentor name what it will do is
4:20create a new Mentor ID field and assign
4:22the value nine to it meaning this Mentor
4:25wasn't known and it will also set the
4:27notes to record the fact there was a
4:28problem
4:30on the other hand if it could find the
4:31mentor name what it will do is just use
4:34the mentor ID it's looked up from the
4:36other Mentor table and it will record
4:38the noes to say that it imported
4:41okay what we'll then do is use a union
4:43query to recombine those two sets of
4:45data into a single data set and Export
4:48that into the SQL Server table of
4:51finalists so we'll Begin by having a
4:53look at the lookup
4:54transform and to do that we need to go
4:56to the next part of this tutorial
5:01before we create the lookup
5:02transformation let's have a look at what
5:04elements of the package I put in place
5:06already on the control Flow side we've
5:09got an initial execute SQL task which
5:12will run against the connection I've
5:14already created to the xfactor database
5:17and truncate the table of fin lists so
5:19basically we'll remove all the old fin
5:20lists ready to import the new
5:22set and I've also got a data flow task
5:26which will import the new set of data
5:27looking at the mentor's details and of
5:30course it's that which we need to look
5:31into so for go into that I've already
5:34created an Excel source which will take
5:37the worksheets of finalists and what I'm
5:39now going to do is create a lookup
5:41transform to split them into those who
5:43have mentors and those who
5:45don't I've already cunningly got the
5:48name for this task in the clipboard so
5:50I'll paste that in and then very
5:54carefully I will resize
5:58this because
6:00resizing tasks with long names in SQL
6:02Server integration Services isn't an
6:04easy thing to
6:06do what I'm now going to do is feed the
6:08output from the first worksheet into
6:10this lookup transform in familiar
6:13fashion grit my teeth as it resizes it
6:17and get it back to how I want it to
6:19look and then what I'm going to do is
6:21configure this lookup transform so it
6:23spits out two types of data those
6:26finalists who have got mentors in the
6:27mentors table and those who don't
6:30to do this I need to double click in it
6:32as always I'm going to explain cach mode
6:35later towards the end of this tutorial
6:37so for the moment I'll just leave them
6:38as a default settings and I'll go
6:41straight onto the
6:43connection the table or view is not the
6:46one of fin lless which is where the data
6:48is coming from but the table in which
6:50I'm looking up the mentor details so I
6:52choose the mentor table to be the one
6:54containing the lookup
6:56information if you use Excel regularly
6:58you can think of this is your lookup
7:01table now I'm going to look at the
7:03columns and what I'm going to do is say
7:05that the mentor name in the finalist
7:07table should match up to the mentor name
7:09in the lookup table and I can do that by
7:12dragging one column onto the
7:15other what I now need to do is say which
7:18columns I'm going to export from this
7:20lookup transform and the only thing I
7:22want to actually keep is the mentor ID
7:25so I'm going to add that as a new column
7:27to the output so now I've got the
7:29situation from the lookup transform it
7:31will send out all the existing columns
7:33from the finist table as output plus one
7:36new one called Mentor ID which I want to
7:38add as a new column which is abolutely
7:40the only choice and I'm going to give
7:42the that new column the name of Mentor
7:44ID the same as it was called in the
7:47lookup table so I can then choose
7:50okay now unfortunately there's one thing
7:52I've forgotten to do which is to specify
7:55what will happen to the information
7:56coming out of this lookup transform so
7:59I'm going to go back into it now and
8:02where it says specify how to handle rows
8:04with no no no matching entries I'm going
8:07to choose to redirect these rows to a no
8:10match
8:11output now to see what this means I'll
8:14just choose okay to confirm that I'm
8:16going to add in two derived column
8:18transforms and I'll configure these
8:20later in the next part of this tutorial
8:22so I'll put one there and I'll put one
8:25there when I drag the output from the
8:29lookup transform into one of the these
8:31derived column
8:33Transformations it will ask me which
8:35output I mean do I mean the output where
8:38I successfully found a matching Mentor
8:40or Jo I mean is in this case the ones
8:42where I didn't successfully find a
8:44matching
8:45Mentor so if I choose okay to that I can
8:48then do the same thing for the other
8:50stream of output and it will
8:52automatically assume that I mean the the
8:55uh finalist where I've successfully
8:57found a matching mentor so that's what a
9:00lookup transform does it splits the data
9:02into two parts those where it can find a
9:04match in the corresponding lookup table
9:06sorry that one and those where it
9:08couldn't find a match and in the next
9:10part of this tutorial we'll have a look
9:12at the two derived column
9:13transformations to see how we can
9:15manipul the data which comes out of the
9:17lookup
9:20transform in the previous part of this
9:22tutorial we created a lookup transform
9:24to split the table of fin lists from
9:27Excel into two parts those where no
9:29corresponding Mentor could be found and
9:32those where corresponding Mentor could
9:33be
9:34found what I'm now going to do in this
9:36part of the tutorial is configure the
9:38derived column transform to create two
9:41new columns for each finalist where
9:44there wasn't a corresponding mentor and
9:46I think it's worth at this point going
9:47back to SQL Server to see what those new
9:49columns will two new columns will
9:51contain the mentor ID will contain the
9:54number nine because that's the value of
9:57the not known mentor and the will say
10:00some notes on why the mentor's name
10:02couldn't be
10:05found we'll go back to integration
10:07services and I dislike having to
10:09hardcode any value into a package I
10:11create so what I'm going to do is create
10:13a pack uh a variable to hold the number
10:17of the not known
10:18mentor and having little or no
10:21imagination I'll call it not known
10:24Mentor
10:25ID and I'll give it a value of nine and
10:28the advantage of this approach is that
10:30in future if for some reason the value
10:32of that record number changes what I'll
10:35be able to do is just go in and change
10:36the value of my variable and everything
10:38will still work
10:39okay so what I can now do is probably
10:42rename my derive column transform using
10:45text already in my clipboard and then
10:48resize it I think I'm beginning to learn
10:50the message that long names are
10:53bad and what I can then do is configure
10:55it to say how it's going to
10:57work now I'm going to create two new
10:59columns as explained the first one I
11:02will call Mentor
11:04ID and I want it to be a new column in
11:06the table I don't want it to replace any
11:09existing one I wanted to add to the uh
11:11output from the final spreadsheet
11:12already there and the expression for
11:15this should just be the value of the
11:16variable called not know Mentor ID and
11:19so I can simply drag that down from the
11:21variables into that space so far so
11:25good the second column I want to create
11:27is the notes table and again I want
11:30sorry noes column again I want this to
11:32be a new column and for this one what I
11:35can do is take the column called Mentor
11:37which will contain the name of the
11:40mentor drag that into my expression and
11:43then concatenate onto the end of that
11:45the words not
11:47known and in theory that should all work
11:51perfectly however it will actually yield
11:53to a problem if you look at the length
11:55assigned to that by integration Services
11:57it's 265 characters
11:59uh ssis gets that by taking the standard
12:01length of an Excel text column which is
12:03255 and adding on 10 characters for the
12:06words not known all that would be fine
12:09were not for the fact that in SQL Server
12:11my let expand that my notes column is
12:15255 characters and you can't fit 265
12:19characters into
12:20255 what I need to do then is shorten
12:23this and I can do that by converting the
12:26type of it using a type cast function
12:30and I'm actually going to use dtore wstr
12:34which will convert it to a Unicode
12:35character string but with less
12:37characters in so I'm going to um change
12:41this to two5 five
12:44characters and what that should do is
12:46truncate it but it still says
12:49265 and the reason for that is I need to
12:53include this expression in Brackets so
12:56that it's truncating the whole
12:58expression to 250 5 characters and not
13:00just the mentor's name and now it will
13:03reduce it back down to 255
13:05characters if I then choose okay to
13:07confirm that I'm hoping I've
13:09successfully configured it so that this
13:11derived column transform will take in
13:13all the columns from the existing
13:15finless spreadsheet and add two more of
13:17its own so that when I come to export
13:19the DAT data to SQL Server I can set the
13:22standard six columns and there'll be two
13:24more available to me as
13:26well what I now need to do is do the
13:28same same thing for those finalists
13:31where we did successfully find a men in
13:33the mentor's lookup and to do that I
13:35need another derived column
13:38transform it's time now to turn our
13:40attention to the second derived column
13:42transform the one which will take those
13:44finalist where we successfully looked at
13:46the mentor ID and add a new column
13:48saying that this was imported okay the
13:51first thing I'll do is just rename the
13:53second derive
13:54column using a name I've got in my
13:57clipboard and with a bit of luck I'll
13:58success resize it and what I'll now be
14:01able to do is reconfigure this to have a
14:03look at the columns it's
14:05exporting it's already got the mentor ID
14:08column because we've already
14:09successfully looked up the final list in
14:11the mentor's table so all we need to do
14:13is add a second column giving some notes
14:16and I'm using the same name as I did in
14:18the previous uh derive column transform
14:20just to make life
14:22easier I'm going to put the expression
14:23for that as a bit of text saying that I
14:26was able to import it okay
14:30and if I then choose okay with a bit of
14:33luck both the two derived column
14:35transforms are now exporting the
14:38original columns in the finalist table
14:40plus two more the mentor ID and the
14:43notes in the case of this derived column
14:46transform the mentor ID is being set to
14:48nine in the case of this derived column
14:50transform the mentor ID is the one which
14:52we've looked up in the mentor's
14:54table the last thing we need to do to
14:57get our package working is to recombine
14:59the two tables together we're using a
15:01unor query or unor transform as you call
15:04it and so to do that I'll choose a unor
15:06transform add it into my package and
15:09then direct the Alpha from my two
15:11derived column transforms into it and
15:15I'll also rename it so it says it will
15:17combine the tables back together which
15:21seems a reasonable name what I'm now
15:23going to do is configure this to check
15:25the columns are being mapped
15:27correctly and you can see for my two new
15:29columns Mentor ID and notes they're
15:33being exported into an alpha column with
15:35exactly the same
15:37name had I not given such consistent
15:40names to my columns I would have had to
15:42create a new column and map columns from
15:45the original two tables onto it but
15:47because I chose consistent names I don't
15:49have to do that so I can choose okay to
15:51confirm that and what I now need to do
15:54is to direct the output from this Union
15:56query into my SQL Server table
15:59and to do that I can put in a
16:01destination
16:02assistant I can choose my SQL Server
16:05Connection I've already got
16:07created I can direct the output from the
16:09union or query into it and I can
16:12configure my destination to say that the
16:14information is going into the finalist
16:16table and then finally I need to go to
16:18my mappings and what I need to do is
16:21check that the right columns from The
16:22Source are going to the right columns in
16:24the
16:25destination and as it happens the ID is
16:27going to a column called Excel finalist
16:29ID the name is going to a column called
16:31finalist name and the finished column is
16:35going to be the position in which the
16:36final is finished and we're going to
16:38ignore the other
16:40columns if I then choose okay to confirm
16:43that I think I've now got a working
16:45package so let's give it a will if I run
16:48my package by clicking on the play Tool
16:51hopefully it will import the data lots
16:53of green ticks which is a good sign and
16:55you can see the five rows have gone
16:57sorry five rows have gone G through the
16:59no match output pipe and 103 rows have
17:02gone through the match output pipe those
17:04are then recombined back here to send
17:06108 rows into the final
17:08table so if I have a look at that final
17:10table in SQL server with a bit of luck
17:13it will contain 108 rows there it is and
17:16as I scroll down you can see the first
17:18103 of the ones which were imported okay
17:21and right at the bottom there's five
17:23rows where we couldn't import it because
17:25we couldn't find the corresponding
17:26Mentor in the mentor's table so
17:28everything seems to work
17:31perfectly as the final part of this uh
17:34tutorial what we're going to have a look
17:36at is cashing ways to speed up the way
17:39in which your lookup transform
17:44Works we've got our package working
17:46perfectly but the question remains how
17:48quickly will it run as it happens I
17:51think the answer to that is very quickly
17:52indeed we've only got 108 uh finalists
17:57and we're only looking up nine men ERS
17:59so I don't think speed is going to be an
18:01issue but what would happen if there
18:03were 108,000 men finalists or Parish
18:05thought 108 million in that case we
18:08might be more concerned about how our
18:10caching is going to work of data you can
18:13see the options by double clicking on
18:15the lookup transform task and seeing the
18:17cash mode and there are three
18:19possibilities full cash partial cash or
18:21no cash at all in full cach mode what
18:25integration services will do is load up
18:27the entire table lookup table of mentors
18:30at the very beginning of the task and
18:33because there's only nine of them it
18:34won't occupy a great deal of
18:37memory at the other end of the extreme
18:39is no cache what will happen then is
18:42every single time uh integration
18:43Services comes to a mentor it will look
18:46for it in the table at a fresh and have
18:48no memory of what the table used to look
18:50up the last time it loaded it this is
18:52clearly wildly
18:54inefficient a sensible Middle Way
18:56perhaps is partial caching
18:59what partial caching will do is set
19:01aside an amount of memory you can
19:03actually control how much of it there
19:05that is by going to the advanc tab and
19:07changing the amount according to whether
19:09you're using a 32-bit or 64-bit
19:12processor we're going to use a default
19:14of 25
19:15megabyte in a partial cache what will
19:18happen is this integration services will
19:21find Louie Walsh it will look up Louis
19:23Walsh and the list of mentors and find
19:24that Louis Walsh is number five it will
19:27then create in his cache a miniature
19:30table containing Mentor ID 5 Mentor name
19:33Louis
19:34Walsh when it comes to the next finalist
19:38who coincidentally is also mentored by
19:40Louie Walsh it won't have to go back to
19:42the lookup table it will go to the cach
19:44and look it up far quicker from that it
19:47will then add Simon cowl to its cach
19:49Sharon Osborne and so
19:51on this technique clearly works best
19:54when you have a very large number of
19:56Records but only a few things things
19:58that you're actually looking up so the
20:00cach size remains relatively small and
20:03partial caching seems the most sensible
20:05option to
20:06me now in addition to that you can also
20:09create something called cache connection
20:11managers and that's what we're going to
20:12have a look at in the final part of this
20:17tutorial the final part of this tutorial
20:19will consider a way to make Lookout
20:21Transformers work even quicker than
20:23using partial
20:24caching it relies on the fact that
20:27getting information directly from s
20:28server is actually not the fastest thing
20:30we can do the quickest thing we could do
20:33would be to load our table of mentors
20:37and there it is into a special cache
20:40file with an extension of
20:42caw and to do that what we're going to
20:45do use is a cache
20:46transform what we can then do is in
20:48integration Services rather than getting
20:50our information for the lookup directly
20:52from olb from SQL Server we can get it
20:55from the cache file we've created and
20:57this is to Advan vantages one is it will
21:00run much more quickly because it's
21:02quicker getting information from the
21:03cache than it is from the
21:04olb and the second Advantage is that it
21:07may well be the case that somewhere else
21:08in our package we want to do another
21:10look up from the same table and we'll
21:12already have the cache file created so
21:14we won't have to reload it into
21:16memory so to get this to work the first
21:19thing I need to do is go back to my
21:20control flow and add another data flow
21:23task and what this is going to do is to
21:25load the mentors into uh
21:30cache what I'll then do is just run that
21:32straight into the next control flow
21:34task if I then double click on that to
21:37configure it I need to add two things to
21:39it in the data flow the first is a
21:42source which tells it where the
21:43information is coming from so it's going
21:46to get the information from the mentor
21:48table so I can use a Connection Manager
21:49already set up and if I then double
21:52click on that I can choose the table
21:55itself and then choose okay and for the
21:59sake of Elegance I'll rename that and
22:03call it mentor's
22:05table so that's where my data is coming
22:08from the place it's going to as
22:11mentioned is a cache file and to do that
22:14I can use a cache transform you won't
22:16find that in the common transform
22:18section it's in the other
22:20transforms so what I'll do is create my
22:22cache
22:23transform feed my data from the mentor
22:26table directly into it and perhaps Reen
22:28name it to say create cache
22:31file what I'll then need to do is to
22:34configure this cache file if I double
22:36click on it the first thing I need to do
22:38is create a Connection Manager I could
22:41have done this separately but I'll do it
22:42as part of configuring the cache
22:44transform so what I need to do is click
22:46on the new button I'll call my new
22:49Connection Manager
22:51mentors
22:53table and if I then go to columns you
22:57can see that it's automatically going to
22:59take the mentor ID and the mentor
23:01name now at this point when you're
23:03configuring a cache Connection Manager
23:05there's a very easy mistake to make what
23:08you should do is index the mentor name
23:11because that is the field which you're
23:13looking up against so for example when
23:16you have a f list it's a mentor's name
23:18Louis Walsh that you want to find and
23:20nothing else if you make the mistake of
23:23me in indexing the mentor ID as well it
23:25would lead to problems later on in this
23:29if I then choose okay to confirm that
23:31and then choose okay sorry if I then go
23:34to mappings you can see that it's taking
23:37the mentor ID and the mentor name from
23:38the original uh olb source and mapping
23:42onto the mentor ID and Mentor name in
23:44the cache which is exactly what I want
23:45to happen so I can choose
23:49okay so that's my data flow task
23:52complete so the first thing which will
23:54happen when I run this package is it
23:55will load the mentors from SQL Server
23:57into a cach file which will be available
23:59for my lookup
24:01transform what I now need to do is go to
24:03that lookup apologies what I now need to
24:05do is go to my lookup
24:07transform configure it and say that
24:10instead of using an olb Connection
24:12Manager so instead of getting it
24:14information directly from a table in SQL
24:16Server I'm going to use the cache
24:18Connection Manager
24:20created it says my connection type has
24:22changed so I need to go to the
24:24connection Tab and I need to choose a
24:26connection manager but integration
24:28Services have thoughtfully already done
24:30that for me on my
24:31behalf everything else will work exactly
24:34the same way I did earlier but I need to
24:36check for my columns that it is matching
24:38the mentor name in the original finalist
24:41worksheet to the mentor name in my cache
24:43which it is doing and that it's spitting
24:45out the mentor ID still so if I choose
24:48okay my belief is that the only thing
24:51I've just changed is where the mentor's
24:53information is coming from so it should
24:55work exactly the same way as it did
24:56before but quicker let's see if it does
24:59so if I click on the play tool to run
25:01that it's imported all the data if I go
25:04to my fin lists and have a look at those
25:06you can see it's imported 108 finalist
25:08just as it did before the only
25:11difference is if I just come out of
25:13debugging at the time the package was
25:15running it had created a cache file
25:17containing the mentor's details which I
25:19could reuse in another
25:22transform and that's pretty much the
25:24whole story on lookup transforms I hope
25:26you've enjoyed what has been a slightly
25:27longer tutorial than average please come
25:29back for more in the series you can find
25:32much more information on net SQL server
25:35and Microsoft Office training at www.
25:38w.c.

About this transcript

This page contains the full transcript of SQL Server Integration Services (SSIS) Part 9 - Lookup Transforms by WiseOwlTutorials, generated from the public captions YouTube serves with the video. The transcript has 4,682 words across 664 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.