Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
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.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.