YT YouTube2Text

YouTube2Text → Transcripts → SQL Server Integration Services (SSIS) Part 11 - Looping Over Files

SQL Server Integration Services (SSIS) Part 11 - Looping Over Files — Transcript

by WiseOwlTutorials · 3,822 words · 498 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00welcome to this wise our tutorial on
0:02leaping over files in sequel server
0:04integration services here's what you'll
0:07learn in the tutorial we'll begin with
0:10explaining what the problem is that
0:12we're trying to resolve and then show
0:14how to delete any old rows and sequel
0:16server tables and how to delete any old
0:18files on your hard disk so that you can
0:20rerun the package again and again then
0:23we'll get into the meat of the problem
0:24we'll look at how to loop over files
0:26using a for each container and how to
0:28add the file names via a variable into a
0:31sequel server table we'll get then going
0:35to look at conditional copying so we'll
0:37copy only certain files into it a
0:40separate folder using something called
0:42an expression constraint and then we'll
0:44set a variable to count how many files
0:46have been copied finally we'll finish
0:49off the package by counting up the
0:51number of rows in the sequel server
0:52table and displaying the two variable
0:55counts we've accumulated in a message
0:57box using a script task so let's get
1:00started in order to be able to create a
1:04package you need to know what is going
1:06to do so let's have a careful look at
1:08what we're trying to achieve in this
1:09more ambitious package I've got a folder
1:12here called folder containing files and
1:15it contains some files in the media
1:17folder and also if you drill down plenty
1:20of files in subfolders to what everyone
1:24to do is write a package which
1:25recursively loops over all the other
1:27files in that folder and its subfolders
1:29doing two things to them the first thing
1:33we'll do is put the names of the file in
1:35a sequel server table like this the
1:39second thing it will do is it will copy
1:41the files to this folder but it will
1:44only copy those files which have got a
1:46txt extension and finally I want to
1:50display a message saying how many files
1:52were in the sequel server table and how
1:54many files have been copied to this
1:55folder now I've got a package which I
2:00created earlier here so let's see what
2:02the final output is going to look like
2:03before we create it the first thing this
2:06package will do is get rid of any old
2:09rose from the sequel server table and
2:11get rid of any files from the
2:13read folder so that's just setting up
2:15our star conditions what the package
2:18will then do is leap over all the files
2:20in the folder and its subfolders doing
2:23three things to reach firstly it will
2:26add a record to the sequel server table
2:28giving the name of the file secondly it
2:32will copy the file to the folder but
2:35only if it ends with the extension txt
2:37so we'll use a precedence constraint to
2:39do that and thirdly we'll increase the
2:43number of files we found by one in a
2:45variable after leaping over all the
2:48files will then be in a position to
2:50count up how many rows are in the sequel
2:52server table and finally show a message
2:55displaying what we've discovered if I
2:57now run this package to show what it
3:00does this is the final message is going
3:02to display 14 files found of which nine
3:05text files were copied to the new folder
3:08and sure enough if we go into sequel
3:11server you'll see that there are 14
3:13files found that's why it goes up to 14
3:15and if we go back into Windows Explorer
3:17and have a look at our copy files you'll
3:20see there's nine files in this folder so
3:23that's what we aiming to achieve it's
3:25time to get started to make this video
3:30shorter than it might have otherwise
3:31been I set up some things to begin with
3:34so we've already got a table called TBL
3:36file in sequel server which contains a
3:39column called file name which is a
3:41string or text type it's NVR car 200 and
3:44in integration services I've already
3:47created a package I've already created
3:50the connection manager to sequel server
3:52and already created an execute SQL task
3:55and what this does is delete all the old
3:58rows from the table so that it doesn't
3:59keep accumulating more and more rows and
4:02if we just double click on there to edit
4:04it you can see that it uses the
4:06connection I've really created two
4:07sequel server an SQL statement is to
4:10truncate the table that is to remove any
4:12records from it but leaving the design
4:14of the table intact what I now need to
4:17do is to make sure that the copied files
4:20folder is empty at the moment it
4:23contains the nine files I've copied over
4:25the previous time I'm
4:26and the package so what I'm going to do
4:28is to create a file system task to
4:31delete all those rows or all those files
4:33so I can do that by adding a file system
4:36task I'll rename it to say empty folder
4:42a short description and then what I need
4:46to do is configure it to say what it's
4:47going to do with a file system task the
4:50best place to start is with the
4:52operation in this case it's going to be
4:55to delete a directory content you can
4:57see all of the standard file operations
4:59you might choose to pick from are listed
5:02so we'll delete the directory content
5:04what I now need to do say which
5:06directory sadly I haven't got a variable
5:10containing the path to the source so
5:13what I'm going to do instead is to have
5:14to create a connection I can do that by
5:18clicking on the drop arrow and because I
5:19haven't already created a connection I'm
5:21going to create one on the fly now so
5:23I'll choose new connection it's an
5:26existing folder which I want to work
5:28with and I can click on the Browse
5:31button to choose that folder and I'll
5:35just go to it and then choose ok if I
5:40then choose ok again I've now got a
5:42package which I can run so let's try it
5:45out it works successfully it's run these
5:51two tasks in probably in different
5:52threads because there's no constraint
5:54between them or no flow between them but
5:56it will still have run both the two
5:57tasks and if I now end that I can have a
6:01look at sequel server and if I have a
6:03look at my file table and look at the
6:05rows within it you can see it's empty
6:07and if I go to my folder and Windows
6:10Explorer you can see that empty too so
6:14that's a good start I've set up my my
6:16position I've got an empty table and an
6:18empty folder what I now need to do is to
6:20loop over all the files adding the rows
6:23into the sequel server table and copying
6:25the files into the folder in Windows
6:27Explorer
6:30so what I'm going to do is loop over all
6:33the files in the folder and to do that I
6:35need a container and specifically a for
6:38each loop container so I'll add that
6:40from the SSIS toolbox and rename it to
6:43say what is going to do which is to loop
6:45over files before I can configure this
6:49what I need to do is do two things the
6:52first is I need to feed the output from
6:54the old tasks into it to make sure
6:55everything happens in the correct order
6:57and secondly I need to create a variable
7:00to hold the name of each file as we go
7:03around the loop so I'm going to call my
7:05variable file name and it's going to be
7:09of type string we'll see later in this
7:11tutorial I'll actually regret leaving
7:13that value blank but for the moment I'll
7:16leave it as it is so now I've created my
7:19variable what I can do is configure my
7:21file leapin tasks by double-clicking the
7:23icon as usual if I get to the collection
7:27tab I can choose a number of different
7:29enumerators and you can see you can loop
7:32over rows items XML files some of those
7:35will come in later packages later
7:37tutorials rather but for the moment
7:39we'll choose to loop over all files what
7:42I then need to do is say which folder
7:44I'm leaping over so I can click on the
7:45Browse button and select the folder and
7:51having done that I can choose ok I'm
7:54going to choose all files if I wanted to
7:57I could choose txt files at this point
8:00but what I wouldn't then be able to do
8:01is to put the names of all the files in
8:04a separate sequel server table it would
8:05just leap over the text files its
8:09importantly the file name is fully
8:10qualified because I want to pick up not
8:13only the name of the file but also the
8:15folder within it so that I can easily
8:16use that for copying files later and
8:19finally in this dialog box I want to
8:22make sure that I traverse subfolders so
8:24that I don't just leap over all the
8:25files in the main folder one core folder
8:28containing files but also in any
8:30subfolders contained within that and
8:31their subfolders too and so on
8:34recursively the final thing I need to do
8:37in this dialog box is to go to variable
8:39mappings and say that in the file name
8:42folder that I've created
8:43I wanted to store the first and only
8:48filename returned from leaping over the
8:51files it seems rather strange that I can
8:54choose an index here and for the moment
8:56I would just assume that you will always
8:57store the first and only item in the
8:59collection returned which of course will
9:01be index number 0 in the file name so
9:05having done all that I can choose ok and
9:08if I now run this package I'm going to
9:12ask you to believe that it stored the
9:14name of all 14 files in a variable and
9:17finished I could set ups and debugging
9:20to check that but I'm a trusting person
9:22so I'm going to assume that's what's
9:24occurring what we now need to do is to
9:27store the names of the files in a sequel
9:29server table and to do that I need to
9:31create a separate task so having
9:37successfully I hope looped over all the
9:39files what I now need to do is add the
9:42name of each file into a sequel server
9:43table and to do that as you can see I'll
9:46add an execute SQL task which will add
9:49the file name to a table I need to
9:54configure this task to say which
9:56connection is going to use it's going to
9:58use the one which rather misleadingly I
9:59called x-factor which will connect the
10:02SSIS tutorial database and it needs to
10:06use a sequel statement so if I click on
10:10the three dots to enter the statement
10:11I've really put this in the clipboard so
10:13what it's going to do is insert into the
10:15table called TBL file in the column
10:17called filename the value as specified
10:22by a parameter which are now create so
10:25if I choose ok to confirm that what I
10:27can now do is go to parameter mapping
10:29and add a parameter now what I want to
10:33do is say that the variable I've created
10:36called filename which will hold the name
10:40of each file as I go around the loop
10:42will which is of type it's just just
10:47writing that over me it's a string
10:50variable and bar car and what it will do
10:53is become parameter number
10:56zero so what will happen as I go around
10:59the loop is SSAS will automatically put
11:03the name of the file as the first
11:04parameter to the sequel statement I'm
11:06attempting to run so that when I come to
11:09run this statement the name of the file
11:11will be substitute substituted as the
11:14first and only parameter in this equals
11:16statement let's see if it works if I now
11:20run this package it seems to work
11:22successfully if I now go to sequel
11:25server and have a look at the table you
11:29can see sure enough it's got the
11:30four-team rosen so it's worked
11:32successfully so we've achieved about
11:38half of what we set out to we've added
11:40the file name to a sequel server table
11:42what I now want to do is to add a task
11:44which will automatically copy the file
11:45if it's a text file to the relevant
11:48folder and to do that I need a
11:50filesystem task so I'll drag that in and
11:53make sure it follows on directly from
11:55the previous task and I'll rename it to
11:58say copy txt files only what I now need
12:04to do is to configure this task to say
12:06what it's going to do so I can double
12:07click on the icon to the left of it the
12:10destination path when copying it to is
12:13going to be the connection I've already
12:16set up called copy files if I could just
12:19come out of there for a second you can
12:20see that when I created a new connection
12:22in the previous filesystem tasks up
12:24there it automatically added that
12:26connection as part of this package so I
12:28can refer to it again when I'm copying
12:29my file so going back into that so it's
12:33going to copy the files it's going to
12:35overwrite the files in the destination
12:36seems to make sense if there are any
12:38it's going to copy a file fortunately
12:41that was a default already set up so I
12:43didn't need to change that as to where
12:45it's going to get the file from the
12:47answer to that is the file name is
12:49already held in a variable so what I can
12:53do is choose our variable from the list
12:54and it will automatically copy the name
12:58of the file which this pass around the
13:00loop is held in a variable called file
13:02name into the folder called copy files
13:05so I can choose ok
13:08there's two problems the first is quite
13:10easily solved if I let my Mouse linger
13:13over that you can see that it says the
13:15filename is used as the variable is used
13:17as a source or destination as empty now
13:20this is a bit of a silly message because
13:22as I loop over the files each time
13:24around the loop the farm name variable
13:25will automatically be populated so in
13:28fact the fact that it's empty at the
13:29moment isn't shouldn't be a problem but
13:33because it's an annoying error message
13:34I'm just going to type in something
13:36ridiculous like test in there and just
13:39to clear the error message and
13:40eventually that message will actually go
13:42it takes a while to catch up but where I
13:45can speed that up is to double click on
13:47it and choose ok the second problem I
13:50alluded to is that at the moment is
13:52going to copy all files and not just txt
13:55ones so what I need to do is put a
13:57precedence constraint on this arrow so
14:01that it only goes onto this task
14:03if the file is as a txt extension now
14:09there's two types of constraints you can
14:10set you can set constraints or
14:13expressions constraints is when you want
14:17something Dierker if the previous task
14:19succeeded failed or was completed you
14:22can set an expression task when what you
14:24want to do is slightly more complicated
14:25as in this case so I can click on the 3
14:29dots to create my expressions and what I
14:31want to do is say with a file name
14:32engine txt all string comparisons in SSS
14:36ok sensitive so the first thing I'm
14:38going to do is go to the list of string
14:40functions and find the upper function
14:42and drag that in the second thing I'm
14:47going to do is to use the write function
14:51which drug-to-drug that in there to say
14:54I'd pick our characters from the
14:55rightmost edge of a string of text the
15:00place I'm getting the characters from is
15:02the file name so I can substitute that
15:04in there and the number of characters
15:07I'm picking out is 4 dot txt so taking
15:13the last four characters in the file
15:14name if they equal txt
15:19making sure that I type that in
15:20uppercase because I've used the upper
15:22function to convert it on the left hand
15:24side now there's one error in that I
15:26then if you spotted it which is because
15:28this is a c-sharp type language when I'm
15:31doing a comparison I need to use equals
15:33equals otherwise when I click on the
15:35evaluate expression button I'll get an
15:37error message so that's my expression if
15:40I choose okay twice when I run this
15:42package what it should do is copy the
15:45files only where they're text files so
15:48if I now come out of that and go and
15:50have a look in Explorer you can see that
15:52it's copied just text files there's 14
15:54files but only nine of them will be
15:56copied across so all looking very good
16:03what I now want to do is to count up the
16:06number of text files copied and to do
16:08that I can add an expression task here
16:10which will add one to a variable which I
16:13now need to create so let's go into
16:14variables and what I'll do is I'll add a
16:16new variable which I'll call file count
16:19and it's going to be of type integer
16:21crucially its initial value is going to
16:24be zero and each time I find a new text
16:26file I'll add one to it so what I can
16:29now do is add an expression to ask into
16:31my loop over files container and make
16:33sure that it follows on from the
16:35previous task and that way it will only
16:37run if this expression constraint is
16:40true ie F the file ends in txt so what
16:43this will do is increment number of
16:46files as we'll call it and what I now
16:51need to do is go in and configure that
16:52to say what is going to do so what it's
16:55going to do is set the variable called
16:57file count equal to the previous value
17:02of the variable called fire account plus
17:041 so we turn around the loop the
17:06variable value will be increased by 1
17:08just check that works it does and choose
17:12ok and if I now run that again
17:16there won't be any proof of this but my
17:21expectation is by the time it have
17:23finished this variable called file count
17:26held the number 9 what I now need to do
17:29is finish up by reporting on to values
17:32one is the number of text files copied
17:34and one number and one is the number of
17:37rows added to the sequel server table
17:41what I now want to do is to calculate
17:44the total number of files which are
17:46added to this table now I could have
17:49done that by using a variable just as I
17:51did with a number of text files copied
17:53but what I'm going to do instead is
17:55create a data flow task after the loop
17:58over files containers finished and use
18:01this the count excuse me the count at
18:05one more time to count the number of
18:08rows and if I double click on this to go
18:13into the data flow the source for this
18:16I'm going to use a sources system to
18:18help me is going to come directly from
18:22my table of files so I'll call that
18:29total of files and the destination for
18:32it is going to be a row count which if
18:35you remember is a handy way of counting
18:37up how many rows are on a table and I'll
18:39feed the one into the other the problem
18:42is that the row count needs a variable
18:44to hold the information in and we can't
18:46use a file count because that's been
18:47used for another purpose so I need to
18:49create a third variable which I'll call
18:51row count and again it will be an
18:55integer variable so what I can now do is
18:58configure the row count to store the
19:00information in the row count variable so
19:03the results of running this data flow
19:04task will be a variable holding the
19:06number of rows added into the sequel
19:08server table I can send the output from
19:13the container into their row count task
19:14and then finally what I can do is
19:17display the information using a script
19:19task so what I'll do is run the count
19:22number of rows into my script tasks and
19:24rename this to say show output I'm going
19:30to go through this relatively quickly
19:31because we've done this in previous
19:32tutorials if I now double click on that
19:35I need to pass in two variables into my
19:38script tasks the first one will be the
19:41row count and the second one will be the
19:44file count
19:46if I then choose to edit the script and
19:48I'm going to use visual C sharp as my
19:50language just because that's the default
19:52what I can now do is to display those
19:59two variables so I'm going to create a
20:01variable an integer variable which I'll
20:04call fire count and set that equal to
20:07the value of the variable called file
20:09count as passed in but I need to convert
20:12that into an integer so that it will
20:16work I'll just complete that and what
20:23I'm hoping is that that line will
20:25automatically store the file count
20:26variable in a variable called file count
20:28and I'll just do a very similar thing
20:30for the row count copying and pasting
20:33the line to make it a bit easier and
20:35finally I can display the results so I
20:39can use a message box to share the
20:41results and I'm going to keep it quite
20:45short I'm going to display the file
20:48count and then the com Rin and then
20:54display the row count oops and with a
21:02bit of luck that will do it
21:03I've got no underlining so I'm going to
21:05just close out down choose ok to confirm
21:07it and then run the package let's see
21:10what happens so I've run the package I
21:14get the file count as 9 and the row
21:16count is 14 so I've copied nine text
21:18files into the copy files folder but
21:20I've added 14 rows into the sequel
21:22server table which is pretty not for me
21:24that everything works successfully we
21:28hope you've enjoyed this tutorial on
21:30integration services leaping over files
21:32you can find lots more resources on net
21:36sequel server and Microsoft Office
21:38training at ww-why UK

About this transcript

This page contains the full transcript of SQL Server Integration Services (SSIS) Part 11 - Looping Over Files by WiseOwlTutorials, generated from the public captions YouTube serves with the video. The transcript has 3,822 words across 498 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.