YT YouTube2Text

YouTube2Text → Transcripts → SQL Server Integration Services (SSIS) Part 12 - Other Foreach Loops

SQL Server Integration Services (SSIS) Part 12 - Other Foreach Loops — Transcript

by WiseOwlTutorials · 4,052 words · 573 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:01welcome to this WIS tutorial on using
0:04for each loops and containers in SQL
0:06Server integration Services covering
0:09everything apart from file Loops which
0:10were covered in a previous
0:12tutorial here's what you'll learn in the
0:15tutorial we'll Begin by looking at the
0:17most common enumerator AO enumerators
0:20we'll show what we're trying to do we'll
0:23create an object variable to temporarily
0:25hold a set of rows we'll then show how
0:27to Loop over each row in that set
0:30running some script for each row in turn
0:32and then we'll test out the entire
0:34package to see if it's done what we
0:35thought it
0:36should we'll then look at item in
0:38numerators where you can store a set of
0:40rows in memory by typing it in rather
0:43than using a SQL Server
0:45table then we'll look at ado.net schema
0:48row set in numerators which is basically
0:50a fancy word for looping over all the
0:52rows in a schema in SQL Server we'll
0:55look at how to create the loop in
0:56container and then how to process each
0:58row within the loop within script and
1:02finally we'll have an overview of
1:03looping over XML files using a node list
1:05in numerator and show how to Loop over
1:07all the nodes in the file so let's get
1:12started before we begin this tutorial
1:15let's look at what we're trying to
1:16actually achieve I've got here a table
1:19called Mentor
1:21numbers and what it contains is a list
1:24of all of the mentors from the first
1:26nine series of The X Factor Series in
1:28the UK apologies to am American and
1:30other viewers around the world for
1:32anybody who's been watching other
1:33tutorials in this series this will be
1:35familiar data now what I want to do is
1:38find out which of the xfactor contests
1:40mentors rather has had the most protes
1:43the answer of course is Lou Walsh
1:44looking at the data but we're going to
1:46write a package which will automatically
1:49Loop over all of the records in turn
1:52monitoring the number of protes and
1:54storing which one is the largest
1:57number if this all sounds like uh
1:59keeping of a record set in VBA that's
2:02because it is very similar but in
2:03integration Services it's called an AO
2:07enumerator so to get this to work we're
2:09going to need to obviously create a
2:10package in integration Services which
2:12I've already done and within that create
2:15a connection to our SQL Server
2:17database and the next thing I'm going to
2:19do is to use an execute SQL task to read
2:23in the data from the mentor numbers
2:25table into something called an object
2:27variable so that I can then enumerate
2:30all the records within that
2:34variable before I can create my execute
2:38SQL task what I need first is somewhere
2:40to put the data and the place I'm going
2:43to put it is a variable which would be a
2:45temporary holding place for a set of
2:47records and the difficult thing about
2:49this variable is not creating it but
2:50choosing what data type to give it so
2:53I'm going to call it
2:56mentors and the data type is actually
2:59really should be record set but there
3:02isn't such a data type so I'm going to
3:04choose the vaguest thing I possibly can
3:06which is object and what will happen is
3:09integration services will allow me to
3:11put a set of rows inside that
3:13variable so now that I've got that I can
3:16create my execute SQL task to begin my
3:19package and what it's going to do is to
3:21load the
3:23mentors into a variable so that's
3:26exactly what I'll call it if I then
3:28configure that by clicking on it the
3:31first thing I need to do is to say which
3:33connection is using and it's using the X
3:35Factor one I then need to put in a SQL
3:39statement happily I've got one in my
3:42clipboard is going to select all of the
3:44three columns from the mentor numbers
3:46table and then what I need to do is to
3:48choose a record set to put that into and
3:52what I'm going to do is export the
3:53entire set of data into my
3:56variable now that's all very well and
3:58good that will create an entire record
4:01set but where will it put it to answer
4:04that question I need to go to my results
4:06set tab on the left hand side and add in
4:09a new
4:10mapping there could be several outputs
4:13from this execute SQL task I'm going to
4:17take the first one which as in most
4:20things in integration Services is
4:22numbered from zero so that is equivalent
4:24to the first set of rows returned from
4:27my execute SQL task in fact it's the
4:29only one one and the variable I'm going
4:31to store it in is the only one I've
4:33created so far which is the one called
4:36mentors so if I now choose okay to
4:38confirm that and run my package I think
4:42I'll probably just need to change it and
4:44set that to be my star package
4:46first what will happen when I run it is
4:49it will load up the data from the
4:50mentor's table into an object variable
4:54and for the moment you're going to have
4:55to believe me that that's what's
4:56actually
4:57happening what we now need to to do is
5:00to Loop over those records doing
5:02something to each in
5:04turn before I can create my fre
5:07container to Loop over the rows of my
5:10table or rather my variable I need
5:12somewhere to put the information so what
5:15I'm going to do is create no less than
5:17five
5:18variables the first three variables are
5:20all going to hold Columns of the current
5:23record in the AO numerator I'm leaping
5:26over so I'll call the first one a first
5:28name and make that into a string
5:33variable I'll call the second one last
5:35name and make that also into a string
5:40variable and the third one I'll call
5:43protes if I could spell it and that's
5:46going to be an integer it holds how many
5:47protes each person
5:50has the other two variables I'm going to
5:52create are going to be to keep track of
5:55which is the highest number of protes at
5:56any point in time and what's the name of
5:59the mentor who has that number of
6:01protes so I'll call the first one
6:04highest number which will hold the
6:06highest number encounted so
6:08far and the last one I'll call highest
6:11name which will hold the name of the
6:14person who has that number and these
6:17last two variables will be used in
6:19script and will keep changing as I come
6:21across a mentor who's have more protags
6:23than any yet
6:25encountered so having created those
6:27variables what I can now do is to create
6:31a for each
6:34container and what I'll do is feed the
6:36execute L SQL task into that and give
6:39this a different name so let's call
6:43this Loop over
6:48mentors and what this will do is read in
6:50each row one by one so to configure it I
6:53can double click on it and the first
6:56thing I need to do is say rather
6:57bizarrely I have to go to the second had
6:59to do this what sort of a collection it
7:01is I'm leaving over now there's a few
7:04here there's for each file which I
7:05covered in a pre previous tutorial and
7:08I'm going to cover most of these in the
7:10current tutorial so later on we'll look
7:12at for each item and we'll look at for
7:13each node list and we'll also look at
7:15for each Ado for each schema but what
7:19we're going to do for now is look at for
7:20each Ado enumerator which we'll look at
7:23each record in
7:24turn I then need to say which variable
7:27I'm going to use to get the information
7:30from and I'm going to use my M's
7:32variable which if you remember contains
7:34a whole set of
7:36rows what I can then do is create
7:38something called variable mappings and
7:41what I'm going to do is say that the
7:43first variable called first name will
7:45take the value of the first column in
7:48the set of
7:49Records the last name column will
7:52contain the value in the second
7:54column and finally the prot's column
7:57will contain the value in the third
7:59column so basically it's mapping the
8:01columns on one by one if I then choose
8:04okay to confirm that and if I run my
8:06package what you should see again is
8:08success but again you're going to have
8:10to trust me with this what it's doing is
8:12looping through every single Row in the
8:14underlying table in turn let's just have
8:16a look at that it's reading Cheryl into
8:18the first name variable Cole into the
8:21last name variable and protes into the
8:23Pro's variable then discarding all those
8:26values and going on to the next record
8:28and doing that for each of the nine
8:29records in turn until it's run out of
8:31rows at which point it will
8:33finish so what I now need to do is
8:36within my for each container actually do
8:39something with the information I'm
8:44gathering so what I'm going to do now is
8:47to add a script task within my container
8:52and what will happen is this will run
8:53every single Row in the underlying table
8:56which has been loaded into the object
8:58variable what it's going to do is the
9:00check for the highest number of
9:06proes and what I can now do is edit it
9:08to say
9:09how now I'm going to put three variables
9:13into this which are equivalent to the
9:14three columns for this particular record
9:18so if I click on that little icon I'm
9:20going to pass in the first name the last
9:23name and also the number
9:26protes and those will be readon
9:28variables because the code only needs to
9:30check
9:31them I'm also going to set two read
9:33write variables which are the highest
9:35name and the highest number so that at
9:37any time in running through the code
9:39those will always be set the maximum
9:41number of prot encountered so far and
9:43the name of the person corresponding to
9:45that so I'll choose okay to confirm
9:48those two things and now I can edit my
9:50script to say what it's going to do now
9:53at this point you could have had to sit
9:55watch me do a lot of typing so I've
9:57taken the liberty of putting everything
9:59in clipboard and pasting it
10:00in let's have a quick look at what
10:02that's doing the first three lines of
10:05code are reading in the first name last
10:07name and number prodigies from variables
10:10passed in into variables within my C
10:14script what I'm then doing is also
10:16reading what the current highest number
10:18is and if I've come across a number for
10:21this particular Mentor where the number
10:24prodigies is greater than the highest
10:25encounter so far I replace the current
10:28highest number with a new new one and
10:30that's what this line of code
10:32does I also store the name of that
10:34person by taking the first name and
10:36concatenating with a space in the last
10:38name so at any point I can be absolutely
10:40confident highest number holds the
10:42highest number of protes encountered so
10:44far and highest name holds the name of
10:46that
10:47person so if I come out of that and
10:50choose okay to confirm it I now just
10:52need to add a script task to the end and
10:55I can then confirm that this is actually
10:57doing the correct thing
11:01so what I can now do is add a script
11:03task to the bottom of my package and
11:06make sure that it is the last thing
11:07which runs and what this is going to do
11:10is display the name of the busiest
11:11Mentor so I'll call is something very
11:13similar to that to say what is going to
11:16do I can then double click on that as
11:18always to configure it and the script
11:20task is going to know need to know two
11:22bits of
11:23information the first one is the name of
11:25the mentor who's been the busiest who's
11:27got the most proes and the second thing
11:29is how many proes they
11:31had if I then choose okay to confirm
11:34those two variables being passed in I
11:36can then edit my
11:37script and again rather than having to
11:40watch me type things in I've copied it
11:42into the clipboard so I should just be
11:44able to paste it in
11:45there so what it's going to do is read
11:47in the names value of two variables the
11:49name of the person and the how many
11:51protes they had and then use a message
11:53box to display that information on
11:55screen so we should say busiest ment is
11:58Louis Walsh with 30
12:00proes so let's see if it does if I now
12:03come out of that and choose okay to
12:04confirm my script what I should now be
12:06able to do is to run this and I'm hoping
12:09i'll see a nice friendly message
12:12consisting of the me information I built
12:13up in the C
12:15script so I can choose okay and stop
12:17debugging that and that all seems to
12:20have
12:21worked it's time now to have a look at
12:23some of the other containers and other
12:25for each Loops you can use within
12:26integration services
12:30the type of FR Loop which is called an
12:33Ado enumerator which I just covered is
12:36probably the most useful in
12:38ssis but just for the sake of completion
12:41this part of the tutorial will show two
12:42or three
12:43more the first one I'm going to show is
12:45how to use loopover items now to do that
12:49I can embed the data within the package
12:51so what I'm going to do is I've taken a
12:53copy of my original package and I'm
12:55going to delete the execute SQL task so
12:58rather the information coming from an
13:00SQL Server table what I'm going to do is
13:02configure my loopover mentors so that it
13:04does something slightly
13:06different instead of leaping over the
13:08records in a SQL Server table what is
13:12going to do is leap over a set of
13:14items and in order for that to work I
13:16need to Define what the items are here
13:19I'm going to use exactly the same table
13:21just to make it easy to understand so
13:23I'll have three columns the first two
13:25being a string column and the last one
13:27being an integer so if I chose okay to
13:29confirm that and I'm now going to type
13:32in
13:33three potential mentors in The X Factor
13:37perhaps these are for a future Series so
13:40I'll have Nigel farage I'll have Adele
13:43Atkins who I think would be a busy
13:44person and I'll have Basel brush he
13:47tends to feature in a lot of my
13:50examples if I then go to variable
13:52mappings what I need to do as I did
13:55before is say that the first name
13:57variable maps onto the First Column I've
13:58created
13:59the last name to the second column and
14:01the proes to the third column so in fact
14:04the only difference with what I've done
14:06is rather than refer to a SQL Server
14:08table externally I've embedded that data
14:11within the actual
14:13task if I then choose okay just to show
14:16there's no trickery about this I'll just
14:17make sure that that is my startup object
14:20and if I now run that package it should
14:23produce exactly the same result although
14:25obviously with a different name and a
14:26different number of
14:28protes so using items is just an
14:30alternative to using SQL Server tables
14:33but on balance I would argue probably
14:35less
14:38useful we've looked now at how to Loop
14:41over uh tables in SQL server and also
14:44how to Loop over items what I'm now
14:47going to do is showare how to Loop over
14:48SQL Server
14:50schemas you can see what's possible by
14:52typing this command in SQL Server select
14:54star from information schema Dot and
14:58when you type in the dot which I'll now
15:00do you can see all the possible schemas
15:02you can um list out and loop over so
15:05what I'm going to do is Loop over all
15:06the tables and when I run that you can
15:09see there's four rows and what I'm going
15:11to do is store the third column so
15:13that'll be column number two from the
15:15point of view of integration Services
15:17I'm going to store that in a variable
15:18called table name and then write out
15:21those tables to a text
15:23file so let's see how this works in
15:27integration Services I've already
15:29created a package called looping over
15:32schemas and what I'm going to do is to
15:34add to that add to that a for each Loop
15:37and what this is going to do is Loop
15:39over
15:40tables I'll configure that as always and
15:44the sort of collection this is
15:46is for each ado.net schema row set in
15:50numerator and what that basically means
15:53is ssis will create a table in its
15:55memory of all the records within the
15:57schema you've chosen and then Loop over
15:59all those
16:01rows now I need to create a connection
16:03and this is where it gets a bit strange
16:06I could have done this separately but
16:07I'm going to create the connection from
16:09within this
16:11task and I need to create a new
16:14connection and the provider for this has
16:18to be this a SQL native
16:22client I'm sure Googling would tell you
16:25why and I'm sure at one point I knew why
16:27but for the moment for me it's enough to
16:28know that that's what I need to
16:30use my server
16:34name is that and I can choose my
16:37database I'm connecting to as X Factor
16:39and then choose okay and okay again and
16:43the scheme I'm going to import is all
16:44the tables from that
16:47database so having set that up I now
16:49need to create my variable mappings I
16:52haven't had the forite to create a
16:54variable called table name so just for a
16:56way of contrast what I'm going to do is
16:58create it on the f by choosing to create
17:00a new variable at this
17:01point I'll call my new variable table
17:05name it's going to be a string
17:08variable and I'll assign it to the third
17:11column in the schema which is column
17:14index number
17:15two so if I choose okay that will
17:18automatically create a table in memory
17:22corresponding to the scheme of tables
17:25within the xact database and store for
17:28each row the name of the table in the
17:30variable I've created called table
17:34name so it's time now to see how to use
17:36that within
17:39script what I'm going to do then is to
17:41add in a script task which will be
17:44executed for every single table found in
17:46the schema and what it will do is write
17:49out the table name to a text
17:52file and although that's a bit over the
17:55top as a name it certainly does describe
17:56what it's going to do if I then
17:59configure that I need to pass into my
18:01script task the name of the table and if
18:04you remember I created the variable name
18:05on the fly but it's been added to the
18:07collection of all the variables within
18:09this
18:10package I can then edit my script and as
18:13before what I've done is put the code
18:15into the clipboard to save home to sit
18:17watching me type it out here's what it's
18:19going to do it's going to use a variable
18:21called tame to hold the name of the
18:23table this time around the loop it's
18:26then go to write out to a text file
18:27called tables. TX
18:29the name of the table and then close the
18:32text
18:33file if I now close that and try
18:36executing my
18:38package what it should be doing is
18:40creating a text file the reason that
18:42runs slightly more slowly than you might
18:44have expected is it's not a very
18:45efficient way to do things keeping
18:47opening and then closing the text file
18:50and now stop debugging I should find
18:53that in Windows Explorer which I think I
18:56have open here I've got my file called
18:58tables. txt and if I double click on
19:01that to have a look you can see it
19:02contains lots and lots and lots of
19:03system files and then right at the
19:06bottom the four
19:08user uh sorry the four tables which
19:10aren't system
19:13tables if I'd had a gain a bit of
19:15foresight it might have been a good idea
19:17to add an additional check that the
19:20table scheme was dbo before writing out
19:22the information to the text
19:25file what I'm going to do now is to
19:28create a an overview of a package to
19:31import an XML
19:33file I could use the wiow website
19:35sitemap but it's too complicated it's
19:38got an opening line and it's got an
19:40additional attributes for the URL set
19:42tag none of which will all of which will
19:45stop it working so instead I'm going to
19:48use a tidied sitem file where I've got
19:51rid of all those extra features and it's
19:53just nice and simple and easy to read
19:55the three nodes are URL set URL
19:59and then
20:00Lo and so what I'll need to do is
20:02specify what those nodes are and it will
20:04automatically be able to import the
20:06websites contained in the middle of it
20:08and create that as a list in a text
20:10file so let's do that I've already got a
20:14package called loopover node list what
20:16I'm going to do is add to that for each
20:18Loop container which will Loop over the
20:22nodes what I can then do is configure
20:25that and say what sort of a loop it is
20:28so in this case it's going to be for
20:30each node list in
20:33numerator it's going to get this
20:34information not by type typing it in but
20:37from a file so I need to create a new
20:39connection to that file and because it's
20:42an existing file I'll be able to find it
20:45and there it is on my hard
20:48disk what I now need to do is specify
20:51the outer path string so basically the
20:53nesting of the nodes and this
20:56is starting with URL set
21:00within that I've got
21:02URL and within that I've got
21:05L my variable mappings I'm going to
21:08create a single variable on the Fly
21:10which I'll call node
21:13name and that will tie into the first
21:15bit of information returned and probably
21:17the only one as
21:19well so I've now got a position where
21:21it's going to Loop over all the nodes in
21:22that file to test it out as always I
21:25need to add a script task within that
21:28container so that each for each node it
21:31will write it out to a text
21:34file so I'll call my script task write
21:37out node to text file if I then
21:40configure that I need to pass into it
21:43the variable which I've just created
21:45node name which will contain each URL
21:47within the XML sitemap
21:50file I can then specify my script and
21:54yet again I've got the code in my clip
21:58board so I should just be able to paste
22:00it in there it's going to get a variable
22:02to find out the name of this node and
22:05then write it out a file called nodes.
22:08txt so let's see if it works if I choose
22:10okay to confirm that I've set this
22:12already to be my startup package so I
22:14should just be able to play that and
22:16it's looking like it's worked but as
22:19always the proof will be did it create
22:21the text file that file definitely
22:24wasn't there before and if I look at it
22:26you can see it contains the three
22:28website addresses so it
22:31worked you can find lots more training
22:34resources on Microsoft Office sequel
22:36server andet training at
22:39www.wis al.uk

About this transcript

This page contains the full transcript of SQL Server Integration Services (SSIS) Part 12 - Other Foreach Loops by WiseOwlTutorials, generated from the public captions YouTube serves with the video. The transcript has 4,052 words across 573 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.