So hello everyone and welcome to this video In this video, we will try to understand how to do SSIS project deployment or how to deploy SSIS project into production server. So in order to understand deployment in SSIS, what I'll do is let me go and create a very simple SSIS package, which will copy files from one folder to another folder. Let us try to keep this program as simple as possible because our main concentration here is to understand deployment and not write some complex program. Okay, so I'm going to go and create a very simple package which will go and copy files from this location one folder. So if you see this in my C drive, I have a location one folder. And in this location one folder, I have a text file called as some text file, right?

So I'm going to go and create a package which will copy this some text file from location one to location two. Okay? So in order to do that, so here's a very simple SSIS project. I have created If you see the solution explorer, you can see that this package or this program has a very simple file here called as file copy or DTS x file. So in this file, let us go ahead and put the components which will copy the file from location one to location two. Now, in SSIS, to copy the file from one location to another location, we have a very great component here called as the file system task.

This file system task component, you know, helps us to create files, copy files, delete files, right? So I'm going to go and drag and drop this file system task component on the DTS x file here. So let us go into this file system task component here. Let us right click, say Edit. So let us go ahead and first create a source connection. So we want to copy from location one to location two, right?

So I'm going to say OK, a new connection and this connection would be a file connection, right? So I'm going to say yes, existing file. Which is located in the C drive in the location one folder. So this is this some text file and I would like to copy this some text file from location one to the destination folder so I'm going to go and create a destination here as well. So I'll say the file is not existing please create a file, right? So I'll say the destination is location two.

And the file name we will give at this moment as destination dot txt. So so that will be the file which will get created okay. So we can see here I have created so if you just go to the Connection Manager tab here, so this is actually my source. Let me go and put a proper name here you can see it has put the names with the text filenames which does not look good. So I'm going to say this is my source connection. And this is my destination connection.

Right? Good. So we have this file system task here, which is copying from the source that is from C program files c Location One I'm sorry, C location one to the destination c location to great. Let us go and run this program to just ensure that everything is running. So great, you can see that the program has run successfully, let us go and just check. You can see that in the location two, he has created the destination txt file as well.

And if I open it, everything is there, right? Good. So I'm going to go and delete this destination file at this moment. So this program is working. Good. So now we would like to go and deploy this program into SSIS.

Right or we would like to do deployment of this program into a production server. Now before we do the deployment on the server, let us first try to understand What things gets deployed, what things actually gets deployed onto the production server. So, in order to understand that, we have to understand you know, how the solution of SSIS project is structured. So, you can see here the solution of the SSIS project is structured in this hierarchy at the top we have a solution file and the solution file will have lot of projects file for example, you can see that I have a solution file here called as file copy program. And in that we have one solution call this file copy program again right. And then I can have one more project here must be file copy program to or must be some other program right?

So you have solution, and then solution has projects and then project has packages. Again, I'll repeat, we have solution. solution has projects and projects has packages and every project has parameters. You can see there is a file here called as project dot parents solution than projects and then Project has packages that is dot DTS x phi and parameters. So, this is how your SSIS project structure looks like right. So you have solution project project has packages, which is nothing but DTS x file and every project has params file.

So solution can have multiple projects as well you can see this is one project and then there is second project, but every project will have the same structure that is package and parameters. Now you can deploy SSIS packages or SSIS projects in production server in two ways. One is termed as the project deployment mode. And the second one is termed as the package deployment model. Now, the project deployment you know it has been introduced from SSIS 2012. And it is the most preferred way of doing deployment.

Well, package deployment was there till 2008. And I will talk about the differences between both of them in the later part of the video. But for now, my main focus will be on project deployment. But before I move ahead, let me explain you the difference Between project deployment and package deployment in Project deployment mode, the complete project gets deployed in one go. Remember I said that project has packages. So for example, you can have one project and it can have a lot of DTS X Files, right?

So you would like to go and probably deploy all these DTS files in one go, then project deployment is the way to go. But let's say later on later on, when you're done the deployment, some developers go and they modify one or two package files, then you don't want to do the full deployment again, right? So at the time, you can go and do the package deployment. That means you can go and take individual DTS X Files and deploy them. So project deployment means you take the complete project and deploy it. While package deployment means you can you can deploy individual DTS x file.

So in this video, our main concentration would be to understand project deployment more because, you know, that is a new way of deploying and that is a proper way of deploying as well now SSIS projects you know, can be deployed. in two different locations, you know, depending on what you prefer, the first one is inside SQL Server. So if you're doing project deployment, then you can go and deploy your complete SSIS project into SQL Server, you know, where we have a nice database here called as the SSIS dB. So if you're doing project deployment, you can go and deploy your complete project one is into SQL Server. The other one is inside the SSIS services, you know, you can go and deploy your project either into ms DB or you can go and deploy it either into your file system. So there are three places you know where you can go and deploy your SSIS project depending on what your preference is.

First one is into SQL server that is into SSIS dB. The second one is into file system and the last one is into ms dB. So let me quickly go and switch back to my project here. So very quickly, if you see here, you can see that I have a SQL Server and I have SSIS here, right? So the first thing is you can, if you're doing project deployment, you can go and deploy your project into this SSIS DB if you're doing project deployment, or else, you can go and deploy it here, inside either ms DB or either into file system. So again, there are three places you know where you can go and deploy your SSIS project into SQL Server SSIS DB into ms DB or into file system.

Also, I would like to make one important point here. You can do package deployment, as well as file deployment into SSIS dB. That means into SQL Server, but you can only do package deployment into SQL Server integration services that is in file system and Ms dB. So in file system and Ms dB, you can only do package deployment again repeat in a ms DB m file system, you can only do package deployment. means you can deploy at the file level at DTS x level. While in SQL Server, you can do both you can do project deployment, that means you can deploy all the packages in one go.

Or you can also go and import package by package into the deployment. In this video, we will be more concentrating on project deployment, because on SSIS 2012, the most preferred way of doing deploying is at the project level. Okay. So, let me let us go and open our solution yours. So you can see that I have opened the Visual Studio here, right? And let us go and rebuild our full solution once so let us go into a rebuild.

So everything built everything nice right. Now to do project deployment. What SSIS does is it creates a set of file. So if you go to the right click on the project, or you can right click on the solution if you wish. And over here you can see there's a menu saying open folder in Windows Explorer. So let's do that and let us go inside this file copy program folder.

And let us go in the end directory. And let us go into inside this development. Now you can see over here there is one file created here, if you see very quickly. So, so this file extension is integration services project deployment file. And if you go and see the extension of this file, so if you right click on this and if you go to properties here, the extension of this file is a spec file, you can see this.is pack name here, right. So, this is nothing but your set of file.

So, if you take this file, and if you run it, it will actually go and start installing your packages, you know, inside SQL Server. So, let me go ahead and double click and let me start installing the package. So I'm going to go and double click this is Pac file. So wherever you want to do deployment on whichever production server or whatever server you want to do deployment, copy the setup file, this dot spec extension, take it over there and and run it. Once you run it, you will see that he will actually start up a deployment with out here. So let us do a next.

The next thing he says is that so you want to do project deployment or you want to do individual file deployment, right means a catalog deployment. So I'll say no, I want to do project deployment. And yes, the, the spec file is this. So take whatever is there in this respect file and deploy it on my server. I'll do next again, he goes, and he validates the project, right. Now the next thing is we have to go and browse to our SQL Server.

Remember, we are going to go and install into SQL Server first. So when I say I want to install into SQL Server, I mean, I want to install into this SSIS DB over here, right? So I'm going to go and give the SQL Server Name here. So that is this. And I'll say Okay, so once you specify the server name, the next thing what you need to specify is the path. Now you must be wondering, what is it what exactly is this path here?

Because the server name is more than enough, you know, and probably this DTS x file should just be get deployed into a database, right? So why this path? Remember, at the end of the day, what we deploy in SSIS is a dot DTS x file. So in other words, if you look at the least level of deployment of the least unit of deployment, I will say it is the DTS x file. So at the end of the day, if it is a file, then it needs a folder. Right?

So what I'll do is let us go back to our SQL Server here. And let us create a folder here. So I'm going to go and create a folder here you can see I have right clicked on that SSIS dB. And I'll create a folder saying my package or something. So I'll say this is my package, right? So you can see that my package is created.

Now inside this package, we will go and deploy our projects right. So the folder is created. So let us go back again here and I will say browse and I will say yes, go and install in this folder, which I recently created and say okay, I'll do a next this is all fine and deploy. So you can see everything is done here it has loaded the project connected to the server, you know did all the verification and then it has deployed the project. So if I close this let me quickly close this and let me do a refresh here. So we'll go and refresh over here and I should see my project so my project name remember it is file copy program.

And inside that you can see my package JSON file copy dot DTS x, right Great. So now the next thing is you like to go and run the package, but before you go and run or execute the package you like to go and configure the package right Miss In other words, for example, if you remember we this project what it does actually it actually copies from one location to another location, right? So probably you'd like to go and configure the location saying that okay, it is not see location one it is probably see location two, right, so, let's find configure it. So let's right click on it, let's click on Configure. So you can see this is our UI, which will help us to do configuration. The first thing what you'd like to configure is the connection managers.

Remember, we had two connection managers created one, which actually has the destination file, right location, and the other one which has the source file location. So in case you would like to go and configure this for example, for now, you can see the location is C Location One some text, right the source location is C location wants some text or txt and the destination by default DC location to right. What I'll do is let us go and change this destination, right so let me go back here. And let me create one more folder here. And I'll term I'll, I'll name that folder as location three. Okay, so what we'll do is you know, rather than creating, copying the file in location to either To go and change this configuration to location three, so I'm going to go and click on this.

And I'll say don't use the default value from the package, edit the value. So the value is now Ctrl C Ctrl V, it is location three, I'll say OK. And I'd say, okay, so you can see, you can go and change your configuration, you know, in your production server as well. So remember, to configure, you need to go and right click on your package very quickly, you need to go and right click on a package and say, configure, right. So with that, you know, you can always go and change your connection managers default value. So what will happen now is it will actually take this file from C Location One some text dot txt, and copy it to this destination here c location three destination dot txt, right. So let's go and run this package.

So to run this package, right click Execute. And before running the package if you wish you can again Go and change this value here as well right so, in case you want to go and make changes while running, but remember when you make changes while running right you know these values are not stored. So, when you make changes to these connection managers during configure the values are actually stored in a production server right I will talk about this parameters and advanced thing later on. So, leave that thing at this moment. And let me just say okay, and I hope that the file will be copied to location three, right so you can see the package has executed and once your package has executed, it also provides you a report or report which will help you to view if your package has executed successfully or if it has errors in case it has errors.

It will give you a display here as well. So I'm going to say yes, I want to see the report also. So I have said yes here. So you can see that there my package is executed and you can see the report as well saying succeeded, right. So let us quickly go to our location three year to just check if everything has been copied. Here.

You can see in my seat Location three, destination text file has been copied. Great. So this is great. I'm going to delete this file for now. This is great. But now I'd like to do the following, I would like to go and run this package as a task, as a task which keeps running continuously must be every hour or every week and copy the file from that folder source folder to the destination folder.

So in order to go and create a task, a background running tasks, what you can do is you can give this package to your SQL Server agent. So let me go and start my SQL Server agent. I'm going to go right click and start this SQL Server agent. In case you're new to SQL Server agent, right what my suggestion is to go and see the video in SQL Server questions and answers video series where we explained what exactly SQL Server agent does. But for now, you can think about this SQL Server agent actually, you know, runs background jobs continuously. You know, like a task, you know, So that, you know, you don't have to do things manually, right?

So what we have to do here now is we have to go and create a job here. So I've got to go and create a job, which will actually go and run this package file copy continuously. So let us right click on this jobs folder here, say new job. So the first thing is we need to go and specify a name year. So I'll say yes, the name is fine copy job. Right?

And it will run under administrators. So that is fine. It is enabled, that is also fine. steps. So I'll say basically, so the step means exactly what you want to run. So I'll say okay, I clicked on new step here.

You can see I went here, and I clicked on this new step here. And so this step is actually nothing but what exactly you want to run. So I want to say I want to run SSIS package can see our SQL Server integration service package. So I want to run a package. And I'll say this is a file copy package. So the next thing he asks is basically so where exactly is this package stored.

So I'll say my package is stored currently, you can see, I have a server name here. So let me go and click on this drop down here. So I'm going to have clicked on the drop down. So my package is stored in this Vin hyphens u, q, whatever it is, right? And which is the package the package which we have to run as a job as you can see that I've clicked on this dot dot here. And from here, I will go and browse to that folder.

And inside that folder, I will go and specify my DTS x file. Right. So you can see in the steps I've specified what has to be done as the job I'll say, Okay, so that is done. The next thing The last thing we need to define is the shedule. So you want to run every week you want to run every day how it is, right, so I'll say, schedule 123 whatever. So the name is given.

Now the next thing is we need to go and specify the frequency by which this job will run, right? So if you see my clock today it is actually Monday. Right? So I'll say okay, this has to run, it is actually Monday, and it is 257. So what we'll do is let's go and run this on every Monday at 259. Okay, so I'm gonna say Monday here, occurs every minute.

Every one minute, right? at 2:59pm. Okay, so we have to do this very quickly, so that we sync up with a clock. Right? And then say, I don't need Sunday, only on Monday. Okay.

So okay. Right. So there it is. You can see my clock is still on 257 here. So what I'll do is let us go and see the view history. So in this view history, you can see that if the job has ran or not, so I would do a refresh here right.

And let us go and start my clock here as well. So you can see now it is still 258. Here you can see my clock here, it is still 258 here, right? So after one minute, right what should happen is I should get an entry here, right? Or I should or the file should get copied someone's back in, does my location three and this is my clock here, which is running. So let us see.

So as soon as 259 happens. So, we have more 30 seconds to go, breathe in, breathe out, you can kill them. So, this is completely live here, right, so, more 30 seconds to go. So, as soon as 259 happens, and it is Monday, that job will run. And if you go and copy that file into this location three folder y location three, remember, you know, we had configured that this package should run on location three, we had changed the configuration of the Connection Manager, right. So, 4849 50 and more 10 seconds.

I know it's a bit boring, so But even I'm interested to watch if this happens or not. So that it is, where is my fine? Must be? I should do a refresh here. Refresh. Well, oh, Derek, as you can see, good.

And also, if you go here, you will get a report saying that yes, this file copy job actually ran at 2:59pm. Great, right? So in case you want to go and run this package, as a job as a background job, you can go and create a job into this SQL Server agent here and keep it running continuously, right? So at this point, I'll just go and delete this thing from here because it's going to probably go and hang my computer, right? So right Great. Now, also, one of the things you know you'd like to do with your setup is or with the deployment is parameterize.

It means for example, you know, when we did the configuration right, when we actually On the configuration, you can see that there was something here called as parameters. So what you can also do is, you can go and create parameters by using this project parents. So you can go and create parameters and pass value to those parameters. And those parameters will internally go and set values to variables or set values to connections, right. For example, I can go to this project or parents here, and I can, let's say I will go and create a parameter here. So I'm going to go and add a parameter, and this parameter would be a string.

So what I'll do is I'll add a parameter which is a string and name this parameter as destination, destination path, right. And I'll save this now. So by default, what I'll do is I will configure this parameter As it has to go to see location three, right? And desktop txt, okay, so by default, this is the value of this parameter, right, and this parameter value, I will set it to my connection here. So I'll go to my DTS x file here. So let's say I can go to my destination here.

So to my destination, I will say that go and configure this connection string, you can see that there we have a connection string here. So let us go and configure this connection string by using that parameter. So I can go and say here that this connection string value will come from that parameter. Very important step, again and show I've clicked on the connection string, I've clicked on this expression, and I'm dragging and dropping this parameters over here. Great. So you can see I've created a parameter and I'm have attached that parameter to my destination path.

Okay? Also one more important point here you can keep, you can create parameter at the project level. So if you remember this parameter I have created by double clicking on this project dot params. Or you can create parameter at the package level that means at your DTS x file level. So you can have parameters, you know, on different levels, right. So when we build the solution, again, because I made some changes here, so in case you want to go and deploy those changes, you have to go and re deploy your project means rerun your setup.

So let's go to File, copy program, let's go to development, let's run this is Pac file setup here again, and that is next. Next. So all these are this is common. You know, this we already talked about in the previous part of this video, and I will deploy this thing, same on this thing here. So he will he will give me a give me a warning here saying that a project with this Name already exists, do you want to continue? Absolutely, I will just go and override it and say deploy.

So now let us go back to our project here. Now remember the parameter what we created is at a solution level or at a project level, right? So in other words, you know, that parameter will apply to all the DTS X Files, okay? So if you see here, so if, for example, if you go and click on this single DTS x file here, and if you say configure, you won't find that parameters here. Why because it is not at the package level. Remember, when I say package level, that means DTS x file level, right?

But it is at this level at the project level. So if you go and if you say configure here, you can see that inside this Parameters tab, you know, this file is here. So for example, now, let's say I will say that I don't know if you wish, you can again, go and configure from here as well. So we can go and configure it here and it will get applied to all your packages, right. So in case you want to go and Create parameters, you know, so you have to go to the project or parents the file and go and add a parameter, you can create parameters at two levels, as I've said, one is at this project level, right or you can create it at a package level. So again, this is one more important thing you know, which will help you to ease your deployment as well as it will help you to ease your project execution on production environment.

But now, let us say that you want to go and parameterize all your projects, which are inside this package. So if you see here, currently, I have the file copy program and let's see I have one more project inside this that's a file copy program to write what if I were to go and parameterize you know, some kind of a global thing to all my project inside a package right. So that means what if I want to go and set one global variable or a global parameter which is which can be applied to this full package here? So for that we can go and create something called as the environmental variables. So what you can do is you can go and create an environment here. So let's say this environment, I'll say, it belongs to my package, okay?

And inside this environment, I can go and create a variable. So for example, I can go to properties here. And I can create a variable, let's say, let's say destination path. Okay? So destination. This is a destination variable, which is of type string, and the value is C, location three, slash, let's say, global dot txt file.

Okay. Let's see. Okay, so now you can go and configure literally anything. What do you want, for example, I can go here and I can say that attach these parameters to my environmental variable. You can see here, I'll say use my invoice. variable that is destination which I've recently created and apply it to this parameters which I had created.

Remember the destination path here. And in case you want you can always go and even configure at the, how do you see at the, at the package level as well or I will say at the Connection Manager as well you can go and set your environmental variables. So at this one what I've done is I have set the environmental variable destination to my file copy program, which is a parameter right? So I'll say OK, here so what will happen is if you remember, this environmental variable is actually pointing towards see location three global dot txt file here. So if I run this if I run this package now, in my location folder, the file name will be global dot txt. If I go to my location three, you can see now it has created global dot txt right.

So in other words, it has taken the value from the environmental variables. So in case You want to go and set parameters at the at the project level, right you can use your environment your parameters. And in case you want to go and apply at a global level at at multiple project level, if you want to go and set value, you can create environmental variables. So very quickly, again, just revising what I said, in case you want to go and set parameters at the project level. When I say project level means to all your DTS x file, then you should use this project dot params, which had recently shown this one, right. And in case you want to, if you have multiple project for example, you have file copy program, then you have five per copy program to write.

If you want to go and set global variables at the project level, then you should use environments. Now if you remember, I said that you can do project deployment or you can do package deployment. Now let's say that you have deployed the project and let's say that later on Some changes were made only in one of the fights. So for example, at this moment, we have only one file here. But let's say we had some two three DTS x file. And some developer has made changes only to one DTS x file.

So what if you want to take that individual DTS x file and deploy it so what you can do is in that case, you can always go to your SQL Server here and you can click on this project node here and say import packages. So if you say import packages here, you will see that you can go here you can see that okay, where is this package at this moment, you can go and browse that folder and import that package. Okay, so I will not show this for the steps here. But this is very easy. You can see there's a wizard here and you can go and import single DTS X Files if you wish, right. So this is one more important thing in case you know, you load your hundred or 200 DTS x file in one go by use by by using project deployment.

And later on when you want to go and send individual changes, then you can Go and take individual DTS X Files and and and deploy them into your production environment. Now when I initially started this video, I said that, you know, there are two ways of deploying. One is you can deploy at the project level, that means you can do project deployment or you can do package deployment. And by default in SSIS, 2012, it is project deployment. So from SSIS 2012 onwards, it is only project deployment. But in case in case you are ever interested to go back to package deployment for very for various reasons, you can see that there is a menu here sync convert to package deployment.

So, you can always go and convert back to package deployment, and then deploy at package level if you wish. And also, you know, as I've said previously, you can always go and deploy not only in SQL Server, you can go and deploy on integration services also. So, in case you want to go and deploy inside integration services, then you have two options here. One is you can deploy it as File System and others you can deploy inside this ms DB here. So for example, if I want to go and deploy inside ms dB, I can right click, I can say import package, I can go wherever that package is, for example, let us try to give this package only. So remember, when we said package deployment it is at the package level, so you have to give the folder where your dot DTS x file is.

So, I'm going to say your Yes, my package path is this, right? And I'll say, okay, there is some error here. Okay, let me see browse. There it is to select the package and see okay, so let me do a refresh. And you can see that there is this file copy package here, I can go and execute this package, right so I can right click, and I can run this package if I wish. In the same way.

If I want, I can go and delete Inside the file system also. So you are also have to say import package, give the package name, and it will get deployed here. So I will not be going inside file deployment more, in other words package deployment at this moment because project deployment is a new way to go. And I and I think that it is more better way to go right. So quickly, let's revise you know what we learned in this video. So first thing, the structure of SSIS project is solution project and then packages and parents.

You can do deployment in two ways. One is you can do deployment at the project level, or you can do deployment at the package level, you can deploy in three locations. One is you can deploy inside SQL Server, which is your SSIS dB, or you can go and deploy inside your SSIS integration services. That is Ms DB or file system. You can configure your project by using the project parents, or if you want, you can give global configuration you know by using the environmental variables, and if you ever want to go and run it as a job, you can use the SQL Server agent So I hope that you enjoyed this video. In this video we were trying to understand how to do SSI as deployment.

Thank you so much.