Creating SQL Server Integration Services packages

- Okay, we're going to take a look at creating an SSIS Package. We've already done that several times, but I wanna show you a couple little tricks. So, down in the taskbar at the bottom, I've already pinned SQL Server Management Studio, and in order to show you Integration Services, I'm gonna right click it, and I'm gonna right click again, and I need to Run as administrator, or else I will not be able to show you Integration Services. And we'll let that pop up, and we're connecting into Integration Services, and we'll say Connect.

Now here's our Packages. And if we move on down, there's the Packages that are already existing in SSIS. So let's work with one of them. We're going to import that. So again, I'm going to go back over into SQL Server Data Tools, and I need to Run this, once again, as administrator. It's probably not a bad idea to Run it as administrator by default. Again, I'm gonna say Yes. And we'll create a New Project in here. I'm just gonna select a blank project, and that's fine.

And it's going to be of type Integration Services, and an Integration Services Project. And I'm going to accept all the defaults. I really don't need a directory, so I'm gonna clear that out. And I'm gonna say OK. Now, I've got this little sample open up here, which opens by default, and I don't like it to be open. So, you see, you get one Package already in here. We already know that you add another Package if I just right clicked on it, New SSIS Package, and we've got another Package. But let's go ahead and import a Package.

So, now I'm going to right click once again, and I'm going to say Add Existing. This is very beneficial, particularly if you well-documented your Packages, and put them into folders appropriately. So, we're gonna look at the Package Store, which is what we just looked at over on our Server, using SQL Server Management Studio. And the name of the Server is BLUEBUFFALOPRES. Hit the ellipses. And we're going to look at MSDB, that's the database, and expand it, and let's bring in PerfCountersCollect.

And we'll say OK. Succeeded in upgrading. You always get that little thing. So, there's PerfCountersCollect, and if I double click it, we'll go ahead and open it. So, here's the Package that we've just brought on in. And what we can do now is, you see, if we had different Packages, we can bring in and put together these Packages within our Package Store, here, so that if we do document, as I said, now we can bring in and reuse the Packages.

If we actually have Tasks on one Package that you want to reuse, let's go through and just take the very simple one to make execute, so we'll grab a Back Up Database Task, and bring that in. I'm just gonna double click it, and now I've got that, and if I right click it, I can do the same thing. I can Edit, and I can go through. I'm gonna add this in here, so we're going to do a Back Up so you can see it.

I need a New Connection. Connection name doesn't really matter here, for this. And my Server is my default. I'll say OK, the Database, the default, by the way, was that . which mean local host. So, if I pull this down, I'm going to Back Up AdventureWorks2014 and I'll say OK. And OK, here. So, now I've got this Database Task in this one Package, and if I right click that Database Task, say Copy, and come over to this, and right click it -- Oh, I've gotta open it first.

And now I right click in here, and I Paste. That comes on in, and you'll notice we have an error. The reason we have an error is, back on Package1, this Connection Manager. Remember the Connection Managers? And here I'm gonna right click on that Connection Manager, and what I'm gonna tell it is, I wanna Convert it to a Project Connection. Now, that Connection is in the Connection Managers, and if I go back over to the where I just copied, and I double click in here, now I can select the Database, because the Connection is live.

And last time I Backed Up whatever. This time I can, say, back up a different one, the AdventureWorksDW2014, my Connection is good to go. So, we'll say OK, and now I've gotten reuse with the same Task in two different Packages.