YT YouTube2Text

YouTube2Text → Transcripts → SQL Server Integration Services (SSIS) Part 19 - Parameters and deployment

SQL Server Integration Services (SSIS) Part 19 - Parameters and deployment — Transcript

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

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:00welcome to this white our tutorial on
0:03deployment and using parameters in
0:05sequel server integration services it's
0:09what you learn in the tutorial we'll
0:11begin with an introduction explaining
0:13about the two types of deployment in
0:14SSIS package and project and introducing
0:18our example package well then going to
0:21look at deploying projects so we'll show
0:22how to create a catalog in SSMS how to
0:25deploy a project and how to then execute
0:28a package that you've deployed finally
0:31we'll look at parameters so I'll show
0:33how to create and use parameters and how
0:35to group them together into things
0:37called environments so let's get started
0:41just before we begin to have a look at
0:44what parameters and deployment means can
0:46I just explain the two different ways in
0:48which you can deploy a project or
0:50packages in SSIS if you right-click on a
0:54project shown in bold type in solution
0:56Explorer you can see that you've got the
0:58option to convert it to something called
1:00the package deployment model now this is
1:03the only way of deploying which was
1:05available to you up to and including
1:07SSIS 2008 release two but from 2012
1:12onwards Microsoft have introduced a
1:14project deployment model which is so
1:16much superior in every respect that
1:18you'll be mad not to use it now the only
1:21reason I mentioned this is to emphasize
1:23that if you're watching this tutorial
1:25video and you're using a version of SSIS
1:28up to and including 2008 release 2 you
1:32can pretty much stop watching and
1:33disregard the video because it won't
1:35work for you so we're using the project
1:38deploying the model which is the default
1:40from versions of 2012 onwards and now
1:44I've got that on my chest let's have a
1:46look at the example we're going to do
1:48[Music]
1:50here's the example we're going to use
1:52for this tutorial it's slightly more
1:54complicated than you might expect
1:55because I want to be able to introduce
1:57parameters later on I've got a table of
2:00finalists that won't surprise anyone
2:02who's been watching the series of videos
2:04from the x-factor reality series there's
2:08109 of them I think
2:09and what I want to be able to do is to
2:11interrogate the database to say shimmy
2:14all the finalists whose finishing
2:16position was less than or equal to a
2:17given number so he finished here in the
2:19first second or third place and whose
2:21names contain B and I can most easily do
2:24that using SQL and I wouldn't have to
2:27touch integration services but that
2:29wouldn't be any fun and certainly
2:30wouldn't teach us about deployment or
2:32parameters so instead I set up a package
2:35to do pretty much the same thing what
2:38I'll do is just explain how it works
2:39there's two variables one is the maximum
2:42position I'm going to say 3 so I'm going
2:45to show all the finalists who got first
2:47second or third position and I want
2:50their name to contain the letter B I
2:51could type in any text I like there the
2:55first thing the package will do is run
2:57an execute SQL task and what this will
3:01do is I'll just go to expressions the
3:05sequel statement has been set to be an
3:09expression and the expression is select
3:14the finalists name from table finalists
3:18where the finishing position is less
3:21than or equal to the value of the
3:23variable I've typed in which was three
3:25converted to a two character string and
3:29the finalists name is like and then I've
3:32got percent followed by the letters I've
3:35typed in and another percent to use a
3:37wild card and if I evaluate the
3:39expression is much easier to see what
3:40it's going to do is going to run this
3:42SQL script select all the finalists by
3:45the finishing position is less than or
3:46equal to the number I've entered and the
3:48finalists name contains the text I've
3:49entered so far so good the data is saved
3:56in a result set and I've said that the
4:01first and only result set number zero is
4:03going to be stored in a variable called
4:04chosen finalists and you can see the
4:08term that's chosen fine that's defined
4:09here as an object variable and will
4:11basically hold it an array of all the
4:13finalists who've been selected if you
4:16want to find out more about this refer
4:18to the earlier tutorial on looping over
4:20aid
4:21rose after Anna execute SQL tasks what I
4:26then do is loop over the rows in this
4:28chosen finalist object variable so if I
4:31double click on that you can see it's a
4:33for each a doe enumerator so it's
4:35leaping over rows and I've chosen the
4:38object variable to be chosen finalists
4:40I've set some variable mappings I mainly
4:43selecting one thing which is a finalists
4:45name so the first and anything returned
4:47from that selection is going to be
4:50stored in the variable called finalists
4:51name so the net result of this will be
4:54this leap will loop over all the
4:55finalists returned and read the name of
4:58the finalists into a variable called
4:59finalists name I then set up an
5:03expression task within the loop set a
5:05string variable called finalists list to
5:08be what it used to be and then I'm
5:10adding on the name of the new finalists
5:13I've just discovered this rather
5:15complicated expression in the middle is
5:17just ensuring that I add on a carriage
5:20return which is backslash n for every
5:22finalist apart from the first one and if
5:25I evaluate that expression it went as
5:27she give me anything because currently
5:29these variables are blank so that's my
5:32expression the net result of running
5:34this leap will be that I'll have an
5:36accumulated string of text in the
5:37finalists variable so all I need to do
5:39is display it and I can do that with the
5:42script task I pass in the name the
5:43finalists variable and in my script I
5:46just display a message which says lists
5:52out the name or the value of that
5:54variable so that's my package is doing
5:57and what I'm going to do now is to run
5:59it to share it in action and you can see
6:01there's listening out all the finalists
6:03who got first second or third position
6:06and you have a B in their name what will
6:11now do is look at deployment using this
6:13package and then go on to look at
6:15parameters
6:16[Music]
6:19the first thing you need to be able to
6:21do before you can use a default project
6:23primum model in SSAS 2012 onwards is
6:27trace on the co2 catalog so I'm in
6:29sequel server management studio
6:31there's my x-factor database I've been
6:33using all this time and I haven't got
6:35any catalogs so what I'll do is right
6:38click and choose to create a catalog and
6:40this is probably something you'll only
6:41ever do once it automatically enables
6:45CLR integration which is good and what
6:48I'm going to do is to automatically is
6:50to tick that box which will
6:52automatically run a store procedure to
6:53tidy up settings when sequel server
6:55first begins to be honest I'm not
6:57absolutely certain what that does but it
6:59seemed a good idea and was recommended
7:01on the internet I'm going to type in a
7:04password it's test if you want to hack
7:07into this and then choose ok what I will
7:10be able to do is later to delete the
7:12catalog even if I don't know the
7:14password and you can see now that I
7:17haven't got any projects within that
7:18catalog but I'm going to remedy that now
7:20by having a look at how you can deploy
7:23projects to that catalog what I'm now
7:29going to do is show how to deploy a
7:31project to the new shiny new catalog
7:33I've just created you can do that by
7:36right-clicking and choosing on a project
7:38in solution Explorer and choosing to
7:40deploy it comes up with a wizard the
7:43first stage of the wizard is just
7:44telling you what the other stages are so
7:46what I'm going to do is click on next to
7:47go into the next stage it's validating
7:50my project so what I can now do is type
7:53in the server name in this instance the
7:57full-stop is showing the fact that the
8:00sequel server database is actually
8:02stored on my local machine so that's
8:04just like tacking typing in localhost or
8:06the name of the machine and this is
8:08acting the named instance within sequel
8:10server but the thing you type in there
8:12will be the name of your server I can
8:14then click on the Browse button and it
8:16will come up with my catalog and what
8:19I'm going to do is create a new folder
8:20within that to keep all these projects
8:22together
8:22so I'm going to call this tutorial and I
8:25could type in a description if I wanted
8:27to
8:27so you can see that's where it's going
8:30to deploy to if I click on the next
8:31button it will just review all my
8:33choices I've made and then I can click
8:35on the deploy button and it will
8:37actually do the work and you can see
8:39there's deploying the project and in a
8:41very short while that's going to finish
8:43I could save report of how this
8:46deployment went but I'm just going to
8:48close this down now if I now have a look
8:50at sequel server initially it looks very
8:52disappointing nothing's changed
8:54but that's because a management studio
8:55to refresh problem if I right-click on
8:58the catalog and choose to refresh it you
9:00can see it's created my folder within
9:03that if I click on that you can see I've
9:06got two things within it I've got a list
9:08of all the projects I've published
9:09there's any one and I've got something
9:12called environments which are
9:13collections of parameters which we'll
9:15look at later what I can do now is to
9:19expand my project have a look at the
9:21packages with it packages within this
9:23and run one or more of them which is
9:26what we'll look at now what I want to do
9:30now is look at two ways in which you can
9:32execute a package the first and most
9:34obvious way is to right-click on the
9:36name of the package I'm going to do the
9:38one we've called parameters and choose
9:40execute and it will pop up with a dialog
9:42box asking me to fill in any parameters
9:44well I haven't got any parameters I will
9:47add some later in this tutorial so I can
9:49just choose okay and it will run my
9:51package and there is the results of it
9:53in a dialog box while it's running the
9:56package is ask the question do you want
9:58to open an overview report and if you
10:01choose yes you get a quick summary
10:03report of how the package executed with
10:05all sorts of information that'll
10:08probably be enough for most people but
10:10if it isn't what you could do is
10:11right-click on your package and choose
10:13to show some of the reports you've got
10:15some standard reports all the executions
10:18which have taken place and all the
10:19validations and you can create custom
10:21reports as well I'll just show you all
10:24the executions and you can see this
10:25report has been run six times so I must
10:28have been testing things in the
10:29background
10:30Chikara and you can also see all sorts
10:33of other information about each of those
10:34executions
10:37so that's the first way to run a package
10:39the second way begins in the same way
10:42you right-click on the package and
10:43choose execute but what you can do is
10:46write your own script so I'm going to
10:49write script to a new query editor
10:51window and what you can see sequel
10:53server does is to create a script to run
10:55the package and that's quite hard to
10:58read so I'm just going to delete it and
11:00to replace it with a version which does
11:02exactly the same thing but with a few
11:04lines and comments in and you can see
11:06that what it's doing is running this
11:07store procedure called creator
11:09underscore execution to line the package
11:11up and then right at the bottom it
11:13actually starts the execution process
11:15and for experience sequel programmers
11:17there's enough information there to be
11:19able to write your own store procedures
11:21to run packages whenever you want them
11:22to whenever you want to the disadvantage
11:28of what we've done so far is that when I
11:30right-click on a package and schuster
11:32execute it I don't have an opportunity
11:33to vary what I'm saying so I'm always
11:37seeing the names of the people who came
11:39in the first three position positions
11:41news names contain the letter B I'll
11:44like to have a list of parameters here I
11:45can vary that so what I'll do is go back
11:47to integration services and add those
11:49parameters and get them to work so
11:52parameter is just like a variable but is
11:54supplied externally to the package I
11:57could create my parameters by
11:59right-clicking on my project parameters
12:00there or rather double-clicking on it
12:03but what I'm going to do instead is add
12:05them within my package and the reason to
12:08do it this way is because parameters
12:11that you're adding your package will be
12:12able to be deployed you can see I do
12:15that by clicking the parameters tab I
12:17can then click on this little icon if
12:20this looks faintly reminiscent of
12:22variables that's because it isn't works
12:24exactly the same way so I'll call my
12:26first parameter Max position which is
12:29the same as the variable name is called
12:31I don't need to do that but that's just
12:33so I can remember the names clearly and
12:36I'll call my second one name contains
12:41and that'll be a string variable and I
12:45don't actually need to set any values
12:47for these because I'll be provided at
12:48runtime what I can then do is delete my
12:51two variables because I no longer need
12:53those what only I'll need to do is go
12:55back to my package and I'll need to go
12:58into my initial s execute SQL statement
13:00tasks I'll need to go into my
13:02expressions and change the sequel
13:05statement source so instead of referring
13:08to a variable it refers to a parameter
13:10and that's easy to do because it says
13:12variables and parameters here and in
13:14fact they're listed in the same list the
13:16only difference is parameters begin with
13:18a dollar sign so what I do is get rid of
13:21my maximum position variable there and
13:25it's dead use my maximum position
13:26parameter and like over to my user name
13:29contains variable there and I'll use my
13:32name contains parameter but otherwise
13:34nothing has changed I'll click and
13:38evaluate expression just to check that
13:39no syntax errors and then choose okay
13:41twice what I now need to do is to deploy
13:45my package and indeed the whole project
13:48there was no need to save it because of
13:51the time of deployment it will save
13:52everything and build it and is saying a
13:55project with this name already exists
13:56click name to replace it I'm comfortable
13:58doing that on this occasion so I'll
14:01deploy it and what it will do is
14:03overwrite the previous project so if I
14:05now go back to sequel server you can see
14:08my project package is still there and
14:09looks exactly the same
14:11whenever I click on it to execute it
14:13it's going to come up with request for
14:15the parameter information I can now type
14:18in the maximum position except that to
14:21the I can't I need to click on the three
14:23dots rather bizarrely and I'm going to
14:25choose the first five positions and for
14:29the name contains I'm going to go for
14:31the left of e which is quite common so
14:33let's see what that produces if I choose
14:35okay and run that I won't show report
14:37and you can see I've got rather more
14:39there's a lot more finalists you finish
14:41in the first five positions and whose
14:43name contains an e so that's how you can
14:46create parameters what I'll look at next
14:48is how you can bunch them together into
14:51thing called environments the last thing
14:55I'm going to show in this tutorial is
14:57how to use some good environments
14:59what are these verses show how they work
15:01and then show how to create them I've
15:04created two environments the first one
15:06is called winners with an S and what it
15:08will show and I'll come back to this
15:10later in more detail is all the people
15:12or the finalists whose name contains an
15:15S and who got the first position the
15:19other environment I've created is called
15:21anyone with an X and what that will
15:22share is anyone who has an X in their
15:25name and you've finished it in the first
15:26three positions all I'm doing here is
15:29trying to illustrate the idea of what
15:30you could do if I then run a package you
15:34can choose either one of those two thing
15:36called environments and what it will do
15:37is substitute the parameter values so
15:39I'm going to choose winners with an S
15:40and what I will do is show all the
15:43people who won you have an S and their
15:45name if I run the package again this
15:48time I'll choose anyone with an X and
15:50this time I'll see all the people who
15:52have gotten X in their name whatever
15:53position they reached so that's what
15:55environments are the way you create them
15:57is extremely messy I think but if you
16:01get the steps in the correct order
16:02not too bad we're going to create a new
16:04environment so what you can do is right
16:06click on environments and choose create
16:08environment I'm going to call this
16:10environment first two places with M and
16:20choose okay
16:22what I can then do is double-click an
16:24Internet at it try that one again and
16:26assign variables now it's absolutely
16:29vital I use the same variable names as I
16:32have in the other two so I chose name to
16:35be my first one and that's a string
16:37variable and for the second one I chose
16:39pause and that's going to be an integer
16:42now what I'm going to do is fill in
16:45values for that so I put an M for name
16:48and for the position I'll put in the
16:50number two so we'll get positions one
16:52and two one gotcha the Nate the query
16:56string is case sensitive so if I put a
16:59capital n it's highly likely I wouldn't
17:02get any one at all because there's not
17:03many finalists who've got a capital m in
17:05their name so I'll choose ok to confirm
17:08that and that's my third environment
17:10what I now need to do is go to my
17:13package right clicking it and configure
17:15it and add a reference to the
17:18environment I've just created so you can
17:20see I've already got to which I'm
17:21referencing what I need to do is add in
17:23a third by clicking the Add button I can
17:26choose my third one which is first two
17:28places with nem and choose ok and they
17:30will add it to the list of environments
17:32I'm referencing I can choose ok the
17:37other thing I need to do with a package
17:39is although I'm referencing the
17:40environment and the environment has two
17:42variables called name and pause I need
17:45to map those variables onto the
17:46parameters in my package I did so as a
17:49messy so I need to right-click on my
17:51package and configure it and for each of
17:53the two parameters I need to click on
17:56the three dots to the right of it I need
17:58to say I'm going to use the environment
18:00variable and this is why it's so crucial
18:02I should use the same names because
18:05clearly Africa I've called the variable
18:06pause in one environment and something
18:08else in another it won't work and you
18:11can see I've already done this I've
18:12assigned the parameter called Max
18:14position to have the value of the
18:16variable called environment variable
18:18called pause and I've assigned the
18:21parameter called name contains
18:23valuing environment vandal called name
18:26the reason I've only got one item in
18:28each list is because SSAS is intelligent
18:31enough to realize that there's no point
18:33offering me up an integer variable for a
18:35string parameter and vice versa so now
18:39I've done all that I hope I can now
18:41execute my package and when I did that I
18:44could if I liked fill in values for my
18:46parameters here but instead of doing
18:49that what I can do is choose to create
18:51an environment and I can say well I'll
18:53use the first two places with em and
18:56what it will do is as it shows here is
18:58take the value of these two environment
19:00variables and substitute them in at
19:02runtime so when I choose okay I will see
19:04all of the people who finished in the
19:06first two places you have a little m in
19:09their name so that's how you create
19:11environments and that finishes this
19:14tutorial on deploying in SSIS and using
19:17parameters we hope you've enjoyed this
19:19tutorial on deployment and parameters in
19:21SSIS you can find loads more training
19:24resources on all things sequel server
19:26net and Microsoft Office at ww-why UK
19:32[Music]

About this transcript

This page contains the full transcript of SQL Server Integration Services (SSIS) Part 19 - Parameters and deployment by WiseOwlTutorials, generated from the public captions YouTube serves with the video. The transcript has 3,602 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.