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

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

Channel: WiseOwlTutorials

Views: 96,395

Keywords: SQL Server Integration Services, Microsoft SQL Server (Database Management System), wise owl, tutorial, ssis

Id: idFCAFAgOO0

Length: 19min 34sec (1174 seconds)

Published: Fri Jun 06 2014