In the past 48 lessons, you've see most of the common SQL Server Integration Services (SSIS) components, but now it's time to think a little out of the box, or maybe just make the box bigger and try a complete solution. In this solution, you create a package that performs a load of a few dozen files into a table and audits the fact that you did this. You also build steps in the package to prevent you from loading the same file twice.
In this lesson, we're assuming that you've gone through the rest of the book to learn the components in a more detailed fashion than will be covered in this lesson. Some of the low-level details in this lesson have intentionally been left out for that reason, but you can see those steps in this lesson's companion video.
To work the solution in this lesson, you need to download a few files from the book's website at
www.wrox.com. The files that accompany this lesson are as follows:
Lesson49Create.sql — This is the file that creates the schema you'll be using throughout this lesson
Lesson49Data.zip — This contains the data that you'll be using to load
Lesson49DataNextDay.zip — This contains the data to simulate the next day's data
Lesson49.dtsx — This is a completed version of the package for this lesson
The AdventureWorks, Inc., sales department wishes to communicate with voters who signed a certain petition. They also want to ensure that you never load the same file twice, wasting the salesperson's time. ...