SQL Server Integration Services (SSIS) Part 1 - Getting Started

welcome to this wise aisle tutorial on getting started with sequel server integration services here's what you're learning the tutorial I'll begin by looking using Visual Studio so we'll look at how to load sequel server integration services and the various options there are and how to customize settings within Visual Studio to make it easier to use we'll then go on to look at creating a project and package within SSIS so firstly by creating a project in a package itself then within the package creating control flow tasks to monitor flow of execution for the package creating data tasks with the source and a destination and finally how to run a package to import data from a sequel server table into an Excel workbook so let's begin in order to be able to create projects in integration services I need to be able to load a visual studio and I can do that in a couple of ways depending on which version of sequel server I've got installed so if I go to the Start menu and go to programs I can find sequel Server 2012 and within that they'll be a full of an icon called sequel server data tools and what that will do is load up Visual Studio 2012 but alternatively if I'm still using sequel server 2008 release 2 I can find the icon called sequel server business intelligence development studio and because that's a bit of a mouthful most people tend to abbreviate it to bids and although that will load up Visual Studio 2008 which sounds like it's all very different in practice it's virtually indistinguishable from sequel server data tools but I'll go for the latest one sequel server data tools and what this will do is load up Visual Studio 2010 and as you can see the first thing it does is load the start page now what I would much rather see is the thing I was just working with so the first thing I'm going to do is reconfigure Visual Studio to tell it when I next go into Visual Studio it should display the project I was last working with and I can do that by choosing tools from the menu then choosing options going to the environment tab and choosing the startup tab and changing it so at startup instead of choosing the start page it chooses to load the last loaded solution which is in my opinion a much better option so I'm going to choose ok to confirm that and then I'll close down my start page because I don't really like it there's also a window appeared for getting started in SSIS so I'm going to close that down and in fact I'm also going to close down my tool box because while this is useful for other visual studio applications it's actually completely irrelevant for integration services and quite confusing SSIS does have its own toolboxes we'll see shortly but this isn't it so I've got a fairly blank window what I'm now going to do is display the two most important windows in integration services and they are solution Explorer and the properties window so let's start with solution explorer if I choose view from the menu and choose solution Explorer then it opens up in my case on the right-hand side now like all windows in Visual Studio it's got a pin at the top right and this can be in one of two positions red on ttle as there or vertical when it's vertical you can dock the window anywhere you like on screen so you can drag it round and position it on any one of these arrows and what it will do is dock the top or the left or the bottom or the right of your visual studio window so I'm going to keep mine on the right hand side because I'm a creature of habit and that's why I always put it and I'm now going to click on that pin again to make it horizontal so that it auto hides the window and an auto hiding window is good news because it doesn't take up any of the screen unless you actually move your mouse over it and then you can see its content so I'll just display the properties window to go alongside it which will be useful later on and I'm going to dock that as well on the right hand side and then auto hide it so I've got my two main windows visible and it's time now to create a project but just before I do that let's have a look at what the project is going to do in sequel server management studio I've got a table of countries and you can see it contains eight countries and for each there's a country ID and a country name and what my project is going to do is to export these into an Excel workbook so that I can use them now there's easier ways to do this I'll be the first to admit copying and pasting springs to mind and this is a bit of a sledgehammer to crack a nut but it does illustrate how to create basic integration services projects so the first thing I'm going to do is go back to visual studio and what I need to do is to create a new project to hold my package which will do or my data import manipulation and export to create a new project you can choose file from the menu then choose new and then choose to create a project and it depends what sort of person you are what you do next if your C sharp programmer you'll doubtless create a windows forms or WPF application or an air speed on their website if your VV VB program or Visual Basic programmer you'll do the same thing in Visual Basic but we're data analysts so we'll create a business intelligence project weather for analysis services cubes reporting services reports or is in our case integration services which will allow us as it says on the right to extract transform and load data and I'm going to call my project my project and then choose ok so what integration services will do is firstly load up a window called getting started in SSIS which I'm going to close down because I assume you're watching this project so you can avoid that and it will also create the SSIS toolbox which I referred to earlier so that's going to be useful later so we've got our package visible on screen and it's got two main windows it's got the control flow which you use for creating flow chart type diagrams explaining in what order the various parts of your package will execute and it's got the data flow which allows you to create data tasks which is you can think of as like controlling a river of data controlling the Audion which it runs through your package if you've ever had a marble run as a child imagine a marble run full of data instead of marbles and you'll have roughly the right idea so I'm going to go back to my control flow and I'm going to create two tasks what I want my package to do when I run it is firstly to display a message saying welcome to this package or something similar and then to actually import the data from the sequel server table into the Excel workbook so for the first task I'm going to go on the left hand side to the common tasks and find a script task and click on that and drag it where I wanted to go and what visual studio will do is create the task which you will call script tasks and there's always a little icon display to the left of that which shows what sort of task it is in this case it's donating this is going to be a script written in either visual basic or c-sharp so I'm going to rename that and I'm going to call it display reassuring message the only reason I've included this task is just to illustrate that you can have more than one thing in the control flow my second task is going to be a data flow task so I can click on that and drag it in you'll notice data flow tasks are so common that they're in the favorites at the top of the SSIS toolbox so if I drag that in to my control flow window and position it where I wanted to go and I'm going to rename this again by clicking once on it and I'm going to call this import countries into Excel and then press return if I then click back on the first task you'll see there's a green arrow coming out of it and this is called a precedence constraint and what I'm going to do is drag that green arrow on top of the second task so to ensure that the two tasks execute one after the other and if I click off that is actually easier to read and that green arrow will stay visible no matter where I move these two tasks to so that completes for the moment my control flow except that my script doesn't actually do anything so the next thing to do is to configure this script task so that it does what it says it does on the tin and displays a reassuring message in order to be able to edit my script task I need to double click on it and what that will do is bring up the script task editor dialog box which allows me to edit the script and I can do that by clicking on the button at the bottom right corner of the screen but just before I do that I've got a choice to make do I create my script in Visual Basic or do I create it in c-sharp Microsoft seem to think c-sharp is more common in the world and I think I'll probably agree with them so somewhat reluctantly I'm going to go with the default choice and click on the edit script button what's happening now is Visual Studio is creating a completely separate project just to contain this script you can see the project's name is I won't bother reading it out but it's clearly been generated by a computer and it's created a script called script main CS now later in another tutorial I'll explain more about what this is actually doing for the moment suffice it to say that there's a programmer a function called main which is going to run which is going to return the fact that the script has been a success and all I need to do is added line into this to display my message and I can do that by typing in message box show open brackets and then in double inverted commas I can put the text I want to display so I'm going to say about to import data which I think will reassure my user and c-sharp being c-sharp I need put a semicolon to end the line all I'm going to do now is to close down this project entirely by clicking on the cross of the top right when I choose okay it will save that script attached to that task and have I made any mistakes they would show up with a warning icon on screen but nothing like that has happened so it looks like it's all going to work okay now I'm nearly ready to do my data import but just before I do that one more thing let's add an annotation to my diagram just to show what's going on you can add an annotation by right clicking and not surprisingly choosing add annotation and into this box which appears I can type anything I like I must admit I never quite know what to type in annotations so I'm going to type package to import data if I then click off that I'm going to drag it up towards the top of my diagram and it can Nestle there and look impressive I hope so I'm now ready to look at my data and to go into the data flow tasks the simplest thing to do is double click on my data flow task which will import the countries into Excel and what that will do is automatically take me into the data flow tab ready throughout separate individual tasks to this now there's three types of data flow tiles you can create you can have sources which are listed there you can then transform data using the other transforms and finally you can create destinations which is where the data ends up now in this particular tutorial I'm not going to have a transform I'm just going to have a source and add a destination the source is going to be from sequel server before I even start doing that what I'm going to do is go over to solution Explorer and go to connection managers and create a connection to the underlying database it makes sense to do this as part of my project so that I can reuse this in other packages I only really want to connect my underlying database once and once only to create a connection I can right click on connection managers and choose to create a new one and the sort of connection I'm going to create is going to be ole a DB which is ideal for Cree connecting to sequel server or Microsoft Access but you can see there's an enormous choice there I'm going to add that in I'm then going to for the purposes of this tutorial delete the one which is appearing on screen and create a brand new connection and what will happen is a very familiar-looking dialog box will appear familiar if you've done any work with sequel server the first thing I need to do is choose my server it's on my local host so I can type a full stop for that and then type the name of what's called the named instance I'm going to be using Windows authentication and I'm going to choose a database name as movies because I'm actually importing data from the movies database some people then click on the test connection button at this point to check that the connection is going to work I've never actually had it fail at that point once you're able to create the data select the database name from the list it tends to work perfectly so if I choose ok to confirm that and then ok again I've now got my connection ready to use so I can now add my source saying where the data is coming from into my data flow task now at this point I could choose the source assistant and there's nothing wrong with it it's a perfectly respectable wizard but I'm going to do things the hard way and add in an OL a DB source manually if you remember ole ADB is the best way of connecting to seek server or Access database so I can click on that and drag it where I want it to go and what will happen is Visual Studio will create the data source and put a red cross next to it to show that there's something wrong with it and if I let my mouse linger over it there's something wrong with it is that a connection manager hasn't been assigned give me a chance is what I always think at this point so I'm going to rename that and call it country's table and then I suppose I need to solve the problem and I can do that by either double-clicking on the icon on the left hand side or right-clicking to change the properties of the task and it will come up with a dialog box it automatically assigns the connection I've created had there been more than one connection I would have had to click on the drop arrow but in this case because there's only one connection major I could possibly apply to this data source Visual Studio doesn't work for me on my behalf what I then need to do is choose which table or view I wanted to connect to so I can click on the drop arrow and choose the country's table TBL country it's probably a good point good idea at this point to click on the preview button to show me what data is going to be imported and as you can see it imports up to the first 200 rows or displays up to the first 200 rows so I can choose ok and the red icon to the right of that will disappear show me that I've successfully configured my datasource what I now need to do is configure my destination and so to do that I'm going to have to add a destination into my data flow before I do that though I need to be able to connect to an Excel workbook and at the moment I haven't got any connection ready to do that I could right click on connection managers and add my connection in there but that would make it globally available throughout my project and it's likely that I'll only want to be able to use it for this individual package so what I'm going to do instead is add it within my connection manager section at the bottom of this particular package and I can do that by right-clicking on the connection managers and choosing to create a new connection it isn't one of the five standard ones listed there so I'm going to have to choose a more general option of new connection and hope that I can find something relevant in the list sure enough there's an Excel connection manager listed so I can choose that from the list and click to add it in the next thing it asks me to do is to choose a path weather Excel workbook is stored so I can click on the browse button I can choose the folder and choose open but because I haven't typed in a file name it won't proceed at this point so I'm going to call my file list of countries then I'll be able to choose the Open button and it will automatically create the file path for me if I then choose okay I've configured my destination rather the first part of it because well I've got an excel connection manager the bottom of the screen what I don't have is a destination itself I can add that in by going to the list of other destinations and dragging an excel destination onto my data flow task and once again the red icon shows but I haven't you have to sign a connection to it and once again I think give me a chance I can rename that desk Excel destination I'm going to call it a country's workbook and then I can change it by double clicking on the icon but before I do that it's normally a good idea to link up or connect up all the bits of your data flow task so at the moment I've got a source up at the top and I've got a destination at the bottom but no arrow linking the two together I can get that arrow by clicking on the source task and dragging the blue arrow onto the destination if you're wondering what the red arrow was for as we'll discover in a later tutorial it's actually for bad or invalid data but I'm going to assume everything works perfectly and all of the countries flow directly into the new Excel workbook now that I've done that you can see the arrow joining the two bits of the data flow task together I can double click on my destination and I can edit it so it's using the excel connection manager which I've already created for this package again had there been more than one I would have had to choose the one I wanted to use from a list I can now choose what I'm going to call my excel sheet but when I click on the drop arrow says no tables of views could be loaded and that's because this Excel workbook doesn't actually exist yet so what I can do is click on the new button to the right of that and it will generate script when I run that script by clicking the on the ok button I can automatically choose the name of the new worksheet in the workbook and it will automatically create it before I choose ok I'm now going to go to mappings and work out which column in the source is going to go to which column in the data and you can see on the left hand side of my input columns from the source table and on the right hand side are the destination columns in the Excel workbook and this is one of those occasions where I don't need to make any changes at all because they will all map up as things stand so I can just choose ok I've got a little star to the right of this package what I'll do now is to save it and you'll see the star disappears a star is visual Studios way of showing something hasn't been saved so I've got my control flow consisting on my two tasks and the data flow task sub divides itself into two separate stages a source and a destination and I think I'm now ready to try running my package to see if it actually does what it should do so to do that I'll go back to the control flow task so I can see what's going on and one way to run a package is to go to solution Explorer to right click on the package and choose execute package and what that will do is run the package import the data into the Excel workbook and you'll see that happening on-screen so when I click on that what it will do is save any outstanding work it will build it to make sure everything makes sense and then it will run it so if I do that now you'll see little green ticks will appear next to each successful task so the moment is running my first task and that's what that rotating amber symbol means it's display the message thing about to import data which is part of the script when I choose okay it will move on to the second task and immediately run it and the green ticks show that everything works successfully what I shall now get into the habit of doing is to stop execution and by far the easiest way to do that is to click on this link at the bottom of the screen which says package execution completed with success click here to switch to design mode or select stop debugging from the debug menu so if I click on that the green text will disappear and I finished executing my package so let's see if that worked if I go into Excel I'm choosing Excel 2010 here and if I choose to open up a file in the folder I chose you can see it's created a workbook or list of countries and if I double click on that you can see it's imported the country ID and the country name so it's worked I'll just close that down I'll just show running this a completely different way or at least with a different perspective by starting with the data flow tab selected I can run it the same way so I can right-click on the package and choose to execute it and this time you'll see the green text next to the successful data flow tasks so you can either monitor a packaging control flow view or in data flow view and this time I'm going to stop the package running by choosing debug from the menu and choosing stop debugging and you can see I could have pressed shift f5 to do the same thing so I've created a package I've created to control flow tasks for the second control flow task I've subdivided it into a source and a destination and I've run my package to create an Excel workbook if you've enjoyed this tutorial you'll find many more online Microsoft training tutorials at WWE

Channel: WiseOwlTutorials

Views: 960,805

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

Id: 3cPq9FXk-RA

Length: 24min 17sec (1457 seconds)

Published: Mon Sep 02 2013