Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The end result of working with Business Intelligence Development Studio and SQL Server Integration Services is you end up with a SSIS Package. It ends in .dtsx. In fact, I'm going to come out of Integration Services because I have that folder open here, and this is the package file that I'm interested in. Yes, I can run it from inside Business Intelligence Development Studio, but if I wanted to have this occur and automated, I can actually take it into SQL Server Management Studio and make it part of the database.
Now again, it's just a simple package. This is the one that's actually affecting a flat file but the operation is still the same. I'm going to open up SQL Server Management Studio. Except this time rather than connect directly to the database engine, I'm going to connect to Integration Services using the same administration information, and what we'll get here is running packages and stored packages, and if I wanted to take in that package into the database, make it part of the database, what I'd actually do is come to the section that says MSDB.
That's one of the system databases that's involved in queuing and setting up jobs. Right-click and say Import Package. It's going to ask "Well, where is the package? Is it in SQL Server?" No, it's actually on the File System. So I'll go and grab the package path. It's going to be in my Visual Studio 2008 > Projects, in FlatFileTransform. It's several levels deep, but it's where I had created it, and click Open and click OK. This package is now considered part of the database.
We can execute it at our leisure or we can even set it up to be a scheduled job. Just to prove that it's going to work, it's just fine from within the confines of SQL, let me just minimize this window a little bit. Because I can actually see that my desktop here contains the names, which is my input. And I can actually come over here and right-click the Package and run it. Running within the confines of the database I've not got BIDS open. So I'm not using anything in Visual Studio.
I have all sorts of questions here about configuration and who I'm running as and connection managers and options and reporting and logging, but of course this is a fairly simple one. So I'm just going to execute it. And I can actually see behind the scenes right now that here's my progress. I can close that but I've already got the output file just sitting there with all the transformations applied to it. Now the window that I just saw is the same one that you'd get if you went directly into your SQL Server 2008 R2 programs into Integration Services and find the Execute Package Utilities.
Just a standalone utility that would execute a package, whether it's on the file system or stored in SQL Server. Of course, the big benefit of doing it this way is you don't need to have Business Intelligence Development Studio opened and running for everybody whoever wants to execute this package. You build it in that application and then it can be executed from the database, even from things like stored procedures. Or you can of course schedule it to happen automatically on a daily, hourly, monthly or yearly basis.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70090 Viewers
80 Video lessons · 127451 Viewers
52 Video lessons · 62394 Viewers
59 Video lessons · 48098 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.