[Music] [Music] so hello everyone my name is andy leonard and i'd like to welcome you to this presentation for the data platform summit 2020. i'm honored to be here with you today and i hope you're interested in learning a little about azure devops and how we can apply continuous integration to ssis using azure devops well with that let's dive right in just a little bit about me if you want to know more about me probably your best bet is to go to your favorite search engine and type in andy leonard data i'm sure you'll find me and there's some information about me here and that's quite enough about me today we're going to talk about a brief history of cicd continuous integration and continuous deployment we're going to do a little bit of source control by example and we're then going to dive in we're going to look at azure devops agents followed by a dive into some pipelines and how they can help us with with azure devops and applying continuous integration to ssis now i need to be frank with you here this is a 102 under 300 level we are not going into some of the more advanced topics some of the other features that you can do in azure devops but this is mainly for people who are just getting started or who aren't using azure devops and i want to get you up at least to that middle level maybe the 200 300 level and if i can do that i will consider um my goal achieved for for this uh session so if we look at um just kind of my history with this topic i presented years ago at the pass summit and i've had this dream of ssis developers being able to being able to do the same sorts of source control and build automation that the javascript and the node and c-sharp developers have been doing for decades now frankly i love the automation the build automation that was first surfaced for me the first place i saw it i'll say it that way was in team foundation server and you can see this is back in 2007 i actually traveled to denver to the pass summit with two laptops and a lynx us router so i didn't have enough beef on one laptop to run a presentation and do these demos so i brought two and it was a it was an okay presentation i'll say it that way but the dream of what i'm uh what i had back then my dream what i shared in that presentation that's what i want to share with you today a lot of those uh those things i dreamed about have come to pass and now are available to us so i also wrote a book about this called data integration life cycle management with ssis or dilm with ssis this book is um it was written back in 2017 i didn't talk much about either team foundation server or get or azure devops because that functionality just wasn't where it is today um i need to update the book i really do but you know we'll see how that works out maybe maybe in 2021 one important thing that i did write in that book is something that applies to us here because maybe you're thinking andy we're talking about ssis sql server integration services and you may be thinking this is data it has nothing to do with software development well that's not accurate ssis suffers from having the name of a relational engine in its in its title but the truth is ssis is software development one clue is we build ssis in visual studio which is an integrated development environment an ide and so software development best practices apply to what we're doing so when we start talking about this the very first thing that i want to talk about in ci cd is is this idea of source control so i believe there are two types of developers those who use source control and those who will because you will lose some code you'll you'll make some changes and you'll forget to check them in or maybe you're not using source control at all and something unfortunate will happen and and you'll lose those changes and after that you will start using source control we're going to talk about connecting to azure devops configuring a project there then configuring your ide your visual studio environment to work with azure devops and then we're going to add that solution to source control and rather than do this in demos i've taken a lot of screenshots here of what it looked like recently now if you're watching this recording or even some of these live screen shots that you're watching right now you're going to look at this and say gosh andy it doesn't look that way when i do it so there's a word for that and that word is the cloud actually it's two words so the cloud and the cloud changes every single day and i just want to let you know if you're not aware of that that's that's a true thing and if that bothers you if things changing bothers you then you may struggle with working with the cloud and i hate to see you struggle so what i would love to share with you the best advice i think i can share with you is find a way to kind of divorce that feeling that well it doesn't look like it did yesterday i had this experience a year or two back i did a seminar right here in my office in farmville virginia where i'm coming to you from now i did the seminar here around noon local time and then did some more work jumped in my car and i drove to richmond which is about 75 miles away and six hours later is when i got there and i did a presentation at a local user group and i am not making this up when i got to the same spot that i had been six hours earlier as i was walking through this exact same presentation something had changed so don't please don't let things changing bother you and please don't get upset when you see somebody's put a lot of time and effort into a blog post or vlog series or presentations or books and and you read them and go well it doesn't look exactly like my screen well figure it out because guess what it's going to change again you're going to have to figure it out then too so that's my advice when we first start with cicd we have to create what we see here on the screen so there's a lot of things on the screen here there's my organization um i founded enterprise data and analytics and ent dna that's our um that's the ent dna.com that's our url but we just use this enterprise dna or dna as an acronym for us um that's an organization and then you have to set up a project and we see a project here ssis samples so as we move through this the very first thing we need to do is create an account so you want to sign up for azure devops they have um they have free source control available here so with up to a team of five individuals so if you go to dev.azure.com as you see here at the bottom and you start for free and again you can do the source control portion of this and many other features you can do for free for some amount of time and there's options here and you want to read all through through all of the options and you want to figure out what's going to work best for you and for your team but like i said up to five people can use this for free that's hard to beat free i guess they could pay you to use it i don't know but go here and set up your account if you're not using this go do that today and if for no other reason than to just get your uh your hands on the tool here and see how this works um the very first thing i did was i created my organization and you can see ent dna over here and i didn't walk through that piece of it i'm going to let you do that and then after you've created your organization go ahead and create a new project now your organization may just be you it doesn't have to be anything fancy mine is a consulting company that i founded back in 2016. so you click this new project button and that's going to bring up the configure project or create new project and then you get to pick whether it's public or private and you get to set version control and work item process and that sort of stuff and you can see here i picked team foundation version control and and then scrum i i like both of those although i do use get quite a bit once you've created it you you end up with a little card here that gives you a lot of good information just an optional description that i put in there and the name and some people ask me andy why are you doing tfs everybody's using git and it's accurate and i get why you might say that but a lot of customers aren't using git some people invested pretty heavily in team foundation services locally and on-premises is what i mean by locally so because they did that some of them are unaware that azure devops offers this option for um people to use tfs and azure devops maybe you don't want to learn a whole new engine a whole new source controller version control engine like get and frankly get's not easy to learn it just isn't so many people have used tfs and they're still using it and they want a way to just continue to use that leverage their existing knowledge and that's something that i've helped organizations do and it's not a trivial move to the cloud but it can be done and again once you've got your stuff there you can then begin to leverage that uh that knowledge that your institution already has so after you've got your your um your engine configured for that and you can use get i use git most of the time i use tfs just for this presentation and a few other things you can click on uh visual studio you open visual studio and on tools you can click options and this is the very first thing that you want to do after you get your engine set up is uh you want to on in options you want to expand source controller you can just click on it and select plugin right there from the source control main page the general page there you can drop down the uh current source control plugin and in this case i'm picking tfs but note there's get setting right there vegas live once you've done that then you want to connect your visual studio environment to your tfs environment online azure devops so you do that by clicking team and manage connections there's some other ways to get to team explorer but this is a good way to do it at least initially and then drop down manage connections and click connect to a project the connect to a project window comes up and you want to go ahead and log in you want to pick your your um your hosted repository and i just i was able to log in using the microsoft login i skipped that step here but you can do that you know what that looks like and if you don't you will after you do this step and then navigate through the the dev.azure.com uh and then your organization and then there will be one or more projects here if you've created a new project out there at dev.azure.com then you click connect and once you've done that team explorer will show up and it'll let you see um depending on whether you pick git or tfs it'll look differently but you'll be able to see some things in common like pending changes um is a is is common to both i think it calls it something different and yeah i think it's just changes in in the get version of that but it's similar functionality it just goes about it in a different way and the engine is totally different under the hood so after you've after you've brought up team explorer you can now look at solution explorer and if you want you can grab a project or create a project at this point and i created one here called test ssis solution and it's got three ssis packages uh in it and these are very basic ssis packages uh just an aside here report and fail why in the world would i write an ssis package that's designed to fail well i do that because you want to test what happens when ssis fails you want that failure to be to be elegant you'd like for it to be managed in most cases at a bare minimum you wanted to tell you why it failed so that's a good reason to test for failures in fact most of my testing is about what happens when something unexpected or untoward happens you want to plan for that sort of stuff that is just good engineering report and succeed is pretty easy and that should work all the time the one we're going to be using most today is write a record and succeed and what that's going to do is it's going to it has a couple of variations and you'll see this when we get into the demos but essentially i wanted to truncate a table in the cloud and then execute and write a record into that table and then i want to run a test to see how many records are in that table and and the goal is to get to see if there's one record and only one record in that table if there's zero if there's more than one well that test is going to fail and we're going to use the fact that it fails as we go through here to actually see how we respond to failures as we go if i if i right click on the solution itself i can add that solution to source control and you can see right here how i do that in visual studio solution explorer once i've done that i have to pick where i'm going to send it so i'm going to send it to um to my i'm going to name the folder in source control the same name as my project here that's just a good idea you want to keep your code locally in sync with the code that's in source control and you also want to keep both of those in sync with what ends up in production if you don't it's just a recipe for disaster and i've seen people make this mistake so i'm going to warn you now don't do this or definitely consider whether or not to do this i'll say it that way what i often warn people is i'll say unless you have a compelling reason to do so and you you won't have a compelling reason for this i've seen people name things like test ssi solution underscore development when you're checking it into source control it's all development but when you start pulling in outsource control to have someone deploy it to a production server that's when it gets confusing who wants to deploy development code to a production server nobody should be raising their hand so just name this the name of whatever it is and then you can you can promote the code to different tiers ti ers tiers levels in your data integration life cycle management just um just be aware of that so once you've done that once you've got it uh you know written out there solution explorer will indicate and for tfs that the code has been stored in source control but it has not yet been checked in so it's a status change uh change between saying stored or ready or staged in get that's the word get used is it staged for this but then you want to you want to commit that change that's another get term so you just want to check it in when you're done there and you can right click again and click check in and when you do you can add a comment and you can see i took this screenshot this particular screenshot a little more than a year ago and i always do this i'll put in you know the date and then my name even though that's all stored in metadata and then i'll put in initial check-in i started doing this literally decades ago and this is just a check-in comment that is stuck with me you do not have to do your comments like this like i said the date and who checked it in those are going to be captured by metadata so you can shorten up your comments if you want do yourself a favor though leave a comment in get for instance it requires you to leave a check-in comment um and and or it may be configurable i'm not positive of that but i always the default is you need a check-in comment you want to do that for future you um right now y'all are y'all are young and you know your minds are just sharp as whips and stuff like that as i record this i'm 57 years old and i'm telling you when you get 57 years old your mind is going to slow down just a touch maybe your memory length is going to shorten just a bit and 18 months from now if you have to crack this code back open and look at one of these check-ins and figure out why you did what you did if that's blank or a space come on don't put a space if it's if it's blank though there's no helpful information in there you're going to really wish past you had taken 30 seconds and typed something in here even if it's initial check-in just something um and do that for yourself also do you like taking time off from work i do and when i do and i'm on vacation and my team is working on some code that i checked in 18 months ago i don't want them to have to guess of what i did or try and decipher what what i wrote here so i am actually pretty verbose when i write my comments in here about what i what i did exactly i include documentation in the code documentation using annotations on the control flow because when you open an ssis package the control flow is the first thing to display so using annotations to add documentation there documenting with your check-ins all of this is helpful and it gives everybody a fighting chance at uh figuring out you know exactly why you did what you did one last note about that i gave you two instances of where documentation i use more than two and often i copy and paste the same information in three different locations and if you're sitting there and your head's exploding and you're thinking andy that's just wasting time you're right and i'd be happy to refund all the digital paper that you're spending money on i'm kidding not only will i not refund it it doesn't cost anything take a little bit of time add a little bit of intention to your coding somebody somewhere maybe you will thank you later and why do i duplicate all of that why make people dig not everybody thinks like you do okay not everybody thinks like me thank goodness so why not make it easy for them not just for me okay somebody else is going to be reading this all right i'm going to kick that soapbox back under my desk here and move forward so after i've checked in code tfs puts these little locks beside all of my code here and you can see these little azure color blocks get it so so there they are and my code is now checked in in visual studio well let's take a look over in azure devops and let's look at at the repository so let's say i'm going to pop over here to my vm there we are and this is my my repos for this now when you look at this version of it you're like wow andy there's a lot more dtsx files in there and you're right i've run for some time run for some time and failed um there's write a record and succeed uh report a parameter and succeed that's that's a new one as well and these are in here because i've made changes to this project i actually made these changes for an upcoming book that's uh that's coming out it's one on um on building custom tasks it's a second edition of that book coming out next year and you can see i've made these changes at various times this was uh if i can sort actually by the this is the first changes if i do my last changes the last change i made was just a few weeks ago october 20th 2020 10 20 20 20. there's a date for you and this is what the repo looks like and that you know i can have many projects inside of my ssi samples here's another project completely it's called parent child demo and there's just a lot in here that i can look at so this is what the repo looks like inside of azure devops not much to that demo other than hey this is what it looks like so continuing um and oh i forgot to tell you this earlier i'm actually on i'm recorded andy i did this in the past um not too long in the past certainly not when it was due but in the past and and real life andy is on right now probably making fun of me and uh the chat window if there's one available i'm pretty sure there are and so i'm here if you have questions about anything as i'm presenting here and talking about it live andy is here to answer those questions how cool is that i actually like that a lot about virtual conferences that i can be on and heckle i mean answer any questions that attendees may have so our next talk is really about agents our next section here and we're going to look at how do you configure agents in azure devops and we're going to talk about self-hosted one of those agents the the reason that i talk about the self-hosted verse versus the um the cloud-hosted ones is when i started doing work with this actually i read in one of the places in documentation they said you need to use the wonders versions of these and vpc hosted or what have you and i was like what what do you mean so i tried that i checked the defaults and walked through it that way and it turned out it was taking about 10 or 12 or 15 minutes sometimes for the ages to pick up and start executing the ssis packages when it was time to do that and thinking back now you know kind of having gone through a lot more experience with what's going on what does it take to spin up um you know an agent in the cloud to do this well they've got to make a vm behind the scenes then they've got to install and configure everything on it in this case it's got to be able to run ssis that's not trivial so you know what 10 or 15 minutes really isn't bad when you think about it that way but what if i use a self-hosted windows agent well that thing's ready to go right away and that's that's what i discovered and i was really impressed with just how fast it took a couple seconds and we'll see this when we do the demos um demo after next it'll just start almost immediately um in order to configure this we're looking at we're looking at personal access tokens this allows us to connect our local uh instance here to something in the cloud and i got to tell you i'm absolutely thrilled by this um i was kind of shocked at how much this tickled me um so it turns out that i've got a virtual machine that's what we're running on it's named vdemo and i've got a self-hosted windows agent running on my vm and it's going to connect to azure devops in the cloud they're going to interact with each other and we'll see we'll see exactly how that works not only that but they made it really really easy i was super impressed with the setup for this so there's this uh blog post and you can get the um the the url down there at the bottom it's not a blog post it's an article and i'm sure it's been updated since august of 2019 and i'm it's most likely changed some just so you know and and you can look at how to set up these self-hosted agents here the one thing that i found that was different in that particular article was they showed the personal access tokens in a different place so that's why i've circled the little person with a gear here that's where they are that's where they actually are now you can find personal access tokens and listen if you run into something where the documentation or the screen or the video or whatever doesn't match what you're seeing click around right don't click a bunch of buttons and spend a lot of money but look around how you know it's just a touch of initiative here will take you a long way just a pinch once you click on personal access tokens if you're doing this for the very first time you're not going to see any tokens in there i've got about 50. so i say that i don't know maybe it's probably around 20 at least in there but you click this new token button when you do that creates a new token you'll see these blades is what they're called they'll slide out you pick um for self-hosted you want to use um the default agent pool here and you're going to want to look at custom defined and again that that article will walk you through all of the things you want to set you definitely want to do read and manage for agent pools and there's one on here that not shown on here that you also want to do i did a bad job of editing that image i can't remember what it is exactly but it allows you to do ssis executions that's what you're after so once that's done you want to now go into your organization settings so again at the very beginning right after you created your account you wanted to create your organization and set all that up so you can then add projects to it i don't believe you can add a project until you've set up an organization at least giving it a name and under organization settings you're going to find agent pools and you're going to want to click that and again you've got azure pipelines which you want to use that pool for if you're going to run in the cloud if you're going to run agents in the cloud the default pool is going to allow you to to configure that to run locally that's what i wanted to do so i clicked on default and then i clicked the new agent button when that appeared this i love i absolutely love this so this is what you need to know to set up an azure devops agent locally on on your windows machine but look you can see right here if you want to set one up on mac or in linux you just click the tab it's going to walk you right through this i'm doing x64 in windows so when you click download the agent it actually downloads a zip file and and it's got some it's got a name that has a a lot of good information in it in fact the name of the file it downloaded here was vsts dash agent-win-x64-2.158.0.0 that's the name of the file it downloaded now you're going to get a totally different file name now the numbers are going to be higher and that sort of stuff and then it's it passes in this dollar sign keyword pwd so what this is right here and there'll be little copy icons up here that you can click and copy this this is powershell and after you do the download don't don't download it and put it somewhere else go ahead and put it in your default directory which is dollar sign home backslash downloads you want to let that file download to there because this is a command and this is going to make a directory and it's going to decompress that file and it's going to send it into your agent director go make a directory name agent and it's going to it's going to unzip it and put the contents in there after that you're going to want to run the configure agent backslash config.command depending on how you're doing this you're going to see i did this differently i actually did this from dos not powershell after i got it unzipped and then run the run command this is a beautiful piece of the user experience right here i think they did a phenomenal job and i want to give whoever designed this cudas i don't know who built those this is a great design so after that you can see here's my after i run that powershell piece to do the unzip um here's me doing the config command and it starts out by saying where is your organization it's going to be https colon whackawacka dev.azure.com something that something is going to be your organization settings that's not the last one you're going to see there's a number of settings that it has to go through here just starts with that and it walks through the authentication type p-a-t is personal access token that's what we're using and then if you type that or just hit enter it's going to ask you about that personal access token now i should have told you this earlier but i don't show you the screenshot where it popped up the personal access token for me when i was configuring that but you want to copy that personal access token and save it somewhere you don't maybe you don't want to save it save it but maybe open notepad plus plus or notepad or whatever editor you like and paste that value in there because once you click ok it's gone forever now all is not lost if you get to here and you realize oh i've been following along with andy he didn't tell me that earlier enough in time you're absolutely right i didn't but you're not lost you can go back create another personal access token there's no extra charge and just set one up and now you can copy it and then paste it into this config part here it'll then try and connect to the server using your personal access token for authentication and it'll register the agent so you can see it starts prompting you after it connects and this usually only takes a couple seconds and then it asks you do you want to use the default what's the agent name and when i configured this i did it initially on my p51 laptop and that was the designation for an old aircraft called the mustang it was nicknamed a mustang it does these pieces where it starts looking for things that are you know all of the pieces it needs it needs to look at the tool capabilities that changes with different versions it needs to um add the agent and then test the connection to the agent and then it prompts for the work folder i just use underscore work and you'll see this when we go back to the vm um and then after the settings are saved it says do i want to run this agent as a service now i never do but you may want to um and then do i want to auto configure auto log on and running on on startup no that's kind of the same as running it as a service i don't need to do all that i'll just start it when i need it um and the next thing is we need to start the agent and we do that just by typing run.cmd this is me i'm setting in a command prompt here it scans for the tool capabilities it connects to the server and you'll know it's done when it pops up this uh listening for jobs piece here so let's go over here and run this demo so let's begin by taking a look at what's in this agent folder before we started and we can see there's a few folders there's that work folder it created there's a diagnostics folder here underscore diag and then there's the run and config commands so we are going to begin by just typing run and when we do our agent installed on a virtual machine running on a laptop is going to communicate with azure devops and now it's sitting there listening for jobs so that's really it not too exciting yet but hang on the excitement's coming so if if we return now to the powerpoint we can see our next step is to look at pipelines and pipelines are where we set up all of the magic that we want to happen now again this is where i kind of run up against the limit of what i'm going to share with you today and i want to reiterate that i'm not using artifacts i'm not using testing officially i'm using powershell to actually conduct manual tests but there's steps in here so i don't know whether to call them manual or not they are automated i mean i am saving them here but this is not the traditional way to do testing and then doing deployment jobs i'm using some new build and deploy tasks that are are put out they've been around for about a year now and the build ssis task and to deploy ssis task we're also going to take a minute to configure a variable for our password because i don't want to give you my password nothing personal i'm just that way and i hope you're cool with that so let's walk through how we would construct a new pipeline um over in the azure devops um list here for our menu rather for our ssis samples project we would pick pipelines and then we'd pick new and make a new build pipeline so we need to do this no matter what then it asks us where is our code in this particular version and we we've stored our code in tfs so it's team foundation version control it's looking for a source of our code and again we pick our source engine tfs and and then we um either type or search for the server path and and then click continue now server path is dollar sign slash ssis samples here when we click continue it asked do we want to use a template i'm going to start with an empty job here i want to show you how to build this from the ground up so first it asks me for my agent pool how are you going to set this up are you going to use the hosted azure pipelines which is the default or are you going to use private which is the default agent pool i'm using that default and the very first thing i'm going to do under variables is set up a pwd bar this is a my password variable when i configured that i mark it as sensitive so that it doesn't share with you and everybody my super secret password um when i configure my agent job's name i'm going to give it some some name that makes sense you're going to see we're going to look at about four agent jobs before we're finished here today and again i'm picking up my um my agent configuration from that screen next i want to build i want to add a new build ssis task so this is available from the gallery and microsoft has wrote these tasks themselves if you search for ssis you'll see this is official exercise build task from for ssis devops from microsoft now just click that add button and when i do it adds it to my steps this is one of the the ssis um the sorry this is the build ssis task in in my first pipeline here so as i'm walking through here we see that i'm going to build it i'm going to configure this to pick up my test ssis solution and write it to this output path this is a dollar sign build dot artifact staging directory here and we're going to see that again especially when we add this deploy task so when we start deploying this we see i'm naming it deploy ssis nothing fancy there but there's that build artifact staging directory and then i'm driving down into the ispac file location so the ispac is the is a deployment pack for integration services and really it's just a zip file but it has the packages and a couple of files in there to manage the deployment the destination type i can pick ssisdb if i'm going to an ssis catalog and i am but there are other options here as well and the name of my server is actually a cloud server sdademo.database.one and then i'm sending this into a destination path using sql server authentication and my login and there's that pwd bar right there if it exists already i'm going to overwrite it and do i want to continue if an error occurs no stop break on error please so if we look at the build execution i first want to review a couple of things that really um that have changed since that those original screenshots were set we see our agent is still running out here but if i pop into uh back into azure devops here and actually i first want to go over to azure data factory because this is where i've configured the um the the runtime that we're going to use this is an azure ssis integration runtime inside of adf this allows me to run packages either stored in an ssis catalog or an azure file share or and you know i can actually embed packages the binaries for the packages and in here there's a number of ways to do this that don't really apply to everything we're saying here but i'm going to use this because there's my sba demo database.one.net that is the um is the azure sql db where my ssis catalog lives and we as we can see it is running that's um there it is right there it's actually running so we'll we'll catch up with that in a minute so if we go back to our configuration here let's go look at our pipelines and we're going to start with this zero pipeline that's down at the bottom now and if i click on that it'll open up the kind of the the individual pipeline viewer and i can click on edit and when i open the editor here we can see this is my build task so if i wanted to add a new task i'm just going to click this plus here and you can see it's going to take me over to this window and if there's a number there's a whole lot of tasks in here azure ssi sorry azure devops task not ssis um and some of these are built by people in the community some of them are marketplace some you have to pay for a number of them are free and if i just type in ssis we can see um there's some there's been updates to these right so there's um different thing and this ssis catalog configuration that's relatively recent um that was released in 2020 so the ones that we're using is um we're going to use this ssis bill but we're using an older version of it just so you know so once we click the build ssis i just named it build ssis there's my project path and there's that output path and then again for the deploy ssis this is important that build.artifact staging directory and then where is it going and then the name of the location so i hadn't updated this from an earlier presentation and i apologize for that screenshot but this is now going to devops test i want to use sql server authentication there's my username and there's my my pwd var so if we go look we mentioned that let's go look at variables here and we can see that that variable is right there there it is encrypted all set up and encrypted so again back to the task back to deploy and that that way we're able to store that and preserve the the encryption of it overwrite it sure continue deployment when an error occurs no don't do that and there's a number of other options that we're just not going to dig into for the purposes of this demo now before we go any further i want to pop over here and look at what's in that catalog so to do this i'm choosing to use a a utility that i wrote and it's available for free ssis catalog browser and you can go to dilmsuite.com and download this for free and we can see that in my devops test folder here i do have a version of this project sitting out here already there's the test ssis project and if we open it up and look at the packages we can see there's all the the packages that are associated with that project in its current state if we look at devops prod here we see that there's there's one there already and we're going to want to overwrite that here in just a minute so before we go any farther i'm going to pause and go delete that project the magic of on-demand video editing here and now you can see that that that version of the devops prod is gone so definitely wanted to uh to move that out of the way and if we look here again we can see that that's where it's going to deploy this first version is going to deploy it to the the devops test folder now there's already a version out there but it's going to override that i'm sorry overwrite that and so we can do that build execution and the pipeline we're going to use to do this is this 0 and 0 build and deploy note that we can do that by manually cueing it here and that's what i'm going to do it asks me for information about this i'm just going to leave everything as the default and click run now when i do that it pops right over to the jobs part of this and if i click the jobs part i get to see this really cool looking console here and i absolutely love this and you'll note that that initialized job was the first thing up there that actually loaded the remainder of the steps and it's just walking through the deployment here we'll see success or failure if i had my email turned on we'd see that we'd hear a ding as i received an email when this job finishes and it would tell me whether it succeeded it looks like it did succeed i get nice green text and and that makes me happy so that's really cool what it did is it just redeployed out there to um you know to that folder that devops test folder so absolutely digging that that it does that and i'm going to pop back over to pipelines because we're going to get ready to do an additional surprise deployment here so yep there's more we're going to do we're going to add some test ssis execution and then test the results test evaluation and before we do that we're going to take a look at the package and see exactly what's going on here but there's a couple of pieces so in order to execute that particular ssis package i've i've created another free utility it's actually not only free but it's open source it's called the ssis framework community edition and you can get this out at d i l m suite.com as well just a little bit about how it works is you can build your own if you'd like what i did was i just did exactly what you see here inside of the the integration services catalogs node in ssms i navigated down to a package right clicked it and clicked execute and when i did um it brought up this execute package window it also allows me to script the execution and i had a you know one of the things that bothered me a little bit about running from the catalog is that in order to execute another package in the project the package had to be part of the project now it's actually possible to run a package inside of the catalog to run a package outside of the catalog in the file system but i consider that kind of jacking up the catalog and i don't want to mix and match like that either want to run everything in the file in the file system or everything in the ssis catalog and one of the limitations of running up another package a child package using the execute package task is that that package has to live in that same project so i didn't really like that either i had felt like i had no choice until i scripted this and i saw this pattern as old as computing this there's three steps here there's create execution which creates an intent to execute down here set execution parameter value for the logging level this is just one example of maybe many calls to to the store procedure where we would configure the intent to execute so up here we create the intent to execute we get an execution id out and note that when we configure the intent to execute we're passing that execution id once we're done with however many steps is here in the middle configuring we then execute and again all we do is pass that execution id and the retry account into it and it just works so what i did was i i said you know what what excited me about this is i wanted to run packages that lived in other projects and other folders in the same catalog and these uh this has all of that in there the package name folder name and project name so i just encapsulated that code into a stored procedure in a custom schema inside of ssisdb named execute catalog package and again you can go get this code it's free and open source go to dialmsuite.com and click on ssis framework community edition you can pick this right up i use that ssis framework that very same one it works in the cloud in azure ssis it works on premises it you know it's pretty handy code and i actually use that for an azure powershell step called uh run ssis package using the ssis framework so i call a single stored procedure one that i wrote and i pass it a single value and it'll run one package uh the value that i'm passing is the name of an ssis framework application which is one or more packages that it runs in a specific order so i can run one or i can run 10 or 100 packages just by passing one parameter or two to one stored proc here and then i i test it that's the next thing i do what am i testing well when we get into the demo i'll show you we're testing is there one value and only one value in our test table we want to write that record and and then pop right out of there um i just added the azure powershell objects here i went over and searched just like i searched for ssis i searched for azure powershell brought in two instances of that and then configured the uh powershell here and you can see um i'm connecting and then i actually give uh give this a little bit of time here right to uh to pause and wait and then i execute uh this using um again this um this is an execution of the um of the stored procedure or sorry this is an execution of the test selects the max date as date and account star and i'm checking to see if that record count that comes back is uh is not equal to one because if it's equal to one that's what i want that's success if it's not equal then i'm going to raise an error and uh and throw that out there the um last piece of this puzzle is i am going to enable continuous integration on on the deploy to test that's my next first step that's where i'm going to start then i'm going to configure the deploy to production pipeline and i'm going to enable a deploy to production trigger on that so i'm going to make it so when i first enable the continuous integration on deploy to test it's really easy you just go to triggers and you just check the box enable continuous integration save this and now part of my dream comes true okay i i'm able now to check in the code and when the code gets checked in when my ssis project gets checked in then it's going to enable continuous integration and it's going to run this deployed a test pipeline right here on a check in um after this i'm going to be able to some add you know and modify my deploy to production and if i add a build completion here and then i pick that deploy to test step when it finishes deploy to test which fires on check in then it's going to run this deployed in production and and finally it's going to um that's going to trigger another execution in production and a second test and in the project version of this as well so let's take a look at all of these moving parts here just to get a sense of of how it's going to work and if i again i'm doing this on check-in so let's look at the ssis package first i want to start by zooming in here how do i get one and only one record in there well i'm writing the value here and i'm just sticking in a date time um the truncate target table though this writes a value to the target table this truncates it first so what i need to do is introduce a change and here's the thing it's really easy to create a change in ssis you don't have to change anything i just opened up this execute sql task and i'm going to click ok and when i do that note i got a little asterisk here i didn't change anything i just clicked ok but it doesn't know that so it sees everything like that as a change so i'm going to call this my ci change and when i check this in what we're going to see is the the agent job will begin executing now we didn't look at that before but we're going to see it now so i'm going to zoom out and when i do the check in here we're going to pop back over to the window and so there was that zero build and deploy completed and succeeded look it's now going to see this other job i think oh no it's not i showed you what i'm going to do but i didn't do it let's go do it it'll go ahead and uh it actually won't do anything for us because we didn't uh we didn't set up the trigger so let's go to our pipeline and let's look at build and deploy let's go to edit and go to triggers so right now there's no triggers on here that's why it didn't do anything yes it checked the code in which was awesome but it didn't fire my my pipeline so now that i've got this checked if i just that's all all i have to do is check that box and save it and if i go now back to my pipelines let's go here and pipelines zoom out so we can get to this a little a little easier that's what we want to do on check in build and deploy to test if we go edit the second pipeline here we're going to be able on triggers to say do a build completion trigger so i'm going to add one and it pops this triggering build up and which one do i want to use let's go ahead and run that one do that one build and test scroll up a little bit and save that and i'm not going to give it a message here i'm just doing this for demo purposes finally i've already got this one configured i'm just going to show you on number three i've done exactly the same thing on triggers i just showed you i've got it set to run whenever number two fires so now we're ready to actually actually by the way it did exactly what i wanted it to do it did what i told it to take that back not what i wanted it to do so again we're going to trigger this um and go ahead and save it there's been a change and we're going to go um i'm going to name this one continue us integration test and check that in and now when we do and we pop over here we should see the number one job fire all there it goes so there's number one let's pop over to the pipelines and if we click on number one we should see a job running there it is continuous integration test we got a little uh progress bar running right there we click this and we click on the job and now we're going to see our console again kind of doing its thing except now when this finishes and hopefully it will finish and succeed it's okay if it doesn't finish or if it if it doesn't succeed we can see what's going on why would it fail we're actually going to rerun this and show you a failure what it would look like so it's working through and this takes some time but i'm again i just this makes me all giggly inside to see this console in a web page walking through these steps these pipeline uh very linear steps here this is what i wanted for data and ssis projects for 20 years i was a i was an mcsd before.net and i learned about test driven development back in the 90s when the years began with a one true story and i wanted to be able to do that as soon as i jumped the fence into business intelligence i wanted to be able to do that same thing for databases and database development and then ssis when i started working with that and i just i was a little shocked that we didn't have the same sort of functionality we did have some ways that we could do this but there were caveats and lots of them so i'm very excited to see tfs let me source control ssis back in the you know 2000s mid-2000s and then really excited too to see um to see this build automation which popped up about a year ago 2019. we go back into our pipelines now we see the deployed production is running we're going to go ahead and get onto the screen and then we're going to pop back over to agent and note that it's telling us wrong wrong button telling us that deploy to production is running and and number one build under quite a test completed with the success result so let's pop back in here that one just succeeded i start too fast they're running too fast today that's not a valid complaint there's our continuous integration test there's our v demo job for that one and we see it's initializing and you you got to catch it then that initialized job step loads the rest of the steps as they move through here we did the deployment already and if we go look at catalog browser we can see what it was showing us before was there was no project in there now if we click refresh and expand we'll see that yep in prod there has been a project deployed and it is our test ssis project and you know we're running right now we're running write a record and succeed again we're going to test it one more time and um what it's doing is it's actually cleaning up some production framework metadata and then it's going to deploy a fresh copy shouldn't take long for that and then it's going to execute the production file in it's actually going to execute this in private so kind of cool that it does that so it it doesn't retest it in production although it could it's not hard to add those couple steps um to to do that i think it's only one step that azure powershell that would rerun that test and then we get a success of failure on the execution in production but the idea here again is just to get your your feet wet to get you started in um you know in azure devops with ssis not exhausting the topic by any means but i love being able to show you all this free functionality that that just works in here and once it's executed um we'll be able to see whether it's succeeded or failed i'm gonna zoom out here again go back to um the console here and we should be able to see the post job checkout succeed and then we'll see this succeeded there it is and again if i had email running you'd hear the you'd hear the message pop up from me getting a um you're getting another email telling me hey you're you just succeeded number three there so you can see them they're in order right now on the pipelines and i could look at all of the runs or just individual runs these are the recent runs that uh that it just did the recent collection of ones it shows me the last set of runs the last one for each each pipeline that i executed here let's do that failure while we're here let's pop over and how do we get it to fail well if we disable the truncate and save this um then it should fail we'll leave everything just like it was but it should fail as it runs through the the process here and it should fail on that deploy to test the number one job so now we just see that agent fired it off if we go back to pipelines here and go to deploy and test there's my assert fail that i just created and we should see when it executes the test suite this is what should fail and it should give us a good message saying that i think it actually counts the number of rows and tells us there are two records found in there um and i want it to fail this is this is intentional you want to see that it actually does its failing just like i designed it to so this will be a test of the test maybe is that meta testing i'm not sure but we'll check this out and uh once we're done we will uh we'll get to see what the test results were and i'll run it one more time just to clean things up so again just i find this fascinating we can communicate with these asians and talk here locally on a vm of all places the agents running on the vm absolutely love this again live andy's here if you have questions you can ask them anytime you can ask questions at the end of course we're allotting some time at the end for that as well but please type your questions in if you have them now let's see we are checking along here again supposed to fail and we'll see that um not only in the um you know in the report here when it's done evaluating it's running a query and it's going to bring back two records instead of one and there it is there are two records found um and it pops out so if we um if we look now if we pop back to pipelines we shouldn't see any running and i'm going to refresh a couple of times it shouldn't be running number two because that number one did not successfully build there's an error message that's uh being emailed to me and if we zoom in here to on the agent it tells me it failed so again how do i clean this up i'll do this while we're zooming out and getting ready to take any questions that you have and for this one i'm going to reserve success did i spell that right i did not there we go all right now we're going to check in and that checked in successfully we should see our agent job running there it goes it just fired off and if we go back to build and deploy and test we should see one running a search us asset not a search typing in demos but there we go and this while this is running we'll just zoom out here and be happy to take any of your questions at all so i'm just going to let this uh this roll on uh while we're here i want to say thanks to data platform summit 2020 for giving me the opportunity to present again it is always an honor to present and i also want to let you know that if i can help in any way um my email address is andy.leonard at ent dna.com and you can learn more at my blog andyliner.blog there's actually a learn more link there on andy leonard dot blog so i again if i can help i love helping in any way that i can um so it's going to finish up this execute test suite this is where we are and we should see this job finished finish move this over just a little bit so we can see the next job kick off yeah we're not going to be able to see all of that but we can see some of it see if we do it like that that's better we should see that success pop up down here i know it's hard to see and i apologize for that there we go succeeded and then it should kick off number two that's the next one we'll see unless i turned off the integration yep there it goes there's number two and you can see it down here it's going as well all right um that's really all i have um as as i said i want a special thanks to microsoft for supporting data platform geeks and sql server geeks um these are community initiatives i'd also like to acknowledge the teams behind us they are astounding they do a lot of work it's volunteer they put a lot of hours into this and i think they do a fantastic job i really appreciate them and i think you should too as i said it is always an honor to present thank you [Music] [Music] you