SQl Server - Setting up SSIS manually

okay next thing I want to show you is how to do an ssis package the reason that you use an ssis package is to uh uh maybe have a data import or data export that's going to happen on a regular basis and uh that that's the main reason that you use a siiz package but it also can have error handling routines it can be scheduled there's other steps that you can do within a SSI package uh so in this case we're not going to use the SQL Server management Studio we're going to minimize that and you're going to go into the SQL Server folder uh you might have to pick in the start menu but I move M to my desktop I want to double click on Microsoft SQL server and then you're actually going to go into the SQL Server business intelligence development studio uh this is a way to make the ssis package manually but I want to show you how we can also make it uh in a more automatic fashion but it's important to know how to do this as well uh you're going to start a new package so I'm going to click on file new uh project and the one that you want to use is an integrated Services project this screen is going to look a little bit unusual to you and uh basically we're going to build this thing now what you do is uh you open up your toolbox over here on the right hand side and even though many people use the ssis for uh data import or data migration backups these kind of things look at all the different things that you can do uh within your um within your ssis you can have four Loop containers which would be like a looping sequence active X scripts uh bulk insert data flow uh so we can do execute SQL execute process means uh like a uh a Dos command and file system task is like you know copying and and deleting files FTP task uh so a transfer database I mean there's so many things you can do within the ssis package right here you can even do uh backups rebuilds uh uh execute T tsql statements so we're going to do a data flow the data flow really basically means that we're going to do import export so we're going to click on the the data F task and bring that into our uh main screen here now your ssis packager can have many of these and uh you can decide what's going to come first you can decide the order of that and all of that uh let's go over to the data flow uh now you're actually going to use the toolbox again so make sure you you realized that the toolbox is out there and uh for the data flow we we need a data flow source so let's say you're going to go from Excel or a flat file or an odbc data source uh let's go back into that let's pick on an Excel source and just drag it right in there okay uh now there's additional information we have to provide for that I'll do that just in a second I want to get back to my toolbox of course with the the data flow we need a a a source and need a destination the destination is going to be your SQL Server typically or we could go the other way but in this case we're going to go from Excel into SQL Server I'm going to move that into our into our job as well all right so let's see how we're going to get specific with this uh you're going to right click on the Excel source and you're going to pick on edit so then we can start to uh get more specific in other words what Excel file do we want to use so in this case uh we're going to click on new and now you're going to pick an actual Excel file I want to pick on browse and we're going to go back into the folder that was supplied with the class uh so I'm going to pick on desktop and the SQL class folder and I'm going to use Excel data too and you can see we can use different Excel formats you probably would either use Excel 97-2003 or maybe Excel 2007 and if your first row of your Excel spreadsheet does continue your column means you want to specify that and then within that workbook which sheet do you want to use of course and then this would show you the sheets I only have one sheet in that workbook but otherwise would show you all the different sheets so that's important to pick that uh now The Columns of the actual fields in the Excel spreadsheet those are fine you can see it's going to make the you know we can change the different names if we needed to but those names are fine and then the error output you can uh you can use this if to make like an an error file although that's not necessary in this case we're going to click on okay so you just establish your your Excel source now we're going to establish the SQL Server Source uh the destination I me I'm going to right click on that and then click on edit uh so in this case the component has no available input column so uh we're going to say no here for a second actually I want to connect these I want to pick on the screen arrow and connect it down to the SQL server and that'll be better so now let's go ahead and right click on that and we'll click on edit and you can see it's going to work out better that other any of that connection so uh for the now we're specifying the SQL Server so you're going to pick on new and you'll click on new again and you're going to get your server name if the pull down doesn't work perhaps you could just copy and paste it into the screen or in this case it did work so we'll pick on that we'll use the SQL Server Authentication [Music] type in your username and password of course if you think that you're going to do this over and over again you would save your password and then we pick the database that we want to copy it to uh we'll click on okay or you can test your connection if you like to and the connection is fine we'll click on okay uh so this will this is the SQL Server that looks good to me going to click on okay now uh this is asking is which table or view that you want to copy into so if you have an available table in that database that's fine in this case we're actually going to make a new table so I'll cck on new and uh now it actually is going to create a table uh using the same field names from the Excel spreadsheet you see where it says a SQL Server destination well I want to give that a better name and I'm just going to call this one Excel import and I'm going to click an okay uh we'll do a preview and we'll click on close there uh now you make sure that the mappings are going to map from the Excel spreadsheet into your SQL server and you can see have a one to one corresponding Stu we're going to click on okay and now we set up that uh the data flow so if we go back to the control flow the whole ssis package can contain many of these steps as many as you wanted to and then you would edit those kind of in a similar way to what we just did uh now at that point um once you have all those in there you might even just have one step or have as many as you wanted to uh we're going to pick on event handlers now what this means is if the job goes bad or if um if a step goes bad we can certainly manage that so let's pick on uh the package otherwise you can pick on um the different elements there and then these are different warnings that or different events that you would handle uh so we'll pick on on error all right and then uh so what do you want to do when it goes into error so then you would add uh some more of these control flows maybe you want to send an email or you know any of these different things uh based on that error and I want to send the email task and I'm going to right click on that click on edit uh so at this point uh I'm going to pick on mail and then you can do um if you have a SMTP connection you would choose that maybe we'll do a new connection there and you're setting up this uh step as we can see now this is if you have a mail server on your computer uh separate you know SMTP server this case we'll click on cancel um the from [Music] uh we'll say um type in the email [Music] address for the two we'll use an email address all right so we're setting that up of course uh we'll click on okay so when this thing is an error then the the email task will kick in and then we can add other uh types of errors as well and other kind of events that we would handle all right so um the whole point of this is to do maybe uh import exports and then there's lots of other steps that you can do Under the control flow and put together the entire package and we can do the event handlers all right when you're done with this package uh then we can go ahead and uh close save that close it uh we can save it we can give it a good name we'll say uh yes there and now you just made an ssiz package uh in a in a couple steps from now we're going to see where that fits in where we can schedule that and do different things with that