Join Ron Davis for an in-depth discussion in this video Using the exercise files, part of SQL Server Integration Services (2015).
- We're going to install SQL 2014. Now the course was based on SQL 2012. There is no difference between SQL 2012 integration services and SQL 2014 integration services, with the exception that you need to download with SQL 2014, the SQL Server Data Tools, separately, whereas with SQL 2012 they were built in as part of the default install.
Now if you already are running an instance of SQL 2012, or SQL 2014, you're in good shape. The only thing you need to make sure, is that you have administrative privileges on the SQL Server, because you're going to be creating some objects as you go through the course. So in order, if you don't have SQL 2014, and you want to receive it, you just take you over to this page, evaluation center, and you download it there. Now you're going to have to sign in and Microsoft's going to require you to give them some marketing information.
It's really no big deal. If you do that then you'll get the evaluation, and you can go ahead and download it, and then we're going to go in and do the installation. The installation that we're doing is based right here on a pristine environment. This brand new and what we have here is we have Windows 8.1 Enterprise edition, and this is an evaluation copy of that, we're running on a virtual machine. It doesn't really much matter. There will be some variance to how you do the install based on your operating system, but it's really pretty close.
I've taken the ISO file and I've mounted it into my D:Drive. I'm just going to double-click my D:Drive and I'll get this UAC prompt and I'll say Yes, and I'm going to close file browser right now and we'll wait for the installation to start. Okay, we'll go immediately to the installation tab, and we're going to do a New SQL Server stand alone installation, and I'm going to minimize this, and again, wait for the installation to continue.
Now, on the Product Key, we'll just go Next. I'm running the evaluation addition. We'll accept the license, and we'll just go Next. Now we're going to scan for product updates, and we'll allow this process to go through, and the rule check. Depending on when you do this update or this installation, your system may find some updates, so let it go through and do those installations. This is really a terrific feature. So SQL Server installation is correct, and we'll just go Next.
I'm going to expand this a little bit, so that you can see it a little cleaner. We're going to take the Database Engine. We're going to take Analysis Services and Reporting Services Native. I'm not going to take any of these. If you want to take them, go ahead. In the shared features, take your Client Tools Connectivity. Make sure you take Integration Services. Drop down to Management Tools and check the Complete which will also select the Basic.
Documentation Components is up to you. I tend not to take them because I prefer to use online. None of the rest of this do you need. It's optional. You can take Master Data Services if you want to ever explore that. If you take Master Data Services, make sure you take Data Quality Services also, but we're not going to use those in this course. Now, we'll just go Next. Instance ID, we're going to stay with the Instance ID of MSSQLSERVER. You'd have to have a really excellent reason, to change that.
I really recommend you never do that, but we'll go Next. Here's your accounts. Everything's fine, so we'll just go Next. Now, I tend to always move to Mixed Mode authentication, because if you ever get locked out, which can happen, if you have Mixed Mode, you can always get into the database, so I always tend to go Mixed Mode, and I'm going to enter a password. I always use for training, password, capital P, A, S, S, W, zero, R, D, but you can do whatever you want.
Make sure you add current. Now what this is, is DemoPC is what I named this instance of Windows 8.1, and I created the Administrative User, and I named it Student. Now, I'm going to Add Current User here, and we'll leave the default, Multidimensional Mode. Next, and Reporting Services, I would leave the default. Let it install and configure it for you, and we're ready. We're just going to click Install, and let it roll.
Okay installation is completed, and by the way, when you're going through and doing this installation, it may appear to stop on you, just let it run, because sometimes certain of the tasks that it's doing, just take forever. So you can look at the report if you'd like to, and we'll call Close. Now, we're immediately going to go back in to the installation center and again click Installation, because we're going to install a second instance of SQL.
That is if you're going to follow along the way the course was done, we use two instances of SQL, and when I say instance, if you're not familiar with that, the way SQL works now, and has for several iterations, you can install multiple instances, and set them up with different security settings, etc., collations. They are completely separate, one over from another, as if they were on separate machines. We're going to install another one. New SQL Server Stand Alone.
By the way, when I say they're completely separate, that's true, however, they do have some shared features, so they're not really the same as if they were completely on a different machine. So, we'll go Next. We're just going to move through this very quickly. A new install, evaluation edition, accept the license, SQL Server, correct, and you're going to take Database Engine, Analysis Services and Reporting Services again.
You notice these are grey, that's because they're a shared feature, so you're good on that. Next. Name, this is a Named Instance. I'm going to name it Instance2. That way it'll match what we did in the course. Next, Next on Service Accounts. Add Current User once again. Go into Mixed Mode, enter a password. Capital P, A, S, S, W, zero, R, D. Confirm, Next.
Now, I suggest change this to Tabular Mode. We're not going to use that in this course, but it will give you an option. Tabular Mode in Analysis Services, is very similar to using the Power Pivot model. We have the current user, and we'll go Next. Install and configure, again Next. And call install once again. Now this will go faster for your second install because it's not installing the shared features which are already there.
Okay, when we're finished we just call Close, and we can close the installation center now also. The next thing we want to do is use SQL Server Management Studio. Again, I'm on Windows 8.1. If you're on Windows 7, or you're on a server, you can just click Start and look under SQL Tools and it'll be there. But, I'm going to find it by hitting my Windows Key and F, and I'm going to change this to Everywhere, and now I'm just going to enter the string, SQL Server, just the M will bring it up.
There's my SQL Server Management Studio, I'll right-click on it. I'm going to pin it to the Task Bar. Now down in my Task Bar, there it is, and I'm going to go ahead and start SQL Server Management Studio, and it'll take it a few seconds to fire up. Okay, we're going to go into the name of the PC, which is DEMOPC, or frankly you could just go, if you're working on Local Host, if you hit a dot, that means Local Host, and we'll call Connect, and as you see we're connected.
Here's the databases. There's really no databases in there. We just have the standard system databases, but now we're also going to call Connect again, and Database Engine, and we'll connect into Instance2, and now again, we're connected into both Instance1 and Instance2. Alright, I'm going to switch over to Internet Explorer. Now, what you want to get now, is the Adventure Works and the Adventure Works DW 2014 edition, of the databases, so that you can restore them.
What you're going to search for, is you can search for Adventure Works 2014 sample databases. Now these URLs change. Microsoft moves this stuff around, and frankly, if you're going to be working with SQL 2014, you just want to go ahead and download it, and save these databases in some location, so you don't have to download them twice. You want to download Adventure Works 2014 Full Databases Backup.zip, and in Others, if you drill down here, you'll find this Adventure Works DW 2014 Full Database Backup.zip.
This is the data warehouse, not the analysis services database. So those are the two you want. Now, I've already downloaded them, and if I look in my file system, on my Downloads, here they are. They do come down compressed, which is, if you can see it, that's this little zip in here. So, when you decompress them, you just right-click and tell it Extract. Here's what you'll get, for example.
Here's the Adventure Works 2014 Backup. What we want to do is, is install this. You can leave it right where it's at if you want to do the installation. I am not going to do that. I'm going to copy this, and I'm going to move it to where it should be, to where seek works best to find it. I'm going to go into Program Files, SQL, and if you come through there, you'll see here's Instance2 and Instance1.
Notice that's A, S, for Analysis Services. R, S, for Reporting Services, and we're going to go in here. I'm going to go into MSSQL, I'm going to go into Backup and I'll get this little prompt. You have to run this as an Admin, so we'll say continue. Now, I'm going to right-click and I'm going to Paste. Let me go back in the Downloads and get the other one. I'm going to Paste the second one in. Again, this was an optional step.
You do not have to do that. We'll leave this running, just in case, and let's switch over again to SQL Management Studio. I'm going to come up here. Let's minimize that and go into Databases. Database, I'm going to right-click Database, and I'm going to say Restore. It's off a Device. Device is an old, old term. We're going to click in here and we're going to Add and search for it in the file.
Now, you'll note by default, SQL looks in its Backup folder. We have to restore one at a time. There's the Adventure Works. We'll say OK. OK, here, and OK here. Close, say OK. There it is. Do it again. Restore the second one. Restore, Device, hit the ellipsis, click Add, select the data warehouse, DW. Tell it OK. Tell it OK.
OK. There's Adventure Works. Now, I always tend to do this, but I'm going to right-click and just make sure I can run a query. Select Top 1000. There we go. I'm also going to test the other one, Adventure Works DW Expand tables, grab any table, Select Top 1000, and there we go. So were good and we'll close both these query windows, just to clean up the display. Minimize the table.
Minimize that. Now, over here on DEMO, we have a database that we used, called Demonstration. Let's create that. I'm going to right-click this, and I'm going to say New Database, and name it Demonstration. Click OK, and there's you Demonstration database. So you are good to go. You've got SQL, and both the default instance and another instance, Instance 2, where you'll be moving data back and forth.
Now if you're running SQL 2014, here's your next step. Let's go back over to Internet Explorer. Go into this URL. You'll click Download, and this will be a long download. After it completes, you'll have that. Now, we're going to go ahead and run this. I'm just going to double-click it. I get a UAC prompt. Yes. Choose Directory For Extracted Files. C, Users, Student, Downloads, that sounds fine. We'll say OK.
After you extraction is complete, move to the folder and now we're going to run Setup. Yes at the prompt. Again, here's your Data Tools. Accept the license. Next. Next. Perform a new installation, and that's fine, and we'll go Next. You want SQL Server Data Tools, and allow the Client Connectivity SDK to be installed also. Again, if you have SQL 2012, the Data Tools was part of the shared features you could have installed during the default.
We're just going to let this roll, and on completion it'll tell you, you need to do a Restart, so we'll just go ahead and do it. Okay, on reboot, we're going to pin SQL Data Tools to the Taskbar, just like we did with SQL Server Management Studio. So, I'm going to search for it and again I'm on Windows 8.1, so we search this way and I'm going to enter the string SQL Server, and if I just type in Data, and drill down here and look for that icon, I'm going to right-click on that icon, I'm going to Pin to Taskbar.
Now, I'm going to go ahead and start SQL Server Data Tools. It'll take it a little bit to fire up. And Data Tools start, always test it. Although frankly, anymore, this stuff really works very well on installation. I just selected New Project, and I'm going to take and Integrations Services Project. When you start you may get a prompt asking you what you want, whether you want a Business Intelligence template, etc., and just select Business Intelligence. So you're going to go for an Integration Services Project, and I'm going to clear this little Create Directory, because I really don't want one.
I'm going to say OK, and I'm not interested in the Sample, so I'll clear that. And we're good to go! Here's a package. Here's my Toolbox. SQL Server Data Tools Work. By the way if you ever here the phrase, "BIDS," Business Intelligence Development Studio, that what we called this before SQL Server 2012. We can close that and your install is finished with the exception of the exercise file which is very easy and we cover in a separate video.
- Creating SSIS packages
- Coding and looping tasks
- Managing and administering SQL with SSIS tasks
- Optimizing data for extraction
- Making packages dynamic with variables and parameters
- Using sequences in a container
- Performing joins
- Handling errors
- Working with XML