SQL Server Integration Services (SSIS) - An Introduction | Phillip Burton | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

SQL Server Integration Services (SSIS) - An Introduction

teacher avatar Phillip Burton

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

34 Lessons (3h 46m)
    • 1. Introduction

    • 2. Download SQL Server 2016

    • 3. Installing SQL Server

    • 4. Install SQL Developer Part 2

    • 5. Running SSMS and SSIS

    • 6. Looking around SSIS

    • 7. Data Flow Task

    • 8. Connection Manager

    • 9. OLE DB Source Task

    • 10. OLE DB Destination

    • 11. Data Viewer

    • 12. Practice Activity - Extract and Load

    • 13. Implicit Conversions

    • 14. Data Conversion

    • 15. Derived Column task

    • 16. Practice Activity - Altering Data Type

    • 17. Aggregation

    • 18. Multi-cast

    • 19. Conditional Split

    • 20. Error handling

    • 21. Row Sampling and Percentage Sampling

    • 22. Practice Activity - Aggregation

    • 23. Sort

    • 24. Adding an Excel source

    • 25. Add Text source and Excel destination

    • 26. Practice Activity - Percentage Sampling and Conditional Split

    • 27. Union All

    • 28. Merge and solving Mapping problems

    • 29. Add some auditing

    • 30. Adding different destinations

    • 31. Practice Activity - Different data sources, sort, merge and auditing

    • 32. Adding Variables and Row Count

    • 33. Script component

    • 34. Well done

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Welcome to this course about SQL Server Integration Services.

In this course you'll be downloading and installing the latest 2016 SQL Server Developer – a full version for free, so that you can use it on your machine. And previous versions of SQL Server Developer have previously retailed for around $100, and now it’s free.

So what is SSIS? It’s a way to automate in the importing and exporting and data – or ETL: extract, transform and load. It’s a very visual part of SSIS, using tasks, components and flowcharts, so it’s easy to learn the basics of it.

We’ll start by extracting some data, and then loading it into another table. I’ll take nice as slow as we start to introduce transformations, such as aggregation, conditional split, merging and auditing. There’ll be plenty of practice activities too, so you can get to practice your new-found skills. And if you are doing Microsoft certificates, it can help towards your 70-463 certification as well.

By the end of this course, you'll be confident in using the main Data Flow components and Control Flow tasks, and would be able to use it in your own work.

Meet Your Teacher

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and con... See full profile

Class Ratings

Expectations Met?
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Introduction: Hello And thank you for joining me on this course about Microsoft SQL Server. I'm Philip Burton Afar Can't stop called UK on. Don't be taking food this cults off. SQL Server SS eyes on introduction today I'm introducing you to the world that its SQL server integration surfaces will be downloading Installing the latest on fullest 2016 s girl server Developer edition. That's a full version for free so you can use it on your own machine On previous versions of SQL server developer have returned for around $100. That's free. So what is SS Iris? It's a way to automate the importing and exporting of data. O E T L extract, transform and Load S s Eyes is very much a visual part of SQL Server using tasks, components and flow charts. So it's easy to learn the basics of it. We'll start by extract some data on, then loading it into another table. I'll take it nice and slow as we start to introduce various transformations such as aggregation, conditional split, merging and auditing. There were plenty of practice activities to, so you can get to practice your newfound skills. And if you're doing Microsoft certifications. You can help towards your 70-46 free certificate as well. It would help if you have some tea SQL schools, but it's not essential. If you haven't, then you can follow along with what I'm doing. So without any further ado, let's install SQL Server Developer Edition. If you don't already have it on, then plunge slowly into the world off fs eyes. 2. Download SQL Server 2016: right now. Let's install SQL Server now. It used to be on previous versions of this cost that I suggested you downloaded version called SQL Server Express. And the great thing about it was it was free. The less good thing about it was it wasn't fully. They didn't give come of all of the functions. It came with S S M s and he came of SS. All right. I don't know if you don't know what these acronyms mean at the moment, which was good, but it didn't come of S s. I s especially on Azaz. Yes. So it was a cut down version, but now I can give you Well, let's show you how to install the developer edition of SQL Server. Now you see on here It was previously on sale for around $100 but I could get it for you for free. It's great. So let's see how we can do this. So this is a full version. Almost So if you just Google SQL server additions, we have a link to the official Microsoft websites. You can see how the moment it says max off dot com U S cloud platform ask yourself traditions, so it's click on the link. Andi, you can see that there are four types Enterprise Enterprises and the biggie. It's the one that's really used all of these Mission critical data bases. Standard is a never business type one. There was used to be one in the middle, which was cold business intelligence. It seems to have disappeared in the 2016 Position Express. This was the one I used to have. You installed it waas free. Andi, you could play about with it quite a lot. But now we have developed, unless you have a read about developer if three full featured set of the Enterprise edition . So the Enterprise edition was this big additional is the full version. The only thing is basically for your personal use or even for your business use as long as it's not used in anger. Basically a non production environment so you can click. Learn more throughout to find about it. But that's down now. The first thing you need is a Microsoft account on. If you don't have what that you can click sign up now. I won't do that for real, but just show you what That's like you signed first name, last name, user name, password and all the rest of it create account. It was Sandra an email. You need to click on the email to ensure that you're real person and it's a real email address. Hand Douala your Microsoft account so that you can see it's not intrusive, really in any way. It's working out these patterns. And, of course, you need to have a moderately strong passwords, at least opera case in lower case letters. And please don't use password for password. Want to free. It is one of the most commonly used passwords out there, so I'm just signing using my personal credentials, right? I can see that I need to review the information, so just reduce its not quite right. So Microsoft may use your contact information. Yeah, OK, it's giving me something for free about visual studio, which is interesting because I've asked it to download SQL Server and you could see at the top where in the visual studio Spit and SQL Server development. Sorry, I can't find any Donald's to continue. Please join visual studio Death Essentials. Okay, let's do that. Join visual studio, Deaf Essentials hand again. This is three. So you confirm that right now we're here. We click on visual studio Dead Essentials. Which doesn't make sense, does it? We want to download SQL Server. Not visual studio, but not it's what happens. Yeah, we got all these visual studio off things on top, but then go further down on. We have Microsoft SQL Server developer additional. This Timmy makes absolutely no sense, but it's free, so I don't care. So let's click download now, and that gets you to this page. Now there is a very tempting button here to just down or directly, however less of a reader. What else? There it Because the problem is this doesn't download inexcusable file this downloads and I s all files you can see here. But here we've got an installer which we can use which is provides a quick and easy basic install experience downloading installing the SQL Server injured in just three clicks. Well, that sounds like a really good idea. So don't click this big green button. Just click here to utilize the SQL Server. I'm still out there. We go very quick because all it is, it's a shell. Andi Now he's going to download things now. It's going to take quite a while, obviously, to download the entire lot. But what are we going to do while basic will just get you the really, really basic stuff that is basically as S M. S and SS the administration for a secure server? This is on that. If that's all you want. If you are doing your calls like 70-461 which is all about t SQL Or if you just want to learn about SQL Server Management Studio, that's the one to go for anything else, I would go for the custom. So this goes through the more complex SQL Server installation with it. So here we go. So clicking store is going to download it into that location, so make sure that location is good for you acquiring settle files. Now, if you ever seen any previous versions, you'll see that this is quite a different install window, at least for downloading. Get done in previous versions, but it is going to take our nine minutes on my fairly fast broadband to download. You can see all sorts of interesting message down there now if you did have a problem getting to this page because he saw high to go in and out. So while well, if necessary, go in this page. If you go back to this page, you can always download the express edition If all you want to do is the very basic stuff, which is again s SMS, which is SQL Server Management Studio and also the administration, or least a lot of the administration tasks. But it really is worth a while getting the developer edition because that allows you access to everything that the enterprise conduce. Just licensing is slightly different, so it's going to take another few minutes. Download. So we have a version you choose, Donald Donald it now, and I'll see you in the next video. 3. Installing SQL Server: right is now old. Downloaded on. We've got this new dollar box come up the SQL Server Installation Center. Previously, we had this big green button and I told you not to press it. Now we don't have any big green button. And where don't you start? Well, let's minimize every single help her page. So we just have this installation center. Andi, do you think you start on this one or this one or this dance was none of these. We'll go to the left hander tub and we click on installation. So feel free to read all of these, but not essential. Click on installation and click on new SQL Server Standalone installation or to add features in existing installation where we don't have an existing installation. So this is a new stand alone installations. So you click this top one right now, we don't need this dialog box, and so I'm going to minimize this. So first of all this new dollar box tells his wit tradition do you want and you see we've got the choice of evaluation, which is 100 80 day Valuation express, which is a small version whole development developer, as you see it does not have an expiration. It has the same features as evaluation, but his license and non production database application development. Army. So that's what we want. I'm not going to use it in anger on a huge databases just for personal tinkering about, so they're also click. Next. I accept all these license terms. I've read, um, completely yet notice next. And now he's just checking to see whether everything's fine. You can see is already found a product update so the update will be installed. When you click next, that is 228 megabytes. That will take about a minute to install. So I'm just going to lessen still, so I'll just pause the video on dial. Replay it Once it's downloaded, it's to downloading. But I just wanted to take this opportunity to let you know on the left side. This shows why we started from on where we're going. So we did the product key license terms. It's checked rules, it seemed, whether there's any updates and it's now installing the settle fouls, so basically we'll start from the top, work away all the way to the bottom. However, there will be additional items being added. So don't think this is just eight. But you can check the progress of where we are by just roughly by looking on this left inside as we go. Well, it's just now finished Extracting Settle fails. So installing the settle fouls now in progress, this should be fairly swift. I took about 20 seconds, so please wait while it finishes an expert. Andi. Now it's checking some more rules, so we start at the top again. So is checking some more rooms. Andrea festival, which is good. It's give me a warning that the father were using Windows foul. But if I compress next, that's fine. So what do you want to instill on Vos? I want to start everything. Yes, I could just say I just want the very basic stuff. But no, I want to Still everything. So everything gets ticked and notice what happens is left inside a lot. What's more, I did so there's click next and you could see how much hard drive space is Too quiet A gig mines. Okay, so Okay, Next, right. And you can see that it's not actually going to allow us. You can see that if we're going to once of gold Polly base, we would need an article something over. So we want their thoughts, go back on and want to get rid of this Polly base because we don't need it for that where we are, what we're going to do also don't want our services and our server Stand alone. Second, look at the rest of it. The rest of it looks fine. So let's get next The All Services and Polly base and are new to the later versions of SQL Server. So we are instance configuration Andi for basically most the rest of these we can I will only be clicking one button and going next. So this is fine. That's the next. Next was the server configuration. Andi, This style of box is all about permissions and setting up accounts and Kalay Shin Next database entry configuration windows authentication more is good, but hey, we have specifying the administrators on I always click adieu, current user, so that has me as an administrator. So there's lots more fear I'm not going to go into them, at least for now. Click Next Analysis Services. This is if you're going to use S S A s. You have to choose at this stage of the using MDX multi dimensional tabular hold power pivot. I'm just going to keep it at the default. Had three years of current user flick. Next reports services install and configure and still on it. That's fine. Knicks at consciousness remember, Just click. This had Kant user. It's available. Click next control name. Next install, right Go to put this on the right Inside, Some people do like to see it Install another two coming still successfully while it's installing. I want to talk to you about the various versions now Body previously have talked about the fact that there are There's the enterprise version, which is Theseus, Purdue per one standard version which still costs a huge amount of Money Express, which is free on the developer, which used to cost about $100. But now you can see Winstone it for free, so those are the main version nowadays, but I don't talk to you about this number 2016 now SQL Server 2016 is the time of recording the very latest version, but you might be going hike on Philip. I want to use the 2012 version. How I want to use the 2014 version. What festival? Why would you send that? Andi The answer, for instance, if you have a look at the exam 70 Dutch 461 you can see it's called acquiring Microsoft SQL Server 2012 not 2014 but not is what else it says here in December 2015 this exam was evaluated for applicability to both SQL Server 2012 Andi SQL Server 2014 and in fact, the modern version off the actual certificate his queer ing Microsoft SQL Server 2012 slash 2014. And the reason for that is that it's virtually the same at least that the standard that we were looking at in any of these courses, you know, really, really, really high tech. They do some very good things to the internals. Uh, but just brought goes anywhere near them. The closest stepped who come to new features are new features that came in the 2012 edition . Onda, uh, that was a little while ago, So let me just talk to you briefly about the other versions. So 2016 only dispersion Before that was 2014 before that 2012. There was a lot of new functionality in terms of commands that you could use in 2012 that made my life so much easier. Now, if I just go back and this one out, you can see that the years started in the year 2000. There were there was 2000 and five and 2000 and eight on. Then there was one every two years. So my question to you is, What is this missing one? And if you said 2010 it should be. But that's in the name of it. 2000 and eight are to release number two now, right to the minute does this time recording the majority off mainstream uses mainstream database the big data bases in the big businesses. They're using a combination of 2000 and 8 2000 and eight to on 2012. Hardly anybody's using 2016. It's only been out for a few months on. Basically, you won't see any big users use the very latest version. It's been out for you for a year on, probably not even then, because it is a big pain to taken existing big due to base and move it a little bait on Dawson money as well. So this is why most off the work that she could get professionally. He's done. Having said that, we're installing 2016 on. There will be virtually no difference between 2012 and 2016 and there will be very little difference between the 2008 versions on 2016 you what you get to the 2000 and 5 2000 versions, he devil. Quite a few changes, but as it's a very I think less than 1% of business issues 2000 at the moment, for instance. So any information that you learn in these courses don't be put off by the fact that you're using SQL Server 2016. And indeed in these calls is that we'll be using your combination 2012 and 2016 because he's hold the same for why we are for the basic for the intermediate user, you won't notice any difference, so I will be using both 2012 and 2016. Just don't sweat about it, but the major thing is, we're getting a school. So 2016 developer for free, right? Well That's all I'm going to say for this video. As I say, some people do like to see that it's installs successfully, so I'm going to switch like falling off on def. You want to move to the next lecture you can and not let's see it installed, right so you can see one or more affected files have operations pending. You must restart your computer after the process set up is completed. So in other words, it's done. Basically, everything is just got a small thing left to do and you restart the computer. But you could see everything succeeded. So now I will restart my computer. Andi in the next letter will actually run SQL Server. 4. Install SQL Developer Part 2: Well, now we got the back engine installed. We now have to install the front engine and avoids the bit that you're going to see. So if I go down to start and go to all programs off, perhaps you've got it as a lapse and scroll down to Microsoft SQL Server 2016. Andi got to installation Center What we get back to this familiar switchboard. It's called a Standard Boxes a switchboard. So let's click on installation. We did the top one. Now we have to do the 2nd 1 so let's click on the 2nd 1 This will install the front end SQL Server management tools, and this time there is a nice button with Jews do conflict, which is bizarre. So you got to click the big blot, so now you can see that it's downloading Onda. This will just take three minutes on my Internet connection, so let's just see what's being sold. So if I going to collapse Andi Microsoft SQL Server 2016 If you've got 4012 Dewie, those aren't stole by some over programs. They're fine. You should concentrate on 2016. The full version of 2012 nowadays that work we does 10 which is a bit of a shame. So we've got some reporting services. That's S R. S. And this is how you can't come figure it. Andi, you've got some more less important stuff. Perhaps importing and exporting data. You've got a dictator quality server and you've also got he configuration manager. Now until you start getting into the higher parts off SQL Server, we talked for you, will. But for now, you probably won't need any of these. The only thing you would need his SQL Server 2016 Installation center Andi, what's being installed and also what we're going to install in the next video. So what SQL Server Management's tools will allow us to do is S S M s onda administration. So if I could just explain what all of these various acronyms are, I says Ennis, she's SQL Server Management Studio. This allows you to called in sequel or T sequel, which are all of these select staff from my table type of statements and affords that the actual programming elements that which is something which is quite well worth learning It will also allow you to get into administration allow you to become what's called a database administrator. So that's SS Embeth is this bias is SQL Server Integration Services. Onda What? This will allow you to do his automate quite a lot off what happens up here, but you have to really get to grips with this or the very least this before you can get on to that. I would put this first and second half ago report, says Horace, which is as conservative with porting 14. Studio on. So that is getting information out in reporting for Mt. And finally, this have been called SS Yes, Analytical Services, and that is to do with cubes. That's is to do with getting information helped, but in a form that easier to use once it's set up. But to start with it is a bit more complicated. So if you're just starting out of this, concentrate on that and then I've a report db 81 of the two, and then only go down to As I said, This is two years later, but the great news is developer will get you all of those. So nice downloaded just last minimizes What is this? So it's still Onda lording packages. So it's not downloading packages anymore, so this will probably just take a moment. Now you can see some of the packages starting to download, right? All specified components have been stolen successfully. Sounds great. So we can close that and go back to this Tyler box and also want to install SQL server data tools. So we got another link. Here we go. We can download, got game, got a good friend of the bottle. So we download data tools so you can see it says we recommend installing visual Studio 2015 . So, in other words, you got to almost suddenly, um, now the visual street, Your community is free. So I click on that and thank you for downloading it on. And there it is. So we're not in my eyes. Everything goes. Now it's installing. And again, he's just a click and do so We started off with installing some back engine stuff. Then we've installed some front engine stuff in the front engine. Looks quite easier so we can customize this. Let's keep from Costa. Might next. You can see what you want to instil Kraft bags. It's very few. These If any. This is the only one Microsoft SQL server data Tools. So click next and so right. And as you can see, it's now all installed. The computer needs to be re start before starting the product. So click reads. Don't know. I'll see you in the next video. Right now we've got visual studio installed. We can go back. Andi going into Microsoft SQL Server 2016 and go back. And do you notice how long this less is and cutting into the installation center? So we've installed the back end. We've instilled the front end on we've installed visual studio, but now we can install SQL server data Tools, so download SQL server data tools. It isn't stolen. All right, would then go down two point free, set up an administrative install point. It's badly named, but then download the appropriate SS DT set up. So going to do that So English, in my case United States, so you can see that didn't take long. So click Next. Yeah, you have read them next. Andi, it's now downloading the latest SQL Server data tubes. So again, I, like celebrate this a bit. Right? So now it's installed. SQL server data tools. So how do you know my fruits installed or not? Well, if we open up visual studio, this is visual studio 2015 community. You can see the little icon that's there. Onda. We create a new project. Then on the left hand side. Previously, we just have things like visual basic on visual F sharp C sharp, C plus plus, But now we've got these and these the really important in our analysis services, integration services, reporting services. So if you remember from Elia, these are that says yes, This is ice. This is ours. How you get the whole within visual studio. So previously we also installed assess m s. So let's go and find that it's difficult to elapse Microsoft SQL Server. And here we are, right near the top Microsoft SQL Server Management studio. You can also find it by ruling S S M s. So I would go, It's okay. Okay. Meisel, These Yeah, it is now, Lord ***, for the first time, it's lording user settings or, more accurately, its making up some new user settings and giving them to you on there is your Microsoft SQL Server Management Studio. I'm looking. The seven name Don't. Okay. Okay, Then we got wearing vent his process M s, which also allows for database administration on Dhere. We've got SS. Yes, and has his eyes on decesaris. So congratulations is taken a bit of a while, but you have now installed the full SQL Server 2016 Developer edition for free. 5. Running SSMS and SSIS: right. Well, that took a while. But finally we got SQL server developer installed. I hope that you got it on your machine as well. Now the first thing we need to do it is that just introduce yourself to the two main programs that will be using the 1st 1 is SQL Server Management Studio S S M s. So if you go to all programs or all APS, if you're using Windows 10 and scroll down to em, we have Microsoft SQL Server 2016 off course. You might be using an earlier edition. That's absolutely fine. Things haven't changed significantly. You can go all the way back to 2000 and eight are two. Really? Andi, you'd still be OK. So inside the first thing that we have here after development was a deployment with it and manage hope settings is Max Soft SQL Server Management studio. So if you click that so a probably very familiar friend s s m s comes up right, we can install the server name I'm using is the very local host which have also put which is also called just adult so which have a server. You're going to look into blogging to it now. Now, hopefully SMS is very familiar to you. If not, there are plenty of courses available about t SQL. I wrote six of them myself, which introduces you to SMS Onda. We will be using this largely just to check the results off SS Ire. So we'll be staying long in here. So the first thing we need to do is get her database. So Adam, a little have got is the report server. And I've also got a database called adventure Works like 2012 on Dive used some of the data in that in a different format for the data that we're going to use. So if you do want some sample data, I can recommend the eventual works. So, as a resource to this lecture, you have a zip file ss eyes dot zip, and if that you can install it, I'm installing it into my C drive into a folder polled and says I s right and we can see that there is a SQL query cold setting up. So if I double click on that, you can see that it opens in. I says I ams Now I'm going to run this in a moment. But festival. I need to create a database. So I'm going to go on for here to the booby new database, and I'm going to call this database S s. I s gonna suggest you do as well. Yeah. Andi. Now, in the query I got to the available databases on you See, the SS ice is now an option. Andi, I just place execute. So we have query executed successfully after pressing this execute. If you don't see this green tick, then either you've not created the database or you've not changed which database you're using, so just try to get so if you expand and expand, you can see in the tables. We've got quite a number of tables, Andi. There will force ready to use, but we'll introduce them at the time, So that is assess M s. And that's all I want to do with it at the moment. So just minimize everything else. Now the question is OK, that's how you do s S M s. How do you open up? It's is science, which is SQL Server Integration Services. Well, if you go to Microsoft SQL Server, you can't really see anything called integration services. This is a classist, but that's not great with it. And if he was a search for it No, My guess is the upgrade was it? And that's because there wasn't actually a program Cold S s I s So let me just explain to you this pre different programs that it could be the 1st 1 if that you haven't told my videos about installing SS eyes is probably because you have all Degussa pre existing version. Now SQL Server comes in years groups. Why they called the version after 8 4000 A call to or not 2010? No idea. However, it's these two I wish to concentrate on. First, there is a program Cold Bids, Business Intelligence Development Studio. On. That is the program that you need If you're running one of the older versions Now, if you're running anything more recent, including the program that that we've just installed, then there's two possible programs. The first is a visual studio. On the second, he is called S S D T, which is SQL server data tools. So it's important that you find out which version that you're using. If you'd have seen from the splash screen when you opened up S s m s, that which version of SQL Server you're using. And as I say, if you have just followed my instructions, you are using 2016 version. So find the program, which is out of bits or SSD tea or visual studio. So in my version, how go to elapse and a scroll not to Microsoft SQL Server, because it's not actually there. But keep going down Onda Visual Studio 2015 and do recognize it with this logo. It's not the warm of folder. It's this logo, so it opens up Andi. This is essentially why we need to be much softer visual studio never goal Foul New project You'll see on the left Inside Analysis Services Integration services are reporting services . When you start off, it may look like that or may even look like that, but with open up business intelligence and open it integration services. So if you are using a version over than 2016 you may have SS DT and still do really depends on what programs you have installed on your computer before installing gets cruel server, so I can't give you a absolute answer. It will be this one. So if you got an older version prior to 2000 and 12 in open orbits if not, then have a look for SST t or have a look for visual studio which ever way This is what we need to get to so file. New project? No, we just call it something s so I'm going to call it. This is Elias working? That's the name of the project. That's the name off the big container. Onda, find a location that you're happy with. I'm going to be putting it into a self folder called Working, which you've got if you unzipped the s s eyes. So you know the delete me. That's me. Just doing a trial version before recalled in this video. So click select folder It will automatically create a new folder called S s Eyes. Working Andi Integration Services project is fine. We don't need that. The import project was it because we don't actually have an existing project to impart. So once we've done that, click OK on. This is the result. It's a blank canvas now it looks fairly complicated, but don't weigh in the next video. We'll have a look around it 6. Looking around SSIS: right. So let's have a look around. Visual studio, inasmuch as it pertains to SS eyes. First of all, the rice inside we have this solution explorer Andi. You see, there's our size solution and within that we have our project. It may be differently named on yours and within that various elements to that project on at this stage, that's all you need to know. It's the rest of it. Isn't that relevant? They're also tamps down here again. Not relevant, really. On the researchers say not relevant is because Microsoft Visual Studio is not just for about s s IRS. It's also about S S r s. For example. On we have a properties pain down here on this property is pain. Is Jolly Johnny useful? If you're doing S S R s, which is SQL server reporting surfaces, it's much less useful in SS eyes. So there are some things you can almost ignore. Now each of these pains can be dragged out. They can be aligned to the top. They can be full windows. Onda. These little tabs can be detached on moved about there can be pinned into place. Oh, opened So in affords they can stay position life. It's that this one is pinned. But if I open it, then it disappears into the side until I kick on it. It's needed. So I drag this out and I'll put this some to the right inside like this. Or I could put it in even fervent right inside by that. So really, what all of this is about is to show you that there are so many different ways you can customize of your view that you aren't required to have just the same view as me. If you want to do something, girls, if you've got two monitors, for instance, you might find that having some pains on the 2nd 1 a second monitor would be more useful. Now I'm restricted. When creating these videos to a very small resolution, you probably got a much bigger resolution, so feel free to customize everything is you want. We also have an output pain, which will be quite useful on then. On the last inside, you've got a server explorer, which is only useful if you're connecting to servers outside off your local one, so I gain not that useful, and we have a toolbox on the two books might say there are no usable controls. It might say something else, but at the moment there will be any actual tools in. Now this one I'm going to pin, so I'm not going to get it hide because this is the second most useful window around. Let me show you the first. We're going to create a project, so we're going to get a package with faulty created a project we've created a solution. Recruited the project. Let's create a package writing click on your project and go at Do you want to on what I want? It's a new SS I package creates completely anyone, So I'm going to call this. It's a size cliff had there. You can see it's been understood to this solution Explorer. So if you ever get this removed, my closet or you close and save the entirety of the thing off the solution and open it began, and for some reason it's not. They're just going to the solution Explorer. Drill down to your packages, and you can hope not as you can also set as the startup objects as well. We'll come to that in a bit of a while, so I want the two box open tennis into place to minimize this slightly. So see the bottom. Now let's open about two box. There are no usable tools. And if you click on data floor control floor and there's still no useful tools just because unfortunately, there are 22 boxes. So this is the toolbox that you use with the rest off visual studio, but not S S s. So use it if you're creating C sharp packages and that's off Instead, we need the S S I s toolbox, and there are two ways to get it. We have a SS iess menu and I can click on exercise to box. All I can write in, click on a blank space within this package and go exercise to box on there. That is the toolbox we want. So if you saw the previous two box, it's empty. That's not useful for us. Now, this package window is the most important window off SS eyes. So the two boxes the second The package is a first. Now, what you to notice is that there are five taps in here. Now look, the toolbox. We start off with data floor task executing SQL task. If I go into the data floor, the two box radically changes nonexistence in the promises. Well, very little in the event of a handle is that you haven't told De Sena in the control floor on a non existent in the package Explorer. So if for some reason you won't say emerge component and you can't see it, that's because you may be in the wrong section on disease control floors and data floor sections. Those are the two main sections. The two men taps off s s eyes. I'm going to be concentrating most over the valleys, the beginning part of this course in the data floor, looking at all of their to box elements and then afterwards will come back to the control floor. But for now, what we have done that we have installed a program that allows us to use SS eyes. We've installed sample data in S S M s, and we've created our first orb a blank package in visual studio right, Forbids, oi says D T or w calling it. I quite like the word bids, even though it's no longer called bids because Easter's very straightforward one syllable thing bits, whereas nowadays we got SST tea or visual studio off, probably calling it visual studio for months of the rest of this because that's more modern . Name that in my heart. I still referred to his bits. So now we've got this. We can actually get to grips with S s eyes. 7. Data Flow Task: more than any off a component of SQL Server. Esa's science is very visual, you know, in, for instance, assess M s. You can create queries using a builder. So design query editor on that gives you a bit off visual elements. But S s eyes is just in a completely new level. This is all visual, at least to stop. Now we start off as we have to with a new package in the control flow. But this isn't really where I want to be. I want to be in the data floor for the first half off this lecture savers. So what I'm going to do isn't going to drag into the control floor a data floor task. Here we go. So literally drag it from the two box to the control flow. So let's just have a look at it. It's a rounded rectangle. If I click on it, you'll see that there's a barrel leading that would lead to the next task. If I put one in, it's not compulsory. I can click inside it on to rename it so tough luck or whatever you wish. Generally, what you need to name it has is something that will tell you as some decorum back into this project after a year or two years what this actually does, because there's a lot of hidden debts under the graphical interface. So if you can range it so that you can see what it does, it will make your life so much easier afterwards. Now, if you have a look at the toolbox for the control floor, you can see that it is very high level activities we've got, for instance, file system tasks Some of us do with the finding structure off Windows Explorer we've got executing package will the in a package. So this executes a noble one. I got things to do with FTP file transfer protocol, no foods loading files onto the Internet, so very high level stuff. But what I really want to do and that all of this lot within the over tasks is db a stuff database administration stuff Such has reorganizing go rebuilding indices, although vital. But they uh not the finer things. They are very broad tools. The data flow task, on the other hand, allows you to have really high level of control data. So to get into the data flow task. A double click on it. Alternatively, I just click on the tub. Now there may be more than one data floor task in you'll package. And if that's the case, you have to select which one you are in by clicking on the drop down box next to data flow Task. Now just one other thing before going to their names have to be unique within a package. That's not entirely true, as we'll find out later. But if you see one of these containers, it has to be unique within a container. But just for now, let's assume that every single name has to be unique. So, for instance, he fails to get a second date. Applause task. I cannot rename it data floor he wants allowed me. So you see another object in this collection. So it's the entire package is, at the moment a collection, so I don't want that. But if I had that, I could then going to here and choose one of two data flow tasks. So I'll just delete that by going down to delete, so you'll see that there's a lot that you can do on the right hand mouse button. And in fact, we will be looking at a fair number things. But for now we can click on edit that that gets us to identify top floor task to her as well. So there are three different ways we're going to my data flow task double clicking on our control for just going into data floor and selecting which while we're talking about all right in clicking and going to edit. So now we are in the data flow task. We will be spending a lot of time here now it says I s is part of a E T l system extract transform? No. So we extract data from a source, we transform it. In other words, we configure it in some way, and then we load it back into another source. So why is each of these necessary while extract if we didn't have himself stated to work on everything else is meaningless Lord, which just in case you can't tell from accent Stan, tell a l o d Lord is putting everything that you've done into. It could be a database. It could be excel far could be a text file. It allows you to save the results of what you've done. So you get data for one place, you save the results, so transform its everything in the middle. It's getting it for one form to another. So next lecture will have a look at the connection managers that you can see down here, which enables us to communicate with the outside world. 8. Connection Manager: we spoke about the principles of e T. L. In our last lecture, every E and every l requires a connection monitor because you're getting better from somewhere to somewhere. Now there are lots of ways that you can enter the connection manager universe. But I'm going to do the most direct way, which is actually in this connection manager paid so right and click to here to add a new collection manager to the SS ice package. Okay, let's do so, Andi, instantly, we've got six options Now, if you're using an older version off bids or S S d T, then you will have of options. One of option you'll have isn't old ABC Connection. If you are using 2000 and eight or 2008 are too. Then you probably won't have an oil e d B connection. So what are your options? Well oiled E. D. B is to connect to SQL Server all 20 data days. If you were using SQL Server 2012 2014 2016 then used the oily DB. As you see all, DBC is not actually recognized in the lettuce version. It's got what's called depreciated it. Microsoft saw that there was a new way off. Communicating with databases for two is better, and so in 2012 gave you a bit of time to move on to the new way before, as you can see now cutting itself. If, however, your using 2008 R two or 2000 and eight, then you won't have an oily DB connection. You'll have an or DBC connection. So whichever one you got and poorly DB or or DDC connection, this is how you connect to SQL Server. So let's create this new connection Now. Here are previously defined data connections there. All Tony no so surprised. So let's click new on this is the collection under Now You may have seen this numerous times on If you be connecting with Excel to databases or access to databases. Only from now that it may look complicated. But it's not really. First of all, we have a provider of the top. This tells the computer what sort of connection. Just leave it as the standard native client unless you got a reason for changing it. And if you're communicating with physical server, you don't now the seven name. This is the server that you connected with in SMS of my cases just docked. And then underneath that you've got the authentication. Now there are two types or main types anywhere off authenticating windows. Authentication your boards. You've logged into windows using a user name and password. No, Maybe happened. Maybe you've got a Windows seven version that you can bypass the user name password. But even if you hadn't, if you even if you bypassed it, you still have one. It's just that you don't know what it is. The alternative is SQL server authentication. That means that it doesn't matter what user you logged into windows as you have to knock into SQL Server again using the user name and password Windows authentication bypass. Is that so less secure? More secure? It's an argument for D. B. A is not for us. So just know What's your server name? Aunt, how you authenticated. So let's go back into this dialog box. So the server name docked. How did I do it? Windows all SQL server authentication windows. So if you Dennis with SQL Server, then you would need to put in your user name and passport. Now you can get a drop down list. Sometimes it doesn't work on all machines. Noticed. So it's best if you actually know what your server name is. Now you need to connect to a database. Now, you could if really weird circumstances attach a database file. But I don't think I've ever used that in anger, so let's not. So now we need to select a database name and you can't see the right and edge of the drop down box. So I just need to expand this. So notice. Just in case you don't know, there's E six dots in the bottle, right and corner. So where you got that means that the Daleks box is expandable. He won't collapse away. You can't get it all the way out here. There is a minimum size. So the database name. I'm going to going to the S s I ST base later on. If you really start developing in a society you may not know at the time which database you're going to go into, if he's don't, Then I suggest you going to the master database. But for now we know which database we're going In s s eyes. Now, we contested connection. Always a good idea. Make sure that's tied to the details correctly and click. OK, so that is the connection. So local host which is another name for adult Andi There's everything there just in case dot doesn't term comfortably internationally. It's also known as a full stop period. So click OK again and there we have our connection Manager Local Holst adult s s eyes. Andi, I can rename it if I wish to under thinker, we'll just call. It s a size. I'm going to call it SQL Server because later on in this culture will be looking at connecting to Excel spreadsheets. We were connecting to text files. This isn't the only one. Now, as I say, this is the most direct way off getting gay connection manager, but just have, look, appear. We have destination assistance and source assistance. And if I double click on the destination assistant, you can see that we could create a SQL server connection that way, Or we could connect into Excel connection that way. Interestingly, no text files or available here. Why was if you rushing click down here? You do have flat file connections which are text files Onda very several options. So personally, I prefer using attraction manager. But we'll be doing a loss of very stiff in connections, so it's entirely your choice. So now we have the handshaking. We have said This is our source or this is our destination area. There's just one problem. All of this is like and if you remember from the control floor, it's going to go into all of these tests that we've put into so far, we've put it in zero tasks and data flow. So in the next video, we're going to put in assaults. 9. OLE DB Source Task: right. Let's now put a source into here. Flow chart Now, though, are lots of sources that we got a toolbox with source assistant and then further down we got the hate D. F s house sauce onda. We can't hold DBC Fassel's. But as I said, that's being depreciated. So you should avoid using that Onda much further down. We've got all of these over foul sources, but it's easiest if you use a source assistant. So let's drunk the source assistant into our floor chart. Interestingly, it's a pity heart when we got to extract things first, which requires assaults and then lord it afterwards, which requires a destination. But because it's in alphabetical order, source comes after destination. Really, it should be there for ever happened. So let's get a source assistant and just have a look at the arrows if it's confusing, So let's drink this it. So we select, which sells and notice here the sources system. We got flat file, which is text on Excel, not in high school silver. We could have used that connection manager here, but we've already defined it. So that is the size SQL so on. This tells you so information about it. I'm a click, OK? And there we have our first component in a data flow Deter floored users components? It doesn't say so, but all of these are components in the control for all of these are tasks, and it does say so. But these are components, so we have going from a database going outwards. Now, if you click on it, you'll see that there are some connections. Remember that there was a connection here as well. We're not going to use a collection just at the moment. But obviously, once you bring data in is got to, then floors it. So why? This is a floor chart, so it's got to move somewhere. So that's what these connections are for. But you see, not all. It's right. We haven't X a destination table name has not been provided in. Avoids we've connected is a database, but we're not said exactly which table we want, So let's double click on it, and the alternative is writing, clicking and going to read it. So be careful not to double click on the name because you might be editing the name so we can see at the bottom. There is the problem. Select a table off you from the list. Whenever you get anything down here, it's important to correct it off wise. I can't necessarily flick. Okay, what? OK, person. But there's no Caperton blow while I'm recording and I can't see it. But that's okay because these dollar boxes are re sizable or you could drug. It's a top of the screen, especially if using Windows 10 on Maximize it or we could just drug it there to the middle of the screen, and that will just make it its highest height. So just in case you can't see Bill six adults, which I couldn't at the beginning, there are over ways of resize again. So this is a typical dialogue box in exercise we have on the left inside the connection Manager columns Andi Error Outputs of each of these are different tables by quantity. Click on that These dialog boxes not tables, dialogue boxes because I have not corrected the error here. Select a table of U from the list. So let's look what we got. We've got the connection manager is the s s eyes SQL server that we did earlier. We've got then control to a table of you or we can select a SQL command. So if I was selecting an SQL command, it's like soft from etcetera. Hopefully, you're very familiar of SQL commands. If not, then out suggest looking at videos to do with 70-461 which is all about learning t SQL. However, you can see if I got to ask your commanded saying, I need still needs to do sinks compose an SQL query. Now what we're going to do is stick to the default table of you. So the name of the table, the view is, and here said, these are all of the tables that I've currently got in the database. You may have more that SQL script that you run. I'm modifying it as the course continues. So what I'm going to do is have look at the customer table. There's going to be some practice activities for you coming up, and you will largely be looking at the address table, so I'll be looking at the customer table. So now it's let that the warnings disappeared. I can click preview, and that gets me a preview off the date of the 1st 200 rolls. There we go. The numbers aren't consecutive, by the way. So if you want to have a couple to 300 something, that's why there are already 200 worlds there. So now we could go into columns. So this tells me the Souls column on what we're calling it, So this can be incredibly useful because of wall unnecessarily. I want a all of the columns, I guess if you're asking for all the coms, that's more data that's got to come from the souls, which means it would take longer for the computer to process. Or it may put more off a burden on this server. I'm talking when we got millions or billions of roles, as opposed to the got 400 right 100 rolls so we can de select, say, I don't want to see middle name. I don't want to see suffering, so you can see they get deleted from this list down here. Or maybe you're saying on you can see it's if you take something, it gets in search of the bottom. Or maybe you want to say, actually, I want to change the A s off What was sick? The alias being gay. T SQL wyd meaning? Let's call it another name. So this is where we should say I want X y and set. So let's have everything. There's 1/3 tab, which how? Look at later air our pot. It's not needed in the moment, so click OK on and this right X doesn't disappear downstairs. Yes, so we have our first component in our days of flow. So let's just execute. This will click the start. Let's see what happens. So done. You could see everything changes. We get rid off the right inside the solution Explorer and the properties, they get hidden. We get rid of the two box not needed. The moment we have some additional pains. Autos on local and what one on? We have lots more over here. Call stack break points, exception settings, command dwindle, immediate window and output. I did see I don't want to just want to have a look at to start with, so let's just see what it says. So the packages starting, it's getting validated, Andi. Then we have a look at each of the combs on the warming that we've got Is this Cullen that way? Are you importing? Is not subsequently used. Removing this unused output column can increase data flow task performance. Well, that's a factor for warning on. Then with the power of execute, we're pre executing. We're executing, repulsed, executing Were cleaning on we finished success. Okay, so that tells me that everything's being okay. So let's now put this back. Thies is tamps or not? That one. We want to keep the package of their because sent sound main one. So playing just bit money with all of these. So this is an excellent time for you to just one might refresh yourself that you can move things all of the place. Andi add them into tabs and that sort of thing, right? Let's have a look at the main event. So data floor task it went into went into debt. A floor task went into the only db source component Dick on because everything in the data floor task was done, this entire task gets ticked. So ideally, therefore, what you want to see is a lot of ticks on. You'll see the bottom package execution completed with success. Now notice that the top it still says running but start actually doing anything. It's just in the debug running moat. You could see Stop the booking from the debug menu or click here to switch back to design mold which were to begin with or as you just press this stuff. But all you could press shift in a five if I have to run the shift at five to stop. So whichever way you do it like that and we're back to the depot commode. Now, if you're expecting some huge result from all this, you may be a bit disappointed, but just think what we've done. We've just load the data into memory, but we haven't done anything with it, but at least it shows that S s I s is working even if you can't see what it actually did. So in our next lecture will had a second component to the data floor. We've done the exit. We've done the e the extraction off e t l. So in a 2nd 1 will do the l love 10. OLE DB Destination: Now I just want to show you some of what was in that script that run right in the beginning . Well, first of all, we created a big address table on. As I said, that will be one that you'll be using in your practice activities. Then you create it'd be customer data table. Each of these lines have 100 rolls underneath and then one that links the two a customer address table. Now what happens next is have created a table called output 01 on this is a table into edge . The customer table is going to go. You notice that there are no insert in twos. Andi, let's just open a new dollar box in the query window. So from I'll put Syria one. And I'll just press control and shifted just to get rid of this squiggly lines. Press execute completely blank table, No rules. So what we're going to do is get everything that's in the customer table into this output table. So we've done ourselves. So now. Secondly, we need our destination. So track this in still using Gaskell. So they're still using that connection manager. Click OK. Ondas an X, the destination terrible name was not provided. Let's double click or this component has no available input columns. What do you think That I mean, have a look. It means that we've got a source. We've got a destination but would not link the two. So I'm gonna cook. No, on this, let's click. The sauce will take the blue connection on drug it down to a destination. So there you go 100 just resize this. Let's resize this one, actually, just to I just want that like to look straight. So now we still have a destination table, not fight it. So let's double click on it again. Right? So we have the same problem at the bottom. Select a table of you from list So the table is going to be this output 01 Andi, we're going to the other dollar boxes map ings, for instance. Now it's interesting to note that I got every single column which is, in our data input data into our help or data. If I look at this, I don't have the words suffix and the column suffix an adult of the column middle name so that what the computer has done is, it's looked on and where the date is coming from, he's not. White is going to on where he's got identical names. It's mapping it. You know what? It's Christ in this line. And if I was to counsel that, for instance, go back into the souls, make it a yes. So in over his first name and last name are not completely identical, then going to hear, to assist, like to help. It'll one look at the map ins. You see, first name and last name on, not map took anything. And you see that down here? We have a destination called first name in the destination called Last name. But we've got no input, so we can change it here. Oh, we can drag across are not. So. Regardless, you need to make sure that you are comfortable you are happy with. But once you see here and again, there's an air output. But let's not do that for a moment. So we're going from assaults to destination just as a reminder. Our destination currently has zero rose in it. So that stopped. So we'll see everything, get text and you see something in the middle 847 rolls. In other words, 804 7 rolls have gone from this component into this component, so it's proof that there were 847 rolls to begin with. But we didn't know that at the time, but we know that they're being transferred. Andi, we assume David putting correctly it has been taped. So let's stop debugging. Let's go back into s estimates on Let's Rerun this quot and we now have 847 rules. Now if I was to run the package a game, there we go. There's no primary key in the table, So if I run that quickly, gain were now 1694 rolls and self off. So if I drink it table, we go down to zero skin. So is proof that this works. We have lorded it, so we have extracted it on. We have lorded isn't my destination, so we have created our first fully working package. Of course, it's not wholly satisfactory that we can't see what is going on. It's a bit like working in the blind completely in the dark, but that's fixed out in our next lecture. Let's take a peek as to what's happening 11. Data Viewer: right. So we have a source, we have a destination and we have an activity between but we can't really see what's going on in the middle. Always sees what happens at the end because we're going to SMS and we were on the select equity. You would you see that the number of rows are transferred, but we don't know what Well, thankfully, there's an easy way of finding out. If you write and click on the arrow, you can enable a data viewer right now. Double click on that. Since this new icon that looks a bit like a my a magnifying glass on, we have a general term which is completely un interesting. We have a metadata time. OK, well, leases tells us something. This tells us off the type of data that is coming in now. Hey, all the data types on grow to say that if you are used to bar cars and bits and small inns and I'm sorry, but they've changed its in S s, I s We have these really unusual acronyms. DT design tools, string extra, so 1,000,000,000. It's awful. Onda, we have delaet. So I'm afraid we're not using bar Charles, and that's what to think about that, but is really this third top of that simple to figure Seattle data view itself so enabled Interview is automatically detect and all of the columns of being shown. So if you wanted to see just few columns and so you don't want to see Suffolk's and middle name, you can put them into unused columns. So as soon as you enabled the viewer column by writing, clicking, going, enable data viewer, that's what you have to do. But you can customize it. So now let's execute that Andi, look what's happened. Well, first of all, this first component is ticked. Then we know that there are 847 rolls. This second couple in Tel Aviv is not tickets. No, we stay yet instead, we get this new tell a box that tells us what is being passed now. We excluded a couple of columns, awfully support. Would it for my date of you. And there you go. 847 rolls, which is say is that now we can copy the data, say petition to Microsoft Excel. So is grateful what's called debugging? Trying to work out what's gone wrong with the package when it doesn't perform as we think it would. So detached data in the view is not updated too much today to each buffer that really is only relevant for very large amounts of data. But as soon as you detach it, it then runs on. That's just close that. So enough words. Let's just play that again. Stop and play. When the data floor is attached, it stopped progress. If I detach it, it's allows progress to continue. Attaching it to now doesn't do anything because it's already gone past that. But if you were doing a really, really large volumes were talking in the millions of roles, then it will show you what's in what's called a buffer on that. It will show you what's in the second Buffon's third buffer, so I don't know how useful that attached is because you can copy the data already. Maybe you want to compare one data floor data viewer with another data floored interview, which could have over there. I can't see being too useful, at least at this level. Now if I press start again, the other Dalla box study of option we got his play and that resumes playing. So playing the execution off this package is composed if I click play, he continues. So this attached button not really that useful at this level in your so Today we go, we are now able to see what is being passed through. Now that doesn't mean that the table at the end has 847 rolls. We've executed this multiple times. This data table now has 5 2540 world roles. But it's telling you what's happening in this particular instance. We start from here. We go all the way fruit there and we end up that. So that is how we can see what's happening internally and we can. If I stop this once, we're happy with that. We can disable the data fewer, and then when I click start, he just goes through his double. So that is one way or being able to get at the internals 12. Practice Activity - Extract and Load: right? Well, we've because quite a lot of ground so far on because s it's Isis such a visual medium of old, These floor Childs. It really requires you to repeat what I've just done in order that you can retain it. So I've created several practice activities and we'll do the 1st 1 now, which basically is just what I've done already. So I see was in practice activity number one. First of all, I want you to create a new package or project. The technology is slightly different if using bids. But one of the older version of SQL Server to Val Tony, talk to your 2008 alcohol. It packages in this but creating a package questioning project. There's a table in the script that you've run called address. And if you've not run that s girl script, then you really need to do so too. Do this practice activity. So I want you to extract that table and then I want you to lord it in a table called result 01 So really actually do this practice with activity now pulls a video Onda do these pre steps and in a minister saw come back Onda. We can do it together just in case you got stuck, right? So hopefully you've done the activity. If you don't find, then feel free to move onto the next video. If not, however, that's a look on and see what needed to be done. So creating a brand new package. So new project. There'll be a package inside of that, so we'll call this. What's having? I called this practice activity. It's lecture folder. I'll choose the working folder on and Click. OK, there you go. Gotta created data floor task not just that my practice activities did, or make several steps that you got to do. Some hoping you could retain the information we create a source New. Ask your services because this is a new package. It doesn't retain what what's in the over package, Which is why I want to do to create the new package so you'll have experience doing this. I need to say which table it is. It's the address table. Andi. I'm putting it into same connection Manager Junior to specifying the table results. One map, the columns, their amount. Andi, let's play. So just to show you it's like star from results of long. Their country is nothing there. Execute this. And now I've got 450 rolls. You could if you wanted to put a brake a, um, data viewer. Well, he was well, but that was a part of the activity. But if you wanted to do so, that's how you do it. So that's how quickly all of these things can be done. I'm taking the over videos at a fairly slow speed, but once you get used to it, you can do all of this very quickly. 13. Implicit Conversions: right. In our previous section, we had to look at the E on the l off CTL extract. Transform? No. In this section we're going to have Look at the t the transform, Professor. When you trust the question, why on earth do you need to transform to begin with? Let's look at this example. Here we have our customer table on. We have a customer, i d small int. And we'd be now putting it to your table called Count. Put 01 which is a customer. I d small end. Now look at this. This is a table. Could help put 02 with a customer. I tee off Charl five. So enough words. The customer table will have numbers 12 free for actual numbers. Do you take what two bites to? Still, Where was this? Has a strength trial five, which will take five bites to stop. They're not the same. What happens if you're going to our S s s model? I'll flow charts here on. We change the destination from output 01 What's in or two. Will it cope? That's a look. So we had it the destination to help with zero to look at the map pins. You can see that customer I d still goes to cast a variety. Let's see if it works. Click start. So those are fewer customer i d the 800 or 47 girls close that just continues goes to the end Says it's finished That's going into S s s database within S s M s on and see whether we do have, in fact, 847 roads when we do So, what's happened to you? While there's beings within cold on implicit conversion without those having to do anything S s I s has transformed it's moment into a trial five. That's a look at a bit more detail for go back to design mold now previously would be going to edit or within double clicking on this That's instead writing Click and Got to Shore. Advanced Editor. So the short Vance editor shows the connection manager details about the component properties life Instance. Which open rule set? It is the calling muffins, so so far, nothing new. However, the important helpful properties is something different. So this is our souls output. There's also something called a source era output, but I won't go into that just yet. So if I expand this, we have external columns and help with columns. So looking at customer I D and customer i d. We have a data type off DLL described to a to bite signed integer. So I said that small int was two bites is converted it into its own version off a small int , and it's not putting it in exactly the same foment to bite signed into job you want, but said earlier that we don't really use properties here because there's so many properties here. This is generally while use it, but you can see Phil certain components. These properties can be quite useful in that you don't have to dent double click on stuff, but it doesn't give the entire range off things that this gives. You can see this is a 1,000,000,000 so these properties are helpful, but they're not the full answer. So there are several reasons why I just gently don't use them that much. Back to the point, this is converted into a DT underscored to. Then he gets the destination. If you have a look at the matter data here, you can say it's still a D T. I, too. You can see Boolean and string here, and then when it gets to the destination, it starts at a d t. I to that your import. But externally, it maps it into a string off length five. Now, this might or might not be what you want, and you notice that this is actually creditable. I could change that interest. Ring off lived 10 for example. So if the computers making guesses about what the output should be, this advanced editor is where you can change it. You can micromanage everything. So the computer is doing this conversion from DT I to two DT str implicitly. You don't have to do anything. However, it may be helpful if you have to do things under certain circumstances, you have to change the column, type all things. So whilst it's good to know that the computer can do it you itself, why don't you also be able to do it as well? So we'll have a look at how you can do it in unexpected 14. Data Conversion: There are two ways to common ways anyway, that data can be converted from one form to another Now. The first, and perhaps more straightforward, is this data conversion component. So if I writing click on that and go delete that then removes the connection between the source and a destination, then I can put in the data conversion. So it's going from one type to another. So now let's join the Blue Hope on the blue into this one. Have a cup. So let's see if we double click on this. What we get so configure the properties used to convert the data type for import column toe a different data type, so I want to change the customer i D. And it also gives us options to set the length. Precision scale. Andi Cold page so length. How long string is precision and skill to do decimal figures and cold page to do with strings again. What's type of alphabetic users. So there's your customer. I d just click on that Andi. It gives the output customer copy off customer I d. Now I can change that alias, but let's do that in a moment, so I'm good is currently it to bite signed into your a d T. I, too. So this is a good time to actually have a look at the common data types, which are used in exercise. First of all, you have 1,000,000,000 currency date time, and you have various have a database times with positions with time stamps and that sort of thing, and you have this standalone date decimal 08 So this is a double precision float, so just concentrate on that last word. It's afloat, but all eight. That means it's a riel number as opposed to an integer snowboards. A real number which can have decimal places on eight, refers to how many bites it takes up. So it's a decimal number with eight bites I ate. Therefore, is an integer, which takes eight bytes and they're signed and unsigned. Now you don't actually have the concept that much off signed and unsigned in SQL Server in SQL Server, you have Tunney and Smalling on bigot on off Those Italian is unsigned because it goes from note to 255. There are no negatives. In other words, Wow is small and into big and assigned because small and calls from minus 1st 17 2067 fruit to 32,768 plus a plus or minus one. Think it actually goes that way. But the key point is signed means that you can have the negative. If he was a sprawling unsigned, which has a say doesn't exist in SQL Server, then you'd go from note to 65,535. It's the same data range. It's just where he starts. That's what unsigned and scientists so unsigned into. Jenna is a you right, so we have four bites. We have a smaller float, so this one just takes four bytes, as opposed to the double precision Flautas takes eight. New Rick has its own number numeric. So it's not our anything. And then a single bite. A string that's a frequent off a char onda W String is the equivalent of an end char off course. These could also be bar charts, but internally in exercise, they're just chopped. How to get exported is another matter, so hopefully now you can understand what are for on without telling you. You, I two means it's an unsigned it Joe with two bites and if you don't want a bite, is a bite. Here's a number between zero and 255 which means that a one bite into chair can have a range of 256 different numbers A to bite into joke and have a range of 256 multiplied by 256 which is 65,536 numbers on It doesn't matter where for that girls from that range or that rains that both have a range off 65,536. Don't forget to include zero. That's one of the possible numbers. And so for bit so for bite multiplies at 65,000 times itself. So about two million and stuff off. But hopefully now you can understand what all of these acronyms beat, so we want to convert this into a string because that's where he's going. We want to convert it into length. Five. Precision and skill don't apply to a string if you putting it to the float so I don't need a philosophy. Now my a a decimal. That's when you can use a scale. For example. Yeah, let's take this true string that five on the area. We now have a copy of customer I D. On now. This goes through to the destination. So going to map pins. Andi, I want to map the copy of customer I d into I. D. So I don't want to My original one. I want to map the copy instead. So let's play that it's going into I'll put to So let's truncate output to before we begin . Right? So it's now in test. So play that. So it goes through this one more step. This transformation 847 rolls in eight and reports several rolls out on the answer. 804 7 rules. Except this time we've done the transformation, not the computer. So just to look into the advanced editor for this, a copy of customer I. D. Was a string with the lead for five, and it goes into a string and for five, So we have done our very first transformation. Now, just before we go on that there is a question that you might have. This is called copy for somebody because we just call it customer. I d. Well, let's see what would happen if we did. So we're going to hear Would there then be 201 things on you could see Now it says oily db Source that customer i D and data conversion dot costa Rieti so you can see it's starting to get a bit complicated. We now have the original Coster I D. And we now have this new customer, Rieti. So went into converting data. It's best to give it a new name. A new column name of eyes. Don't be saying well, which is it that we really want to refer to? So yes, you can, but you might not want to. 15. Derived Column task: So I said in the previous lecture that data conversion was just one way off changing good data type. So let me show you the other. So if I delete this, obviously the connection between source and destination, which was through the data conversion component, gets lost. The other is derived table on, def. You have a read of this. You can see that it allows transformations, which are more extensive than the data conversion. So derived table is one that's used more frequently. The data conversion, especially as you've seen that there are lots of implicit conversions already done. So has joined these together. So it's going to be derived color dialog box. Okay, it looks a bit complicated, but it's actually meant to be simple. What these are are your columns. We also have a bit for variables that we haven't done variables or parameters yet. And then these are all your operators, your friends, your functions. So you don't have to remember them all now just looking at them hopefully thes look quite familiar to you. Andi, if you have have gone through my T SQL courses or know how to use Excel whole have a decent will be familiar to you. You probably knew No, but now you know what the data type is. Oh, use visual C sharp or visual? Basic. And then a lot of this should Well, now be familiar with you. The reason I mentioned C Sharp that is one big difference between C Sharp and languages such as visual basic Onda. That is, days in visual basic. We use an equal sign to be both a comparison and a set up to say that in very old basic it would let my var be equal toe one. Why was C Sharp doesn't use this technology, but if it did, it would have to equal signs in the comparison and one in the assign er so you can see equals that he has got to equal signs. But there's no assigning here because that's do variables and self off. So just be aware that is no equals. It's double equals, right? So let's not get this thing down here. It looks fairly similar to the data conversion component that we saw earlier, except it's not quite as guests working. You don't have your columns with tick boxes. You have to actually perhaps dragon down. So his your column, my customer, I d. And you could see just dragging it down didn't work. So the drive to column name Customer I. D. Hand. We're going to replace how existing customer i d. With that. So the expression cause variety. We're not going to do any transformation over the just assigning the expression, the data type. But this is when we could become converted. Exactly. I can't. I can't double click on this and do anything but not is what we've got here. Type casts. So this is where we can convert. So if I want to convert it to a string, which we now know is this, I can put this in front of it. So let's do that. Rocket DT scope the length. Five on the court page just for memory. I think it's 12 32. I'm wrong about that on. Indeed, you can see I am wrong because the expression is in red, which means something's wrong. Then you can see the value 12 32 specified for the call. Paige Parameter is not valid, says he told 52. There we go on and get out of that and It's now read does not miss now black. It's no longer read, right? So has it worked? Let's find out. So we got this customer, I d. We've replaced it. We replaced the customer idea with the new custom Righty. So it's gone through. Let's see what happens when we go into the advanced ator. We've now got the customer I d right to the bottom, which is on i t two. Well, the reason for this is because we have kept the old name we're replacing. This calm is expecting that the newcomer will have Basically the same data type is the old one. So instead, what we can do What we should do is that this is a new column and not is instantly the data type changes. So when you to replace is a cost a customer i d, which is a small into a to bite signed integer then that's what is expected. The apple to be that as soon as it's a new column, he becomes a string we've lent for five. So we call this customer I d new, please. You're asking Okay. This tour for two. I don't get it. How did you know about that? Well, let's have a log. Had the meta data from the source Look at any of the text fields and you see that they have a data to call Paige off 12 52. They see Sosa things that you just memorize eventually. What it advocacy. All it means is that it's in the standard Latin, one cabin to pick map. So there we go. So now if you're going to the customer destination, you can see that the old custom ideas do being month. You can change that to the new customer. I d. The output stoop becomes customer i d because that's where it's going. Analysis of Look, a meditator customer i d. New is a string of Lend five, and it's going to a string of Flint five. So the derived column is more powerful than the data conversion. But in Neiva, would I suggest you use it to convert a column off one name to a column off a new data type of another name, just in case you have this problem. If you don't have the problem, fine. And as you can see, there are so many more things you can do with a derived column that you couldn't do with data conversion. He looked at all of these additional functions, and we'll be using these functions in the future. Don't you worry. So this is another way off getting this conversion. 16. Practice Activity - Altering Data Type: right. It's practice time again. So let's see what's in practice. Activity number two. So, first of all, we're going to use your existing practice activity. We changed destination from the terrible results. There are 12 results. There were two and we check that the implicit conversion works. What's implicit? Conversion? Well, if we just have a look at the table address has address idea of small int. Why was result to his address? I d off char five. So to make sure that that simples ical version blacks on, then add a data conversion. All a derived column component to change explicitly. Address I d to a DT stra five. Okay, so that's your activity. Give you a few minutes for you to do that. And then now, due to myself, and you can see how we compare, right? So as I said, the problem is with address I d. It changes from a small into two hr five. So first of all, I'll save all it doesnt said before, if you got it right in there, no problems. Feel free to skip the rest of this. So I go into my practice activity, which is the one I was use together. Take that away that you know Well, because the first thing I'm doing he's changing This too. Was old told to There we go. There we go. Yes, it does work. We've got tix and ticks on going to this one. It's probably coming, so But it does open. Start from this out. Yes, 450. So we'll just truncate the table. And soon, actually, how to do that from actually inside as this eyes truncate table No rose. So go back into visual studio Bates. Andi. Now get rid off this connection. Try this down. Lowy. Andi, I said evil. A data conversion or derived. Com. Let's do data conversion. There we go. 12 Uncle. Averting address. I D to copy of Address Idea. Why not? Under all market a DT. String five on and in the destination. Sort of using address idea. We use copy of address. I d onda should not work. There we go. Go back into here. See you over. That looks. Yeah, on the auction I give you was Jesus. Arrived column Dr Color, New address. How does a new column and we're converting that using the type casts. If you're converting it to W string with Popsicle, remember to That's fine as well. Actually, it works that there's no red going to hear no longer have copy off I d. Now new address I d. And there we go. We should now have 900 rolls. How do you do? So that's the practice activity done. 17. Aggregation: now to this point, we don't e extract t transformer hiding derived columns are changing the data type and we've done hell Note. For instance, we've extracted 804 7 rolls and we've lorded 847 rolls with To my mind is not very transformational. So let's have to do some real transforming nt SQL you've got something called Grew by which summarises data. You often use it with some average count. And you got that here as well, except because we're speaking a different language as you solve all of those data types. It doesn't call itself group buy. It calls itself Aggregate. So you can see down here aggregate state over function Such his average some count Max min , for example, group data to compute the sum of sales per product or the average visits per day. So, first of all, let's get rid off what we've got apart from the source. So I highlight thes two components together. Press delete. That just leaves us with the data cells customer. Let's put agree it. So those of you who know my personal that is a Sigma sign, but so let's just move it a bit. I want hand connect the two. So let's Iwas wrong selected least one column for the contract legation. That's fair enough. So configure the properties used to perform group operations. So let us have a look at what we've got. We've got custom I D. Name, title company, name, phone, middle name, that sort of thing. In this example, I want to have Look at sales person. Andi. I want to have a count off the sales person. Let's take that. So so is person input sales person out by Operation group. By all, you could counter, you could count distinct. So that's the first column we want out. And then we want everything else. I'm just counting. So you'll have seen in T SQL that you could do and I'll do it for this one. Select. So it was frozen group by sales person. So this is the output we should be getting. So let's he won't get Andi. I'm going to say this into a destination off output zero free. I think it together. See what things are sales person on and so you can see e put a column name of council, so put that into sales person count. So go back to Yeah, this is Dennis on. We'll see that there's nothing on my sleeve. Nothing. It help with your free. So run this. See what happens Goes from 847 rolls. 29 girls. Let's see what happens. There are nine girls. So that is how you can aggregate all to use group by very quick. Very easy. 18. Multi-cast: now, I've just had a data viewer to the first connection. So if I run this, you can see that our data concludes, Mr and Mrs So what I'm going to do, first of all, is I want to be able to export this data, not in tow. One destination pressing to to Currently I can't do that. It's just going from the top going into the next component and then going into the next component seriously wanted to go into two compartments. How you can do that by using the motor cast distributes every import roll to every rule in one or more help. What's so we can add a motor cast to here on. Then we can add to destinations So we have one over here, which is going to be, which is good to be Output one. And then I can put a second destination assistant Over here. Onda. We use a second blue connector. Make sure it's believed I'll explain all of the different types later. Toe output zero to You may have seen a red connective instance at some points, but we're just using blue connectors. So it's that now transmits 804 to 7 Rules for the souls to the mortar cast on then 800 afford seven rolls. Go fruits, his destination on Day two, the second destination. So that's a look at the map. Ing's okay, things work, and the reason why I say Let's zmapp is the cost of this X and says the number of input columns cannot be zero Sort of. The problem with the wrappings so soon to the click happens. It really evaluates everything right, so that is going into output to tended into output one. So let's truncate those put two. Put one. I said Nothing in that. That's don't so 8 47 girls into their on, then goes out into both adults. Andi, we could see. Just show you one of them. Execute that 847 rolls, so splitting your import into various branches off your floor chart can be quite useful, because it means you can do multiple things. For instance, suppose wanted to aggregate and put one set of aggregations in tow, one table on one set of aggregations into a second table. Well, that's doable if you use the mortar cast now, just in case you're wondering well, couldn't we have had two oily DB sources going into to destinations something copy paste something like this. And so no lead for more to cast. And the answer is, yes, she can. The problem with that is now we are reading the data twice, and that's fine when it's 847 rolls. No so fine when it's 10 million or 100 million. Ideally, you want to do each operation once, so you want to just read once. And of course, if you're reading twice, that's twice as much memory you've got to do. It's simply do you think this will work? He does know connector between this sauce in this sells. Let's just try it. Yes, it is. Yes, it starts at the top of works its way to the bottom in both separate contains. So not everything needs to be connected to everything else for things to work. But so the problem with that is to say, is that it needs to read things twice. Eyes. If you use a mortar cast, you can consenting to to oh, what derived. Come over here concert into free. You can insert into how many you choose to. It simply generates more connections, so multicast saves time in reading data and then transmitting it into separate operations. 19. Conditional Split: But sometimes we don't actually want tohave an entire copy. The data 847 data going worlds in going into two different dead. It's offers, maybe want to split what comes in here so that let's say one table has all off the misters on one table has all off everything else can remember what they've as, well, there's a miss on and I think there's some nose. Andi think there's also a senorita. Let's just have a look around, Mr Miss Few knows. And somewhere down here there is a senorita area. Chelsea Miller. I don't know if you Spanish or Italian, so want to split it into, say, all the ones with Mr and everything else that we can do with the conditional split roots data rolls. Two different outputs. Cesaire help What's input outputs, depending on the content off the data, for example, separate records that need to be cleaned from those which are already ready to be lauded. So we get rid off the mortar cast on DWI, insert a conditional suppression step, so I don't look Connexus. No, since we have one blue connector, which is the standard output on one red connector, which is the error? Help put. Still counting that Andi. Then there's ever ALS. But we haven't had no needs to be 1/3 connector, which is the rest. And we can't do that yet because we haven't fully customized the conditional split. So let's go into it. So this looks like what we've previously had. Just a bit smaller. We've only got three columns here, so let's see what we got. So the first thing is, I want the title to be equal to M R. Full stop. So that strike title there on put equals him. Stop. Okay. Can you remember what's wrong? I have locked. It is in greatest. That's clearly something is wrong. What's wrong is that this is a C sharp type language. We need to equal signs for equality, not just one. Right. So that is being output id too. Title is Mr, but you can see we can't do that. You can't have four stops and fluctuations in fields so doesn't like so everything goes goes into this default are put, so I'm gonna call that tape is not Mr Okay, so let's get rid of this. I'm trying it. So what do we have here? Well, the computer doesn't know what help what we want. So it gives us a dialog box to tell us. So the output we want his title is Mr So we'll rename this. That's right, including gold rename too, Mr Table and again you can see we can't use punctuation. Let's do that. Okay, Just just move this down so you can see more clearly that we have this title is Mr Annotation. So now we use this second connector and you can see it automatically say its title is not Mr. We still have the era connector. But we're not looking at the error connectors just at the moment. So if I rename this so the Mr Table just remind you is output one and we have outputs to hers. Did not Mr. So the trunk troll caked these tables again. Run this and notice how many rows goes into Each wouldn't know That's a problem package executed with an era. So it looks like it should have worked, but it hasn't. It stopped a conditional split. What's going on here? The answer is that the conditional split has literally encountered some parents. So what we have to do to get this conditional split to work is to do some error handling 20. Error handling: our last video raised an interesting dilemma. We have an error. We don't know what this error is, Andi. It's very hard to work out what it could be from worry at the moment. Now it s a size is all about adding automation, and it's no good having an automated process that could fail. You went from the top, went to the middle. We didn't get to the bottom. What if this is happens on an overnight task? 10 million rules that you needed to be processed by the next day and aren't who did? That might be your job on the line. So what we need to do is to look hat, not what this particular error is, but how we can handle errors. Generally, let's go into the conditional split again on. We can configure the error output so we have. What happens if there is an error in the role? What happens if there's a truncation error? So truncation error means For instance, we have My name is Philip starts at about 20 characters, and we try and put that into a bar chart. 10. That's a truncation era Satyam. Or was it set upside TV for those happen, fail, fail. I fail. It means we just stop. You don't continue. But we have alternatives. We can say we're going to ignore the fact that this is failure. So it never happened. All we can do something else called redirect role, but let's have a look. It ignore fairly. For now. Now I'm fairly sure that what's happening is not a vocational, but that's what and we can just to see what's going to happen. So now I've said, If you remember, I said, Ignore the failure on did no works 800 afford seven rules. 490 goes into mr 357 goals in two. Not Mr Now I could open up these tables and see what there is, but instead I'm going to do them or I s I weigh on at a date of you. So that's what Look and see what these date of your show. So he's the Mister Data viewer and we have all of these misters. That's to be expected. So it has pleased all the misters out on. Then we have this not Mr On. We have means Andi know. And so where we'll have a signora that's a senior, so we have to be fine. According perhaps, but he works is what we said we're going to do doesn't have to explain what the error is, so let's go into 1/3 connection. So instead of saying we're going to ignore the fairly, we're going to redirect the role. Why do we read out at the road? Here's where the Reds comes in. We read out with Rule someone else. Let's just see what they say is well centered. The error output will be lost. Okay, so if you don't use the red, what happens? It still works. But notice we have 804 7 rules going in on only 840 going out. We have lost those seven rolls. It's very wise. Fel it, we want right? So let's put this read somewhere else. I could do a new the table, another oily DB destination that I'm not going to. What I'm going to do instead is at a derived column. I'm going to put us into their So these are the redirected roles. Now one of the great things about the Derive column is that it works as soon as I plug it in. It's a link up. Press stalled. It just looks in a Ford. I don't need to do any configuring to the derive tape column. I could put something else after it and we will see that no transformation happened. So what's the point of putting something? Why nothing's gonna happen? Because I can enable a data viewer on That is the good thing. So you got one data viewer on Mr One date of you or not, Mr One date of Euro on this era. So without this derived color, got nothing that I can put this to. So derive columns are great for the booking. Yeah, I don't need it. Trustee, create a new column. I'm happy with it to create zero new columns. I'll just put the date of your on there. So let's see. Hopefully this will contain the seven missing good roles. So just get these data views out to the way. So 490 plus free fifties 8 40 plus seven 847 So, yes, we have seven rolls which were the problem. A number of the death of you. You can see that all of those are the ones which hard no says other. Any nose left in this not Mr On. You can see the answer is no if I sort it. See, we have senor and Senora, but no nos So what's it all without telling us? His 10 *** that this traditional split fuller title equals Mr Fails on nos Interesting. How do we get around that this nose functions will help. So if expand that we have a replace? No. So this replaces any nose with another expression. So how will that happens? Well, if I replace what's entitle if something else if it happens to be no right. So what happens if taxes Mr. While it goes into this function it say's is title? No. No. So we'll just output title. So it's his title. Mr Equals Mr Is Equipment to Mr So that's fine. What if title was myth? Well, the this replace know his time shall know No. So they are putting this function is miss is miss equivalent to Mr No Now what happens if title isn't? It goes in this function says this title No. Yes. Then replace it with not Mr is not Mr The Equivalent off, Mr No. So it goes into the not Mr Now a detectable not Mr here. I could put whatever I wanted to just as long sighted put, Mr, because I don't want it to go into the Mr Table. Even typing There were no liver wanted to. That's a string is supposed to the condition of what type of something that is a big, more explanatory. So now let's see Fowler Conditional formatting conditional. Speak works. So we only have two debt of us because nothing went into here. We have 493 157. Andi, if I run it again, we will see that there are indeed in here. Nos on. Then finally. Finally proof. I'll just truncate these output to one out of two tables. Run it again. Play. Okay? Oh, just close it on. No, they should have 300 57. Except for twice. Apparently, Andi, This hat should have therefore net Nitra native on it twice accidentally. But you can now see that it is working as it is intended. So ever checking. Well, no, it's ah, it's tricky. Speak our checking. Pete's anticipating failure. What? You wanted to do. If something fails, it might be a one million chance. But you don't want the success of what you're doing to be hampered by one in a 1,000,000 chance. I won't be doing too much with error handling. This is going to be the main Electra part up. But when you're doing this in real life error handling, it's so important off wise, you'll find that when you stop running stuff, you get failure. You don't know what stupid. It's just the whole world. Get rid of the replace no very hundredfold. If something goes wrong, you need to be able to be able to manage it. That's everything. Whatever handling is about. 21. Row Sampling and Percentage Sampling: this state of you going to derive color is so useful that I'll be using it in future lectures. So don't be worry. Different particles arrive column on and not actually explaining it. Who we saw that I can check the out. But now we got 847 rules here. But sometimes, of course, quantity does not equal quality. Suppose you had 10 million rolls on. Do you were trying to just look through that? For representative samples, it can be quite too much. What you can do is you can trim it down to a small a sample, and there are two ways you can do that. You can do it and you need to look in under. Other transforms for this by rule sampling or percentage sampling. So roll sampling as you can see samples a specific number of roles. Forman input. So if you put this in config great, it by default will get you 10 rolls. I'm going to be such 25 on. You can see that we have to output. We have the selectors outputs and the UN selected output. We have the standard columns, so let's hod two derived columns this one is the selected on this one. Is the UN selected Andi That's your sevens. So 847 going 25 goals into selected 822 girls into UN selected. So let's put on a data fewer, but only on the selected because it's provides consequence. A few problems to have to debt of you is open. So here you have one on we start off with Just have, like a custom I d to 90 two nights simple 029 6/4 sample and then number four. So it really is random. If I do it again, remember number four with that last time? His number four parts at this time? No, of course I couldn't be sure until I see Rather it is his random number four could. Well, having that this time Now, if you want a random sample but not so random, you want to be able to repeat it. You can specify the random seed and have a look at this using the same random seed on the same import data always generates the same sample. So using for development and testing package Absolutely. You don't want to do to real life. So now let's look at these random Morales on. You can see it starts suffered run over 40 and if I do it again, you will still start off at Rule number 40. So that is just the random sampling. In a nutshell. We also have percentage sampling, which instead of saying 10 rules, we won't say 10%. But I'll leave it for you to have a look at that in one of the practice activities. For now, let's go on to a different practice activity. 22. Practice Activity - Aggregation: right. I'm going to test your memory a little bit because it's darn good means showing you something and then you repeating it immediately afterwards. What happens if we let a little time go pass? So practice activity number free? I want you to group the address table by country by state and save the results in the results. They were free table, and that's all the information I'm going to give you. Good luck, right? Well, hopefully you got through. That's okay. If not, let's have a look at it now. I said, Group off course, it's not actually a group. Here it is, and I could get So we set up our data source on the address. Onda. We put the aggregate into that. Now previously will only aggregating by one field have asked you to educate by two. But hopefully you'll have had no difficulty that because this time it just clicking two things. I want you to do it by country on my state and then I want you to count them. So a group my country by state now, if you didn't count them, that's fine. I don't think I explicitly asked you to do that, but that's always good on. Then we put it into a destination, namely results for free. Look at that happens. Always look at the map things so you can see the input column is not exactly the same destination. I just needed mapping. Let's run that. There are 450 rolls goals into 25 rolls, so I could have added a date of you. Oh, I could look it up. In here on Dhere are 25 rules. You know that it is not really salted in any way, but never mind. It's done exactly what I asked you to do. 23. Sort: now this is the results off the previous practice activity, and I said right at the end of the lecture. Is that the reason it's not really salted in any particular order? So we changed that. If we go back into here, he's calling out E T. How so? That's had a new over L. We are going so never t we're going to transform before we get to the destination by salting it. And frankly, this is something in which at least speaks the same language. It is called salt. So let's link the two. There you go. My insult. The number of import columns can't be zero. What does that relate to? Well, do I have to do after going to mapping? So if ever you see that we have to go into nothing, right? At least one column must be slighted for sorting. Wasn't thoughts us all. Me right passed through means that the column is literally going to pass through. It's going to go through the import and out into the output. Now what's out you want to do is to salt my country region and then two sold by State province. So I consulted, sending garden sort, descending it salt in this particular order, going number one and then number two, small country first and then state province. Not that this thing at the bottom. Remove rows with Juba foot. Salter values so in efforts. If you had to rose with United States, California would only retain one of them. And also, there's this comparison flags. Let's just have a quick look at what that is. Ignore case. In other words, open gates located doesn't matter if no kind of type. So that's especially for using the Japanese character set to so much of love. It's hugely gonna or cat. Agana ignores non spacing characters That's to do with accents. For instance, a acute is the same as a ignore character. With that doesn't reply have been Western languages. It's whether it's a single space character, a double spirits character signal singles. You're symbols like punctuation and stars, and that sort of thing on salt punctuation has symbols, whether it goes before letters or not. Most of time, the only one you need to look at his ignore case, right, So that's quick. Okay, it's exit. Andi, I'm not going to get it to the oily destination. This time I am just going to use my trust. Your favorite derived column hunt a dent of you. So let's see what it started. And, as you can see, it is now ordered by country by state. Just one question. Let's not do the aggregation. Let's just go through two sold straight away. This removes rolls a budge with Jupiter, so values what would have happened so well. Festival. It's saying that that we had a column called Council, which is not be previously used. So in affords he's got the wrong meta data. It's got the wrong columns. So if we show the advanced editor on the input output, you can see that the input columns are count all. Whereas if I click fresh, which is down here, so for close that to look at this eggs. This is the first time we've seen an X here on the floor and destructive, but it says there are some era mapping mapping hours on this path. We can resolve the references. I see what that does. Onda we have. These are the ones which are much these other ones which are unmapped. So now I want to map thes into why this should go. So really, it's having a really hard time. It's not doing anything. Bless automatic columns. See what happens? No, nothing. So what can we do? Well, let's do it that I did it again. Not still there. Now, look, is it now? It seems to work. It's just that we got the count all as it passed through, and that's not good. So we've now got an X here. There are some mapping hours, Andi. It's difficult to work out what to do, since if you click resolve references. It just tells you that there was a problem. So what we have to do is going to both those components and have a look. The problem and the problem is in the salt. You can see if I extend this downwards, then we still have count. All has an import calm, but we don't want it. And we're not passing through all the others you want to do. So let's see if that's yourself. The are looks that way, So kick start. So there are 450 rose in 450 rolls out. Why couldn't we see all the columns because the data viewer doesn't have those columns. So if I edit it, got a date. If you are, you can see that it is now. Risk us all those columns, so sometimes you just have to go into stuff just to allow me just a time to just work itself out. If you change the import, you may have to go into the component going to debt of your to get it all working. So now you can see it's working. Get it gives us for all better. It gets his 369 13 or so Rose. But what happens? This is the main point apart. Former. Show you what happened when you change the component it. But what happens if we remove the girls with Jupiter itself? Values have a look. Well, now it does literally what it said it was going to do. It has removed all but one off the Alberta addresses. Now, which one is Children? It's not exactly random, but it's not necessarily something you can predict. It's not random, according to the computer, but we can't necessarily predict it. So do be careful. If you are using this tick box, remove rolls of duplicate sol values. If you are passing through over columns, you may find that what you wanted instead perhaps, wasn't aggregate, but then sort it. So the salt coms just to go back the salt transformation and said the sorts component allows you to salt certain columns in order and you choose which the older is and allows you to pass through columns that you don't want and therefore, by extension, any columns which you don't select passed through well, not go through to the next component. So that is what sources doink. 24. Adding an Excel source: Well, up until now, we have used the one source. This one comes from SQL Server, but there are many more sources about if you go to the s s I s folder. We have a sub folder called Sauce Files, which probably contains many more than files for this. But I just want to concentrate on these two. To start with, we have a Microsoft Excel workbook on. We have a text document. So let's have a look at how we can use this data in the in our data flow. So let's just zoom out. You can see that we've got this soon. That's being very, very patiently waiting first so you could zoom out like that and then soon back 200%. So we need a new sauce. Now I've got to use the source assist. But there were over ways of doing this on First of all, let's have a look at our excel. So click on a new collection manager because we haven't got any Excel to connect for managers on defense in his 10 years. What's the file path? Well, let's going to the source files on there are import zero now the Excel version. The computers automatically changed it to a modern version in 2007 to 2000 and 10. It also knows about 13 4016. But all of this depends on what version off SQL server you're using Now. The first role has column names quite standard with spreadsheets that you have header roles . If not, you have. Don't take that. That's quick. Okay, Hang, Take. Okay. Practice. So there is us Excel salts, but still nuts, right? The destination table name has not been provided. So in other words, we have told it that we're going to this workbook but not told it. The actuals bread chief. We're going to so table or view hand the actual table. She $1 she to Donal, she free dollar. Now the dela means the entire especially the entire table as opposed to and named range. So let's click. OK, lastly, that's going to columns. Let's see what columns we've got. Hey, could see customer I D names Town Boulder. Familiar customer columns. I'm not trying to do unusual data sources for you because managing data sources isn't really the point of this. It's actually doing the floor chuck and work out or how all the components got so I tried to use the same sort of data in each of my examples. Ondas Usual. We have the air output in this time. Now we've had to look at the previous our output. You can see that when it tries to convert customer I d into what is tragic averted to it. Can I have ignore the failure or redirect the rule or feel the component so you can do that for every single column on What do you mean convert while If I go into the show Advanced editors and Input Output properties, this is what it believes. Each of these are names. Town is a double precision float, which is a bit much, perhaps, for a column that just contains the number zero. We have customer I D, which is a double precision float again. That's a big march for something that contains into GIs while small inns on. Then you nickeled strings off length 255. What it's trying to do is give the maximum degree of flexibility, and it's over the top. If this was a real production system, I would be telling it. No, actually the date ourselves for the phone. It doesn't want to be unicorn unicorn. You don't want to be Unicord. It wants to be a string off signs, whatever and give it the maximum size. But that's only something really need to dio production machines. Andre Things, data sources with huge imports. So there is our data source. So that's how our data viewer Onda We'll see what we got. Who? It's not worked. How does it know what? Andi, It's not giving me any clues, Ryan. 12. The coup is in the progress, so let's have a look at all of the exes on this is the major one, the requested oily Devi provider, Microsoft dot a CE. The oily DBT 12 0 is not registered, so that is the problem. If the 64 bit drive is not installed, run the package into 32 bit more. Okay, so now what can we do? Let's stop this, I see. Let's do what it say's run the package in 32 Big Bold we writing click on the package Andi So this is the proper the project Onda go to properties. Andi in the deep booking. We have run 64 bit a long time, especially find some of the projects started 64 bit. Okay, and he's called the true. Let's change that to force. See if that makes any difference and you can see from the progress. Yes, it does. Andi, you can also see you now. We have ticks. So it wasn't Earth. Is all of this about? Well, unfortunately, Oh, perhaps fortunately, technology keeps improving. When Windle's 95 came out, it waas 16 bit on 32 bit additions. I think it certainly was a major step up from where we were before. More recently, when we got to have one or 8 4010 we started getting 64 bit machines, and advantage of 64 bit machines is that they could take advantage off memory beyond free and 1/2 gigabytes. So if your computer has a eight gigabyte ram, for instance, if you had a 32 bit windows, he couldn't use 4.5 gigabytes about. But thankfully, nowadays, 64 bit windows is much more prevalent. Unfortunately, as you've just seen, the connectivity between programs doesn't always work on 64 bit mark, at least not out of the box. So if we change Hispanic to run 64 bit runtime is true, then we have the problem. So we've been able to find out that there was a problem. How do you get around it On the the really important. But it is this paragraph. If you put us in Google, you can see that the 2nd 1 we got is exactly the problem we've connected to excel from S s I s on the solution. He's a very bizarre. You have to download install of the 32 bit version off Microsoft Access database engine 2010 run 20 to 10 run time. You could see the reason on the screen. It's a really, really bizarre solution, just complicated by the fact that it actually works. So if we don't know that and we have to download the 32 bit no, for 64 bits are not back 64th. So this will install my computer. Hey, totally unnecessary program. I'm just going to close out off official studio while it it's doing it. So it's a good time. This allows you to run Microsoft access databases without having the full version of Microsoft access in Andi. The reason that this is a solution is because thes contains the translation that Microsoft Excel needs when you're really get school serving 64 bit. Now, if all of this makes complete got to give it to you, it does to me as well. Except this is a consolation to a common problem. And if this doesn't arise in your computer, just be aware that if it does on a later computer, this is the solution you instill, they completely unnecessary program. We just make sure that we are in 64 bit more than we are. And now, when run it and its students work now, you should work on your computer. It could be that there were a few too many programs that conflict. We've also just don't which I mean that can't work on mine. But he should know, walking yells. So now what I'm going to do in this sometime in the short term is change this back to 1st 2 bit on start. So the only reason why feds tube it it's a problem is because I can't handle off the computer. Can't handle more than free and 1/2 gigabytes of memory. There are some limitations, however, very knowing you feel to get around this. Don't save it in the 7 10 24,004 months. Easier said than done sometimes. But that is how you can use an excel cells and how you can. Hopefully for you, it's happened to it succeeded in the past overcome one of the most common problems. 25. Add Text source and Excel destination: now Excel sources aren't the only off a source of status that we've got. If you have a look at the source assistant, we've also got Oracle. Well, I don't have any recall. Data sources toe haven't. But maybe you do. If you do, you just go into the collection manager on. Then you log on exactly the same way. And so we've done. We've SQL server or flat files. No one Earth off flat files. Well, they are files. Okay, I know you expect to me to see what your flat which, uh, two dimensional text files comma, separated values, styles foul, separated by taps, that sort of thing. So we call this a text file import on click. Browse Onda. We can see the sources text and see SV. Yes. So good to use this input. 02 has R Texas and you can see how it's going to be delimited. Either is going to be having the limiter like a tab. Oh, it's Those commas are often used or it's going to be fixed width for matter. Quite often that happens ragged, right? Have not often seen. But really, that's on the end of a form of fixed width. It just means that the writing loss column has a available. So really, you got delimited or fixed ways. So if you go for delimited, we can see that the deal emitters for the hetero carriage return on line feet. There are overhead roles, but generally you won't need to. Why too much about this? This one header rows to escape? It could be that you have an introduction to your text founder. This text file was imported at so on. So on was done. By this, you might actually have an introduction. Well, if you do, then you've got the option to skip, say how many of them in the head of runs there are. And then finally, we have got this column names in the first data roll again. You couldn't say whether it's Unicord or not. Unicord obviously is. If you've got it outside off the Western European languages, the passkey set off 256 characters. The columns are not defined for this connection. Manager hand have not said how is delimited were both of those are don't even comes. So here, we say, is the limited by on the computers guest taps on. That is indeed the case, and you can see what a previews like. Alternatively, you could have said it is a fixed with Andi. Here are the with hand. You could then say I wanted this to be the width and you could see right and, um, our market click Delete or double pickle marks will move it or moving marker by drugging it . So anyway, we are delimited in this case, it's going to reset the color details. Absolutely. That's exactly what I want. And here you can see the data. So we have this flat file source so we could call it a text file. Now what if you want to pull, Let's just make sure it works. First, we're going to track this into their had a usual day. Two of you are on the run Onda text files from tough. Any problems signal like the Excel files on there. You can see the data 15 rolls Now what if we wanted to export that to his perch? Eat well, we'll get rid of this derived column. Does not actually harming anybody. Andrea put in a destination assistant and we have an excel gestation time. We don't want the destination that we had previously, which was imported one I'm going to create a new one had a gun to use 90 Santa tube of 2000 free, which is the dot Alex Hess Sadat x l s suffix. So I'm going to call this and I need a foul puff, so I'm going to call this output 01 dot Exe a s. I think these two together this X should disappear if we've got the table name which haven't so need to never table sheet on the aren't any tables or views So we need a new excel tables. So we're creating a table in pseudo SQL called so please, like the new Excel sheet from the drop down list. What's there you go. So Excel Destination is the name of the news one on the muster columns Automatic. You don't. But you do have to click this map. Things tap quite a lot. Okay, so now does the X disappear your time to disappear? Custom. I d cannot converting Unicord and non Unicord string data types. Okay, so we've got a unicorn data type and a non Unicord. Let's see what we've got. So we opened up the advanced editor and customer idea was talking about. So the imports. That's to say what's coming from the text is being translated into a string on the output is being converted into unique old text stream. Lovely. So let's convert this into standard strength. I don't see convertible the others into string as well. So I'll just do this for all the columns. Just pulls the video. So what would have to do is not do a straight translation, but to add in between a data conversion color. So we had to look at the data conversion a while back. So just tickle of these on do we want to change him from a string steam to a union called String? So just do the job down. Just type the letter U and the destination going to the map ins. Andi. Instead, we are going to use copy off for all of these groups of done the wrong one of them. This shows the problem of doing its layout of older always do top to bottom on. Now we need the X has disappeared. So now that start took and we're going to our for and we have help with 01 dot xxx so we can open that something excel and hopefully that will give us the 15 girls. That's would that the area. So sometimes, when converting between different data types, you may have to put in a data conversion. But the easiest way to find that out, it's time to try the thing and see whether it froze up in ever. 26. Practice Activity - Percentage Sampling and Conditional Split: right. It's that time again. Another practice activity about something that you may have forgotten about. Bathroom. This is one of the best ways of turning. You get told something. Then you got to something else. Then you get reminded about what you were told that you might just forget all about it. Let's have a look. See what this practice activity is all about. So, practice activity number four. I want you to get 10% of the roles which are contained in the table address on, then split them into two outputs one for US addresses and one for non US addresses and use data views to check your results. OK, but look, right? How did you get on? So let's have a look and see what I didn't. So, festival. We're talking about the table addresses, so we've gone back to our as his eyes. SQL server on. We're looking at the table address. I want 10% of that so you could manually work it out and say it's this number of rules 45 years I think it is. Or you could use the percentage sampling. So that was something which I said earlier you were going to have a look at. So we want 10% of the rods. So he's going into selected and on selected. Now all I want is the selected because if you remember, I want to split the 10% of the rolls into two parts, one for US addresses and one for others. So how can you do that? Split using conditional split. So the conditional split from the selectors how put I wanted when the country or region is equal to double equals marks, I think it's United States could be wrong, but we're gonna find out. So that is going to be us. Help us on. This is going to be nonetheless now. I could configure the air output and in fact, it probably would make sense because there may well be some nose. So I'm just going to say, Ignore, figure. That's the easiest way on. Then I want to use data viewers and to use debtor views. I can use a a couple off derived columns, one coming from us, one coming from the non us and I'm not connecting the hours because theaters are going to be just it. Note. No. So let's had a couple off data viewers and see what we get on Dhere. Our interviews. We got two months. That's always boarding. Well. On one of them is the country United States, 29 rolls. And here's enough warm 15 rolls, most of which are Canada, one from the United Kingdom. So that's 29 to 15. So because it is random, if I run it again, he wants 29 to 15 year olds say this case 22 to 24. So that is how to do this particular practice activity. 27. Union All: now previously going back to where we were before we did that practice exercise. We had created a X cell connection, which is test 01 And we had created a text file connection, which is imports here or two. So he was there one and imports Onda. We use thesaurus assistant to put them in. So now we've got data from two different sources which are compatible. They are basically part of the same table except the different elements of it. So if I was to get to derived albums hard data viewers on drawing them, it's telling me that I have not gone to the mapping. Okay. Oh, haven't specified the sheets as well. God columns. So we've got she could see two separate views off different parts of the table. Gots rose six through 19 orchestra rides six through 19 and 20 to 41. Now we can combine them together. Andi previously reviews the multicast to make one salts going to several outputs while we can use the union all to get more than one output into import into one output. So the multicast tickles downwards split into two. The union all gets more than warrants combines this into one. So let's other the union all into this. So put one input there wanting. Put that Onda and see what the output is. We put derived, coming. Did data viewer on start. So see what we get, Andi, you can see. Oh, now I can't see the problem. I felt that this was relation to this because it's a rolls. But no, I have taken that date of your off. There's only one date of you got April's coming in here, 15 coming in here. But on the eight going out there's a problem. Let's find out what the problem is. We can't figure the union all Andi Oh, my goodness. The imports are completely disjointed. So let's try and joint it. So customer I d. We put custom. Let's just put this data cells back in here and we put this one back in here, see if that resolves it. You could see this input is not working on. When I try and change says manually, it tells me that the meta data is wrong. In other words, is that problem that we had earlier wave the whether it is Unicord or not? Andi just to show that through Excel has converted into unique old strings. Andi, the text file has been converted into strengths. So what we need to do with that? Full. It is a delete on delete. We need to add some data converters. Now, just convert wall of them the Excel, or convert that into strings on. Then we can do the union join of one and two. So let's do this data conversion. So we're putting in all of these on we're converting them into strengthens. These time. I know that it's not necessarily the most sensible outcome, but just a demonstration purposes. There you go. I have to put a data conversion on this. That has got to do, you know, can get this to work. Still not the silvery. Did I have a message? Field properly. Sites linage. I d on Union one. Okay, let's just have a look at the meta data. Make sure I've got this right string string. Okay. Doesn't know. Strength on these are off course. No notes from we've I got this data conversion, but we've not actually used it. We need to change this to all of these copies off. So thanks um, the easiest way to get into this. You know, I had a new union. Colin, We go. No, this is where we can map Customer. I d name Stale title first last company sales email on from Erica on. Now, if you put a Herman this into the derives column, go on and at Hey, data viewer and I only want to read those columns. Let's see what we get. So those are the columns I wanted to read, and you can see now they are all there or be it that they're in a very strange order. So now what will you have to do is add a sort if you want it in the right shoulder. Right older, I think is by custom. Raidi on did well, then had the derived column. So we had the sort which enables us to get rid off all of those columns we don't want. So we sold by customer i d. And we just called up Customer I d on and at the date of you with that the end result. 10 11 12 16 18 1920. It's in the right older except for the fact that these two should be numbers, but if you remember, we converted them all two strings first. So it's put it right, correct into the order off alphabetical support to New Miracle. So what we could do to get this even writer is First of all, we could use the debt iCal version earlier, but now we can use a derived column I did ARRIVE COLUMN This direct column is a cost copy off customer i d. Except it is going to be converted into and interject for bit into job, and I'll call this customer D. So onda we now salt Not on customer i d. Which is going to be a pass through, but on customer I d salt, which is now going to be a number on. Finally, we get the right answer six through to 29 right? What we've seen in this lecture is that as well as the preceding lecture is that there are problems one using more than one different type of data salts, especially nowadays using Excel data sources. So you may have to have a data conversion after each data source, and you have to get right off wise. If you convert numbers into strings. When you salt them, they get sorted into alphabetical order as opposed to numerical. Arda. So I think this is probably one of the last time that we're going to use an Excel Sal's Andi a tech souls together. Let's keep it simple. 28. Merge and solving Mapping problems: right. We've spent a bit of time looking at Union Old for completeness. I'd better tell you about merge. Merge is quite similar to union Old. Eight allows for two inputs. Junior Knoll can have more than two inputs, but one off The disadvantages, as you'll see, is that both in parts must be sorted. So, in other words, for this to even get off the ground instead of doing the salt necessarily afterwards, we need to do the salt before so a lot to salt on and whole Celtic by customer i d. I know it's a string, but just bear with me and then we'll put that into the merge. So if I sold by that and way is that has the salt Onda, we don't pass through the's. So you are a lease. It is custom body. So told one what we haven't x there. This is a common thing why you change the source earlier, and now it says there's some mapping hours. So what we do is we writing click and resolve references and you'll see that here we have the problem. We have mapped input columns. We can delete thes unmapped import columns by just clicking on delete over there on that resolve. The problem. We're now going to merge. Andi, we don't want tohave. He's going to delete them. What? What I really should delete. It's all of these. So complete thes first few, these are the names actually want. And so we can name that. All right, we can't do that because it's still got the old meta data. So therefore, what I'm going to have to do is delete from here down to there and then had in the names off the fields that now think is crossed. Is it going to look? Yea, so now it is mapped in the right order. So you saw that there was a common problem. The common problem Waas an ex mapping errors on this path and that occurs when things happen. You change things earlier, and eggs expecting the same things happened later. So hard to get around that writing click resolve references. Now that he's one of the main things about this lecture, Deaver is that there is such a fingers merge. However, it's not as useful in my view as union, all because you need to actually have. It's sort of beforehand on personally how it started afterwards. And union also allows you to have more than two in parts. So you used merge when you need to, but most of time use union. Oh! 29. Add some auditing: Now, when you do all of these additions and changes and self off, you really should have a way to log what you're doing. And there are several ways of doing that. One particular one is called auditing, and I'd like to talk to you about that right now. So I just got back to an earlier version. Why? I'm looking at the customer table. Andi. I could export it into a output into a table. I'm just going to have a derived conscious Arcand due date of you, but I'm going to before then had an order transformation. This tells me information about what I'm doing. So let's just have a look at configuring it. So the oldest information that I can add and this is to any transformation I cannot my user name, while not necessarily my user name user name that it is running under. I can add the test name the machine name when it started, hand all sorts of over maybe more esoteric stuff. So if I had one of each of these, we'll see what the output is. It's an interesting Golder. That's it's giving it into me. Never like so these are new output columns that are going to go through that summer chain secrets. So let's see what happens. So this is the data that we originally have. So it goes all the way to middle name. Now, this is what happens after the auditing. So we get an execution instance. Good. Good is a unique number that's meant to be unique. An entire universe. So one good is not meant to be the same as any other good. A package idea version. I d. Okay, those all too useful to non techies? Maybe, but we have the package name that's useful. We have what machine is running under what? Using in. And we have the test name. In this case, it's the data flow, tuhs. So by doing things like this, we can help document any additions to database. So when you going, Where did this come from? The worst answer is Well, I don't actually know a much better ones with while he'd happened from this package at this time. So auditing always good. Another way of auditing course is to add a derived comp and to say that this information was added INTs off such day also actually make it into English. So we had this dr column here. So how did he knew? Derive column. This one is going to be mild on this'll walls. Find me can not very useful, perhaps, but never mind. So we'll hunt the date of you there. We know what Dad was going in so we can disable that date of you so we can see what we know what day it is going in. What data is coming out. In addition to all of that, we also have my new, very useful. This data was ordered to by me strength. Now, let's just see what we can do to expand that while we got these data functions, one of which is get date that returns the current date of the system. Now, I know we have some things like that in the auditing would maybe we want to do something a bit more customizable. So we use the concatenation function, which is plus and so we hunt the get date and you can see that there's a problem is in the wrong type. We're adding a date to a string, so we need to convert this into a strength. So let's had a typecast will cast it as a Unicode strings. So w str drug that in make it a profit letter think thirties of proper length. So our length goes to 50. So that must be 20 characters in. That's now 30 Onda. We start and see what the answer is. Now this is a job. I me and then expand this column and it gives today's date time to tender small places. So that's what you use for actually different than the execution start time. So that might be useful for when you say when it actually was created. A supposed to. When did the package start? So you could use that for auditing how long a particular sequence takes to work. So this is all about auditing on what you could also do. Just for bonus points is to use a multi cast. So one going to the output hand, another going to a second output. But before this second out, what happens? We aggregate the data on the aggregation we want, perhaps is just the execution start time and something else. Maybe so let's had a date of you on that. So what's the point of that what I've just done. Well, the point is the data that we got on the left those are the new rules that you are adding your handing it to the customer table, let's say, or the customer new table. But the date on the right, this is your auditing information on you might want to lock that into a separate source. So you have a second table, which is sold if auditing. So you can tell me now that this package was run at this particular time and you just have a list off when the packages will run. And that also is quite useful for editing because, let's say somebody goes into the left and table, the custom in new table on delete what you've done while you still have the auditing on the right inside in a separate table. So it's good to have backups and security. Yeah, write something in detail, but then write it in summary else way just to say this is what's happened on this particular date so that his orders 30. Adding different destinations: I think we might just want to have a quick word about the over connections that you could have. Andi there are getting a bit more esoteric. So what do you need to use him? To be honest, I doubt it. At least not at 1st 1st of all, we have oily DB. We have seen that with regard to S s I s on. That's principally what it is used for. You can see that we have a jet providers. Well, that's come be used in excel. We can also use it with Microsoft access. So all of those are the Oh, I lied to be flat foul. We've previously seen that they are the text files on bond the CS fees that cover separated vying fouls predominantly video dot net. These are used for programming specifically from anyone using the dot net framework. You can see here we go Oracle and the or DBC. That's no longer much used. So I only use those if you actually have a need for them. Generally used benefit girls analysis services. This is connecting to a cube. This is connecting to S S A s. So if you start using cubes then you'll find that S s eyes is very good. A transferring data from an oil TP souls which they're similar. A sort of source is SQL server the sort that you get in SMS that soil TP for transactions to S S A s on or L A P analysis, which is for cubes and that sort thea cream stand for online transaction processing and online analytical processing. Basically, transaction processing is for entering data. Rapid speed on analytical process is for analyzing the data in a more user friendly on faster format foul connection. So that just connects to a file. And that's a big, more Barrett on a new connection. You can have lots of different connectors such as Hadoop, Andi, all data Andi, I see. Over here on the left hand side, we also have hates d. F s. So the more esoteric stuff that you could get, we also have CDC, which is change data capture. But that's a bit advanced to go through in this course Onda. We have again a few other things. Data readers said you can see using as a custom application such a sea shelf. So if you use visual studio for programming you might be aware of. The date readers raw file destinations on sources. They are used by SQL Server. Exercise themselves so you can save data and then retrieve it at a later date. But again, that's far to advance record sets. Generally, they are small tables, and we may be using one later when we have a look at the control flows. But the rest of them has a say very esoteric in often used. But do you know that they're there so that if you ever have to use him, you could go that one? I want to use that one. But for the most part, if you have a look at the source assistance, you can see that they are divided into SQL Server, exchanging flat fouls and our recall if you got it. Whereas the destinations are SQL Server, Excel and article, those are in brief your over connection manager 31. Practice Activity - Different data sources, sort, merge and auditing: right. It's now time for your fifth practice activity. Let's see what we got for you. Well, we got to source files called Soul Sierra one in Seoul sale to not tell boy the both text files. But I including an Excel spreadsheet if you do want to in your computers, OK, to read from an Excel spreadsheet. But I've got to text files for you if you just want to text files. So combined them had some auditing on then I've exported into excel All use a debt of you. So just to confirm in the s s Eyes folder got some source files on do we've got here a text file and another text fell plus a spreadsheet if you want to use that so combined to the files together on and that's, um, auditing and see the results. Right? So let's see how you could do it. So if I delete everything that's there, let's add into text file sources. So these are flat files. If you remember. So one flat file is source one. So that looks OK on the over is cells to he fails. Doing this at less of a speed out to rename it told the rest. Combine it well, you know, all the high. Prefer using that. If you write to use merge, then you would need to sort it first. So let's just open it up. See, Fats works on. Then you can have exported to excel. Or you can use the data viewer. I'm just going to use the debt of your riches for convenience on Let's see what we got. So it I was about to say it happens to be sorted, but it doesn't. So you could use sorting afterwards. Or you could export it to excel, not practice activities, yet you can do with this. 32. Adding Variables and Row Count: right in this lecture. I'm going to introduce the concept off. The variable now available is a object is container and it stores a number. Start the string stores. I filed stools. A table set stars almost anything. The object is very versatile. Now I'm just going to introduce the variable using something fairly trivial. How Maney rules do We have 36 from 19 and 70. This is you will recognize from the previous practice activity. Now suppose I wanted to add that to my order. Tink, I want to say that there are 36 rolls that we've Let's say added, Let's say we're adding that into a database in the 1st 6 Ross or we need to be able to count the number of roles on there is a raw count component, so we'll have that in between union All Onda the derives column. It's attracted down on the road count, so that goes from there, and that goes for to that. But at the moment it doesn't work, and the reason is the raw count. Yes, it counts the roles, but it needs so my to store it specifically, it needs a bearable. So let's at available now, variables can be added over here. With this, it looks like a cube. So if I click this, we got a new pain come up. This pain is the terrible pain, so we have at move, delete and properties. So let's add a new available. Now I'm going to call this variable what I call it number over the original. Now its scope is why it is valid. So at the moment, we all have just the one sculpt SS size package. But when you're doing project with multiple packages, you might say that the pact, the scope is the entire project. Or you might say it's an individual package or you might say something smaller now. Data type gain. This is a frustrating bit. It's a different terminology to what we've seen previously. It means the same thing previously. We're talking about I fours and Eights. Now we're talking about in 32 while in fortitude is the same as in High. For his four bytes equals 32 bits. It was very frustrating. Hand is default value. It's a default value off zero minus one whatever. And then we have an expression tap on this. You can assign it to a particular expression, so we won't need that just a moment. But you can use all of these expressions to assign this available to something specific. Okay, so now we've got available. We can now assign this raw account to available, so I double click inside it. And here's available on. We now have one variable. Actually, we have more than one. We have lots of system variables that have all been settled, but it won't allow us to use those. So here we have a number of roles. So if we play, nothing happens. You think, except we now have a new variable which has gone into 36 gotten his new available, but we just can't see it. So what on the computer to do is to break is to pause when this value has been added and we can do that Using break points now break points. You can't set them in the data floor. Most of my nineths, if you might think, click on that. There's nothing about data points break points, but in the control flow. We do have something called break points so the control floor controls breaks, so let's edit the break points, and you can see that there is an enormous number of things that we can tell the computer Stop for Weaken Break before the data floors started. This particular task. Weaken break after it's completed. But keep going down and weaken break when the variable has changed. Okay, so we click. OK, you can see a break. It's this red circle which, if you've used visual basic for applications or visual basic all visual C shop, you probably very familiar with that being the symbol of a break point. So let's click. Start right then from the floor has broken. We have the circle with a yellow arrow in it. That's the symbol off. I've stopped. I'm waiting down here. We have a watch and we can add valuables to the watch. The number off roles and he could be user colon colon number of roles. Currently, the value is minus one so we can see that's all the information contains. So I'm not going to play notice. Aiken invented a watch when I wasn't running the card. There's a few things which were quite annoying. I want to be able to set things up, but I can't. I have to actually start things and then stop it. So let's continue and see what happens. So now notice that the virus has gone to 36. So it's gone all the way through. Its come back to the control floor now say is available has changed and now is posed. So now the variable says 36. So that is one way to read it. Admittedly, it's not the best way in the world, but what you could do, perhaps is you might want to. I had a multi floor and exported that way. Or there may be other ways that you can export it. We'll see how we can use variables in our next lecture. 33. Script component: right. So in the previous lecture we developed this variable called Number of Roles. Onda We used a watch window to get access to it. I wasn't particularly good. So what I want next is to have a dialogue box come up with the number of roles. So let's get rid off these edit break points I want going to use in the control flaw is a script task. So this is something which is going to happen after this data flaws completed. Now, if a double click connect, the scripts can be written in Max Soft visual C sharp. Oh, Microsoft Visual basic. If you don't learn to any of those, I'm afraid this electricity is not about learning visual, basic or visual C sharp. There are over one's available for you and it is a big study. However, I'm just going to show you how it interacts For those of you who do know wave S s s first of all, we have to pass it The variables we have to pass the rebels that we want to read read only verbals and those that we want to write So we can do both here Select which language you're going to use. I'm going to use visual basic for this example and then edit this script. Now we do have to wait a moment or two for this to come up. It's not exactly on the quick side, a new window or come up with our visual basic editor nowadays. By the way, if you got a choice, which to use visual basically see shop. The SQL community seems to prefer C sharp because I'm very old. No, really. But just feels it sometimes on because Microsoft Excel uses visual basic for applications and access users visual basic publications. I feel a bit more comfortable in VB, but I can use C sharp pick needed, right? So there's lots of things going around. But the only thing here that I need to concentrate on, it's at your court here, So I'm going to do a message box. Andi, it's going to contain DTs, um, data tools, DTs adult variables, open bracket quotation in the name of available, which happens to girls. And then afterwards we want the value don't value, and we want to convert it into a string because message box is anticipating a strength. So this is very, very if we give up on basic in terms of what functionality can do here, so I'll start a run it on Dhere. We have our dollar box 36. Now that shows you what you can do with available, but it also gives you just a glimpse into the script task. Yes, there's a lot more you can do of it, but this is more of an introduction to exercise. If you're more off a C sharp person than let's introduce a second script made this one visual C sharp so you can makes him much if you really must put out really prefer. If you stuck to one of the of art, imagine anyone reading your cold with preferred, so we wait for the script to come up. Andi. It's almost the same. So we go down to the main message. Walks don't show. DTs don't variables Andi. It is rules and then find you just drink. So in case you under cynical in the end, of course, 100 open bracketing close bracket that so it's a big difference to visual basic which time you prefer, Has, it said, now does the trend seems to be c sharp for s s eyes people. So here's our first quit running in VB 36 is our second script running in C sharp on that is displaying a message box Onda the script task. 34. Well done: congratulations. You made it. The term SS eyes Integration services sounds obscure. But now you know the voting blocs. So now you be able to interpret over people's planets and try and work out what they mean. So what did you do? Well, first of all, we installed the developer edition of three. And that's not a time limited thing. It's just to keep we extracted data from SQL Server from Excel. Don't we noted that a few problems. That and from text files we didn't. Various transformations merge union ole salt derived columns, data conversions, all sorts of things. We split up into more to cast and we got back together with unions. Then we saved him into SQL Server into Excel. Or we just put on a date of you and see what's happened. And then, apart from the data floors, we don't had a look at the control falls, especially for each content. So now the world is your oyster while you go from now, personally, I would practice. Why not download the adventure works database and use that data for you to extract transform on bar good luck in the future and thank you very much.