Microsoft® SQL Server® 2008 Integration Services: Problem-Design-Solution

4

File-Handling and Processing Methods

SSIS has many powerful capabilities for handling and processing files. They might be data files from which information needs to be extracted. They might be signal files that are used to coordinate a process between two systems. They might simply be regular document files that need some administrative management. SSIS has features that can be helpful in all of these scenarios.

This chapter examines common file-handling tasks, and how you can accomplish those tasks with SSIS. The common file-handling tasks addressed in this chapter include the following:

• Finding files by name or other qualification criteria
• Looping through those files and manipulating them individually
• Performing simple file system operations
• Transferring files between servers
• Receiving notification that a new file has been created

This chapter is structured into the following three sections related to the problem, design, and solution of ETL extraction processes using SSIS:

• The “Problem” section elaborates on some of the common file system tasks listed previously. This section is general to ETL, and is not directly related to SSIS implementation details.
• The “Design” section picks up where the “Problem” section leaves off, and discusses how to approach the common file tasks using SSIS.
• The “Solution” section is dedicated to an example for merging multiple files together.

Problem

Many data-management tasks involve manipulating files. A file must be transferred from another ...