YT YouTube2Text

YouTube2Text → Transcripts → SQL Server Integration Services (SSIS) Part 7 - Conditional split and derived column transforms

SQL Server Integration Services (SSIS) Part 7 - Conditional split and derived column transforms — Transcript

by WiseOwlTutorials · 2,803 words · 406 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:01welcome to this wise hour tutorial on
0:03conditional split transforms and derived
0:05column transforms in integration
0:07Services here's what we'll aim to cover
0:09on the
0:12tutorial the first thing we'll look at
0:14is what we're trying to achieve which is
0:16actually to marry off Cheryl Cole to one
0:18of our trainers but more on that in a
0:20second we'll then look at how to create
0:23a conditional split transform to split a
0:25table of data into two or more
0:28parts we then look at how to create a
0:30derived column transform to create a new
0:33column or amend an existing one in a
0:35table of data and finally we'll complete
0:38the project so to get the finnal output
0:41and send it to a SQL Server table so
0:44let's get
0:46started here at yal some of us are Avid
0:49followers of The X Factor I'm afraid
0:51it's a UK version and so we've got an
0:54Excel workbook of X Factor finalists
0:56over the last few years what we're going
0:59to do is import the data paying
1:01particular attention to the mentor
1:02column cherd Cole has just got married
1:04to one of our trainers Dave Wakefield
1:06and so we need to change all occurrences
1:08of Cheryl Cole to Cheryl
1:11wigfield also we can't spell this
1:13person's name we can't even actually
1:15pronounce it so what we're going to do
1:17is redirect all people whose name
1:19contains the words
1:21contostavlos down to a path which will
1:23then be
1:26rejected the package in ssis would look
1:29like this we'll begin with an Excel
1:32workbook of the xfactor finalists and
1:34we'll import that using a data flow
1:37task if the mentor is Cheryl will
1:40redirect the output down this path we'll
1:43then use a derived column task to re or
1:46to change Cheryl's name to Cheryl
1:48Wakefield and then that data will
1:50reunite with the normal data in a union
1:53or query to recombine all the finalists
1:55together the final result will be
1:58exported to a SQL Server table of fin
1:59finalists except that Tisa contostavlos
2:03that sounded more likely didn't it will
2:05won't appear in the final results her
2:07Rose would have been
2:09rejected here's what the final results
2:11will look like then there will be a
2:13table containing the mentor name Sher
2:16wakefields will all appear at the top or
2:18the bottom depending on how we do
2:20it uh but there won't in this list be
2:23any toisa Kosta losses those records
2:26would have been
2:28lost so let's have a look at
2:32the package in ssis we've got a control
2:35flow containing an execute SQL task what
2:39that will do is just delete all the
2:41records from the finless table to begin
2:43with so that we can reimport them we've
2:46then got a data flow task which will
2:48import all the xfactor
2:50finalists and all I've done so far
2:52within that is created a source which
2:55will take data from an Excel worksheet
2:57of excel Factor finalists and if we
2:59double click on that and preview it you
3:02can see there's the columns contained in
3:04the Excel workbook and it's the mentor
3:06column we're going to be conly
3:08particularly concerned
3:10with so let's get started with the
3:13conditional split
3:16task so what we're going to do is split
3:19the data coming out from this Excel
3:20workbook into three parts people Mentor
3:24by Cheryl Cole people Mentor by TSA
3:26whatever her name is and everybody else
3:29and the way to do that to split data is
3:32to create a conditional split task so
3:35I'm going to add one of those and I'll
3:38rename it to say split mentors because
3:43that's what it's going to do and then
3:45what I'm going to do is run the data
3:46from the Excel workbook straight into
3:48that conditional split
3:49task what I can now do is configure this
3:52and I can have as many conditions as I
3:55want the default
3:57output will be called normal people but
4:01in addition to that I'll have one or
4:03more conditions above here in one for
4:05each line which says specifies another
4:08data pipe so let's start with the cherl
4:11coal
4:14finalists and I'm going to call that
4:15stream of data sh call finalists for
4:18obvious reasons what I want to do is
4:20find out whether the mentor name
4:23contains the word Cheryl now I can get
4:26to the mentor Name by clicking on that
4:28column and dragging it into the
4:30condition to find out whether one bit of
4:33text contains another I can use a string
4:36function and users of other Microsoft
4:39software will find a lot of these
4:40familiar the one I want on this occasion
4:43is find string if I click on that you
4:45can see that it says it Returns the
4:47location of the specified occurrence of
4:48one string within another so I'm going
4:51to drag that down into the condition and
4:53it has three arguments the string within
4:56the bit of text within which I'm
4:57searching the string I'm looking for
4:59four and the occurrence I want to return
5:03so whether it's the first or the second
5:04or subsequent
5:05equasion the character expression is
5:08just going to be the column called
5:09Mentor so I can click on that and drag
5:11it onto the character expression
5:13argument and it will automatically
5:14replace it you can see that my whole
5:17expression is still red because it still
5:19doesn't make sense to integration
5:21Services the next thing I need to
5:23specify is the string I'm looking for
5:25and I'm going to look for the word
5:27Cheryl but I can't actually remember how
5:29I typed it in so I'm going to use a
5:31fairly standard trick throughout
5:32Microsoft software which is instead of
5:35just looking for it in the Co in the
5:38case I typed it in I'm going to convert
5:41it using either the upper or the lower
5:44function and all these functions do is
5:47to convert text to Upper or lower case
5:49and because I've typed in cherl in
5:51uppercase I'm going to have to type in
5:53the word upper in
5:55there and close the brackets there so
5:58what this is doing is taking the
6:00mentor's name converting it to uppercase
6:03and then searching within that for the
6:04word Cheryl in uppercase so now it
6:07doesn't matter in which case I typed in
6:08Cheryl it will still
6:10work the occurrence I'm looking for is
6:13the first one and I'm hoping that when I
6:15click off that row it will turn black it
6:19hasn't it still doesn't understand it
6:21and if I let my mouse linger over it it
6:23will tell me why it tells me the answer
6:26is a Boolean I don't know if you read
6:27that briefly and that's because I
6:29haven't actually specified a condition
6:32the F string function will return the
6:35character number where it found the word
6:37Cheryl in the mentor's name and what I
6:40want to do is say that that should be
6:41greater than zero so what I'm saying is
6:44try to look for the word Cheryl within
6:45the mentor's name and if it finds it at
6:47a character position all well and
6:51good so that completes my Cheryl Co
6:53finalist condition I'm going to add one
6:56more output which is going to be to Lisa
7:00finalists and this is going to do pretty
7:02much the same thing but instead of
7:04looking for the word toisa anywhere
7:06within the mentor name I'm going to just
7:08by way of variety see if the first six
7:11characters of the mentor's name equal to
7:13Lisa so I can do that rather than using
7:16find string I can use the left function
7:20which takes a string of text and picks
7:22out a given number of letters from it
7:24beginning at the left hand
7:27side I'm looking within the mentor name
7:30but again I'm nervous whether I typed in
7:32me toisa in lower or upper case so I'm
7:36going to do exactly what I did last time
7:38and convert it to
7:42lowercase and I'm going to pick out six
7:44characters and I want to say whether
7:46that equals toisa except I need to make
7:50sure I type toisa in in lowercase and
7:52when I click off that it still gives me
7:53an error and the reason for that is
7:56because I haven't actually specified the
7:59right equal sign this is a condition and
8:02in ssis just like in C you have to put
8:06two equal sign to specify
8:09equality so let's just sum up where I've
8:11got to then the stream of all the
8:14contestants are going to come from an
8:15Exel workbook the ones where the
8:18mentor's name contains the word Cheryl
8:20in any case are going to go down one
8:22pipe the one where the mentor's name
8:24begins with the word TSA are going to go
8:26down a second pipe and all of the others
8:29are going to go to the default output
8:30called normal people so I'm going to
8:33choose okay to confirm that and just do
8:35a quick test to see how well it's
8:38working the quick test is going to
8:40involve the TSA
8:42contestants I'm going to put a union all
8:44query there to mop up all the data and
8:46I'm going
8:47to configure the output from the split
8:50mentor's conditional split task to run
8:53straight into that and I'm just going to
8:56take the TSA finalists for the moment
8:58they're the only ones I'm interested in
9:00and choose okay and then I'm going to
9:02right click on that pipe and enable the
9:04data viewer just to see what's flowing
9:06down
9:07it okay if I now run this package what
9:10I'm hoping to see is the data viewer
9:13containing the list of all the
9:15contestant whose names began with
9:18TSA so that looks like it's
9:21working what I can now do is get rid of
9:23my union all query because I'm actually
9:25going to lose my Telsa finalist now and
9:28what we're now going to do is show how
9:29to configure uh integration services so
9:33the shell Cole contestants automatically
9:35get renamed so instead of being mentored
9:38by Cheryl Cole they're being mentored by
9:40Cheryl Wakefield
9:43instead so what we now have is a
9:46conditional split transform which is
9:48splitting the data into three paths and
9:51what I want to do is for the contestants
9:54who are mentored by Cheryl Cole I want
9:56to rename every occurrence of Cheryl
9:58Cole toer
10:00Wakefield to do that I'm going to use a
10:02derived column transform which will
10:05either create a new column or replace an
10:07existing one and what I'm going to do is
10:10feed the output from the conditional
10:12split transform into that choosing to
10:14take the shell Co finalists
10:18only what I can then do I'll just rename
10:22the derive column transform to say that
10:24Cheryl has married a
10:27wise owl which I I think definitely
10:30deserves an exclamation mark what I'm
10:33going to do now is reconfigure this
10:35derived column
10:36transform and create a new column within
10:39it which I'll call new
10:42Mentor now I can do one of two things
10:44with this either either create a new
10:46column alongside the existing ones or I
10:48can replace an existing column I'm
10:51actually going to create a new column
10:52alongside the existing one and I need to
10:54say what the expression for that column
10:56will be I want to replace every
10:58occurrence of with Wakefield and that
11:01sounds like a string function to me
11:03specifically it's going to be the
11:05replace function so I can drag that in
11:08and what I'm going to do is replace all
11:10the characters in The MENTOR
11:12name so I can drag the mentor name and
11:14use that as my character
11:16expression and the other two arguments
11:19are going to be the thing I'm looking
11:20for which is the word Cole and the thing
11:23I'm replacing it which is Dave's
11:28surname it's G black so must have
11:30accepted the syntax at any
11:32rate bizarrely it's created a length of
11:35570 characters if I choose okay to
11:38confirm that just for the moment and go
11:40back to the path feeding into it and
11:42edit it by right clicking you can see
11:44for the metad DAT for that path the
11:46mentor name actually only had 255
11:51characters what I think is going on here
11:53is that integration Services doesn't
11:55actually know how many times the word
11:57Cole is going to appear in each name and
11:59so it's ering on the safe side and
12:01creating a very long arbitrary long
12:03length of 570 characters unfortunately
12:07this will cause problems further down
12:08the line because the SQL Server table
12:11only has 255 characters set aside for
12:13that column so what I'm going to do is
12:16cast the expression to be shorter than
12:18it currently is I want to keep the same
12:20data type dtor wstr which is a Unicode
12:24text character
12:26string but I want to change the length
12:28of it to
12:30255 and that will truncate it at 255
12:35characters so I can choose okay to
12:37confirm that and that completes my
12:39derived column
12:41transform what I now want to do is to
12:43reunite the original normal people and
12:46the normal people with Cheryl Cole
12:48renamed so what I can do is add in a
12:51union all transform to recombine the
12:54data and I'll call that reunite
13:00normal
13:02people and Cheryl
13:06mentored I seem to have lost the ability
13:08to type one more go and what I'm going
13:11to do is run firstly the normal people
13:14down into
13:16that and because I've still got the TSA
13:19finalist which I want to discard I have
13:21to choose the output which is normal
13:24people what I can then do is take the
13:27finalist mentored by sh Cole and feed
13:30those into the unol query or the union
13:32all
13:33transform if I then double click on the
13:35union all transform to reconfigure it
13:38I've got one change I need to make for
13:40five of the six columns everything will
13:41stay as it is but for the mentor column
13:45yes I want to take the mentor from the
13:46normal people as it stood but from the
13:49other derived column transform I want to
13:52take my newly derived new Mentor column
13:55which is exactly like the mentor one but
13:57containing the word cod Wakefield
14:00instead of coal so I can then choose
14:02okay to confirm that and all I have to
14:05do now is to send my data to a SQL
14:08Server data source and test out my
14:12package so just to complete the picture
14:15I need to send the output from my union
14:17all query and feed it into my SQL Server
14:19table and this should be a revision of
14:22things we've done in previous
14:23tutorials I'm going to I've already got
14:26a SQL Server Connection Manager set up
14:29so I'm going to add
14:30a destination I'm going to say that I'm
14:34using my SQL Server Connection
14:36Manager and I'm going to feed the output
14:39from the union all transform directly
14:41into that and I'll call it Final Table
14:46in SQL
14:48Server what I can then do is reconfigure
14:51this Final Destination the first thing I
14:54need to do is say which table is feeding
14:55into it's the one called TBL finalist
14:58and then what I need to do is set the
15:01mappings it's only been able
15:03successfully to map one column the
15:04series column and that's cuz I've done
15:06some renaming as it happens the name
15:10will go onto the finalist name the
15:13category I'm going to ignore the mentor
15:15Will Go On The MENTOR name and the
15:18finished will go onto the position which
15:20holds the position of the
15:21finalist if I then choose okay to
15:23confirm that I should now be in a
15:25position to run my package so let's see
15:28if it works
15:29if I click on the play Tool I'm hoping
15:31to see a lot of green ticks excellent
15:33I'll just stop running that for the
15:35moment and if I now look at SQL server
15:38and if I just execute my package you can
15:43see that I've got all I've got 104 rows
15:49there were originally 109 take it from
15:51me I've lost the five um finalists who
15:54were mentored by
15:56TSA the only difference this has made is
15:59the sort order has changed the first
16:01thing it did was add the normal people
16:04and then it added the ones mentored by
16:06Sheryl Cole and that's why they all
16:08appear together at the end if I didn't
16:10want that to happen I would have to
16:12implement some sorting
16:14algorithm I hope you've enjoyed this
16:16tutorial on SQL Server integration
16:18Services conditional splits and derive
16:20column
16:21transforms you can find lots more
16:23training resources on SQL server.net and
16:26Microsoft Office at ww w. w.c.

About this transcript

This page contains the full transcript of SQL Server Integration Services (SSIS) Part 7 - Conditional split and derived column transforms by WiseOwlTutorials, generated from the public captions YouTube serves with the video. The transcript has 2,803 words across 406 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.