YT YouTube2Text

YouTube2Text → Transcripts → SQL Server Integration Services (SSIS) Part 10 - Sequence Containers and FOR Loops

SQL Server Integration Services (SSIS) Part 10 - Sequence Containers and FOR Loops — Transcript

by WiseOwlTutorials · 2,810 words · 408 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:00welcome to this wise hour tutorial on
0:02using sequence containers and for Loops
0:04within integration
0:06Services here's what you'll learn in the
0:09tutorial we'll Begin by having a look at
0:11sequence containers so we'll have a look
0:13at the task host container the default
0:16container for everything you add to a
0:17package then we'll look at how sequence
0:19containers work and finally we'll look
0:21at an example of a sequence
0:23container we'll then go on to look at
0:25for Loops so we'll see what for Loops do
0:28and then look at a practical example of
0:30a for Loop to list out all the prime
0:32numbers so let's get
0:34[Music]
0:37started before we have a look at
0:38sequence containers what they are and
0:41why you might want to use them let's
0:42first have a look at something called
0:43the task host container which is the
0:46name given to the entire
0:50package to show you the constraints is
0:52puts on what you do let's try adding a
0:55couple of data flow tasks there's one
0:59and there's another
1:00and what you'll see is it automatically
1:02gives the second task a different name
1:05and that's because task names have to be
1:07unique within the container and if I
1:10were to try renaming the second task to
1:12data flow task as well when I click
1:14somewhere else it will give me an error
1:16and tell me that another object in the
1:18collection already uses this
1:21name so you can't have two tasks with
1:23the same
1:24name in addition to that it won't
1:27surprise you to know that you can't have
1:29two VAR variables with the same name
1:31either so if I call my first variable
1:33there I which is going to hold an
1:36integer if I then had another variable
1:39and try calling that one I as well you
1:41can see that when I try to move off it
1:43it gives me the same sort of error
1:45message and tells me I can't have two
1:46variables with the same name within the
1:49task host
1:50container what I'm going to do now is
1:53show you how you can overcome this
1:55constraint by using something called
1:57sequence containers which independent
1:59boxes Each of which can have their own
2:02tasks and their own variables so let's
2:04move on to
2:05[Music]
2:07that what I'm going to do now is to add
2:10in a couple of sequence containers you
2:13can do that by dragging the sequence
2:16container task from the ssis
2:19toolbox I'll call my first one box
2:23a showing a singular lack of
2:26imagination and what I'm going to do is
2:29copy that task
2:31and then excuse me and then paste it and
2:34guess what I'm going to call the second
2:35one you guessed it box B so now I've got
2:39my two separate sequence sequence
2:41container tasks what I want to do is
2:43prove that each task that you add within
2:46that is independent of any other
2:50sequence container so I'll just make my
2:52containers a bit bigger so I can fit
2:53things into
2:54them I'm going to add two data flow
2:57tasks into box a I could have used any
2:59any task I like for this to illustrate
3:01it that's just the one at the top of the
3:03list there's the first and there's the
3:06second and as before it gives each task
3:09a distinct name because within the
3:11sequence container all the tasks have to
3:14have unique
3:15names but if I now try adding data flow
3:18task to box B you'll see that it's quite
3:21happy to give the first task I add a
3:24name data flow task which is identical
3:26to a name in another sequence container
3:30and what this shows is that each
3:31sequence container is completely
3:35independent the same thing applies to
3:37variables so if I go down to my
3:39variables what I'm going to do is add a
3:42new variable and I'm going to call it J
3:45to show an integer and set it equal to
3:48the value of
3:49one if I were to add another variable
3:51called J it wouldn't let me because the
3:54scope of that is limited to the entire
3:56package which I've called containers
4:00what I'm going to do is change the scape
4:01of this variable by moving it it's not a
4:03particularly good
4:05name but what I'm going to do is move it
4:08to box a rather alarmingly it will
4:11disappear but that's because I don't
4:13have box a selected if I now click on
4:16that box a and display my variables
4:19which I'll just pin that you'll see that
4:21the variable called J has appeared if I
4:23click off it it disappears again and
4:26that's because the scope of it is
4:27limited to the sequence container called
4:29box a
4:31I can do the same trick to create
4:32another variable which I'll also call J
4:35this time I'll set it to a value of two
4:37and it's let me do this because the
4:39scope of the second variable is the
4:40entire package and the scope of the
4:42first one is limited to the Box a
4:45sequence
4:46container I could do the same trick to
4:48move the second
4:50variable to box B and what I've now got
4:53it appears is no variables whatsoever
4:56but when I click on each sequence
4:57container you can see that it pops up
4:59with each variable the first one is got
5:01the value of one and the second one has
5:04got the value of
5:05[Music]
5:06two what I want to do now is give a very
5:09simple example of how you might use the
5:11sequence container in
5:13practice I've got a simple package here
5:16and what it does is deletes rows from
5:18three tables and then from an Excel
5:21workbook of X Factor finalist with which
5:23regular viewers will be quite familiar
5:25it Imports three worksheets the list of
5:27the people contestants the list of the
5:29series and the list of the
5:31mentors having imported those three
5:34worksheets into three different tables
5:36what it then does is take the workbook
5:38and copies it to another folder and if I
5:41run that package you'll see it chugging
5:43away and finishing so it's
5:46working it's not too important what the
5:48package does but what I want to
5:49emphasize is that these three tasks
5:53should all have to run before I can copy
5:55the finless workbook but there's
5:57actually no particular reason that they
5:59should run in this sequence it doesn't
6:00matter what order they
6:02happen if I was creating this package I
6:04think a neatest solution would be
6:07this create a sequence
6:11container and let's call it
6:15import
6:17tables and then take these three tasks
6:21and drag them into the sequence
6:23container now as it happens when I do
6:25that it's not going to work the message
6:27is coming up with is saying I have to
6:29remove the precedence constraint before
6:31proceeding so what I'll need to do is
6:33break the links between those three
6:34tasks and the outside
6:38world and then I'll be able to add drag
6:41the three tasks into the sequence
6:43container what I can then do is take the
6:46first
6:47task and feed it into the import tables
6:51one and then feed the output of the
6:53input tables one into the final copy
6:56file
6:57task the final thing I'll be temped to
6:59do is to break the links between my
7:03three
7:05Imports there's actually no particular
7:07reason that I should import the data in
7:08this order it doesn't matter and by
7:11breaking the links the multi-threading
7:13nature of ssis will allow it to uh
7:16schedule these tasks in the most
7:17efficient
7:18way if I then run this package again you
7:22should see that it does exactly the same
7:23thing as it did before and you'll have
7:26to take that on
7:27trust but you can see it's finished
7:30the beauty of it is is that these three
7:32tasks have been confined to a sequence
7:35container so I've got more control over
7:37when they
7:38run it also of course allows me to move
7:40that around anywhere I like on the
7:42screen and the three tars contained
7:44within it will follow
7:46[Music]
7:49it what I want to do now is show a
7:51particular type of container called a
7:53four Loop container they're not actually
7:56that useful four each Loop containers
7:58are far more useful ful and they allow
8:00you to iterate over files and Records
8:02but we'll cover those in a later
8:04tutorial for now let's just look at the
8:06humble for
8:07Loop what I'll do is add one of those
8:09tasks into my package and rename it to
8:13count from one to 10 and that's actually
8:18an indication of what the loop is going
8:19to do it's probably the least ambitious
8:22package I've ever
8:23written in order to be able to count I
8:25need a variable to hold the counter so
8:28I'll add a variable
8:30and I'll call it I nice short name and
8:33it doesn't actually matter what value it
8:35will hold initially because the for Loop
8:37will overwrite
8:38it there's a Red Cross which tells me I
8:41haven't yet configured my for Loop so
8:43what I can do is double click on the
8:44symbol at the top left and set three
8:47things the initial expression the assign
8:50expression and the evaluation expression
8:52and probably I'll do it in that order
8:55the initial expression is I want to set
8:56the value of the variable called I equal
8:58to one
9:00you'll notice there's yet another way of
9:02referring to variables this time I can
9:04proceed in them with the at sign it's
9:07very similar to how it works in SQL for
9:09those who know that my assigned
9:11expression each time around the loop
9:13will take the value of the variable
9:14called I and set it equal to one more
9:18than it used to be and that's a standard
9:21programming convention to get a variable
9:22to increase by one each time around a
9:25loop what I now need to do is to set
9:27something called the evaluation
9:28expression
9:30which specifies an expression that will
9:31stop the leap when the expression
9:33evaluates to
9:34false so what I'll do here is say that
9:37the variable called I has to be less
9:39than
9:4010 and so what it'll do is set the
9:42variable to one increase it by one each
9:44time around the leap and keep going for
9:46as long as it's less than 10 so if I
9:48choose okay let's try running that I
9:51think it's going to be a bit
9:52disappointing because while it runs
9:53there's actually no evidence that it did
9:55anything useful so what I'll do this
9:57time is right click on the for Loop and
10:01set break points edit break points and
10:05what I'm going to do is add a break
10:06point to the on poost execute event and
10:09what that means is when the this
10:10particular task is finished executing it
10:13will take me into debug mode so I can
10:14inspect the value of my variable if I
10:17run it again now the same thing will
10:19happen but this time you can see the
10:20yellow arrow shows it's paused and what
10:23I can now do is have a look at my locals
10:27window for example debug windows
10:29locals and you can see there's my
10:32variable called I and it has a value 10
10:35so it was increased once each time
10:37around the loop until it reached a value
10:39which was no longer less than
10:4110 so that's a simple for Loop what I'll
10:45do is just finish running it or rather
10:47abort it and what we'll do now is look
10:50at a practical example of
10:51[Music]
10:54this what I'd like to do now is to show
10:56a slightly useful example of a for Loop
10:58in practice ice I've got a package here
11:01what it will do is leop over all the
11:02Primes or rather all the numbers for
11:04each integer it will test if it's Prime
11:06and if it is it will add it to the list
11:08and the end result will be a script
11:10which will display the value of the
11:11first n primes here I've Set n to be 100
11:14or at least I'm testing all the integers
11:15up to 100 if I run that package you'll
11:18see the output of it is a list of all
11:20the Primes 2 35 7
11:24Etc what I'll now do is show you how we
11:27did that so I'll just close that down
11:28and go back back to one I've partially
11:30created earlier this already creates the
11:33script to the bottom which will display
11:35the output of the final message what it
11:37doesn't do is contain either any
11:38variables or the for Loop itself so the
11:41first thing I need to do is create three
11:43variables the first one I'm going to
11:45call test number and that will be the
11:47integer I'm testing to see if it's
11:49Prime the second variable I'm going to
11:52call Max number and that will be the
11:55highest number I'm going to go up to so
11:56I'm going to test the first 100 numbers
11:58so that we're not here all day and the
12:01third variable I'm going to call Prime
12:03string and that will be a string
12:04variable which I will initialize to two
12:08and the reason for that is I want to
12:10begin assuming that two is non Prime and
12:12test only the odd numbers to speed
12:14things
12:15up having created those variables what I
12:17can now do is to create my for Loop so
12:20I'll add that in here and I'll call it
12:23test
12:25primes and I'll configure
12:27it to say that the initial expression
12:30should be that the test number should be
12:32three I'm going to start with the first
12:34odd number the assign assign expression
12:38should be that each time around the loop
12:39I'll set the test
12:41number to be two more than it used to be
12:44so I'll test all the odd numbers 357 Etc
12:48and the evaluation expression is that
12:50I'll keep going until the test number is
12:52less than equal to the maximum number
12:54specified which in this case is
12:57100 what I then need to do is add a
12:59script within the for Loop and what this
13:02will do is test if Prime so it'll test
13:06each number to see if it's
13:08Prime I double click on that I need to
13:10pass in two variables as a readon
13:13variable I need to pass in the number
13:16I'm testing to see if it's prime
13:18otherwise the script won't know what it
13:19is it's
13:20testing and as a righton variable I need
13:23to modify the prime string so I need to
13:26add a prime to the string if it is inde
13:30Prime what I can then do is edit my
13:33script and I've got something in my
13:36clipboard I hope so if I just paste it
13:39in there I'm not especially proud of
13:41this what it will do is find out what
13:43the number is and then what it does is
13:45loop from create a variable called I and
13:49Loops from two all the way up to the
13:51maximum number I'm test the number I'm
13:53testing which in this case is test
13:55number the percent symbol divides the
13:58number I'm testing ing by an integer and
14:02looks at the remainder of it and if that
14:04remainder is zero you can assume the
14:05number was perfectly divisible and it's
14:07not prime if we get all the way through
14:10that Loop just clear that and we haven't
14:14found it it's not
14:16prime it must be Prime in which case we
14:20add the number onto the end of the
14:23string and then end the script
14:31what I now need to do is just to connect
14:34those two tasks
14:36up the second script passes in the
14:40highest number and the prime
14:44string and just displays it and you'll
14:46see in
14:48here there's just a message box
14:50displaying the message primes up to this
14:52number are couple of carriage returns
14:55and then the list of primes we built
14:57up so what we now need to do see if this
14:59actually works to make it fair I'll set
15:01it to be the startup object so you can
15:03see there's no trickery going on and if
15:05I now execute that package you should
15:08see that it lists out all the
15:14Primes and that completes this look at
15:16four Loops what I would do now is to go
15:19on to look at some of the tutorials on
15:21four each Loops there's a couple there's
15:23one on files and one on records because
15:25they're far more useful in practice now
15:27we've got the basic idea in
15:30place you can find loads more training
15:33resources on integration Services other
15:35aspects of SQL server.net and Microsoft
15:38Office at
15:40www.wis .co.uk
15:42[Music]

About this transcript

This page contains the full transcript of SQL Server Integration Services (SSIS) Part 10 - Sequence Containers and FOR Loops by WiseOwlTutorials, generated from the public captions YouTube serves with the video. The transcript has 2,810 words across 408 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.