I make extensive use of variables, expressions, and .NET scripting in an SSIS 2008 package that performs a backup of user data on a Windows file system.
SQL Server Integration Services (SSIS) is Microsoft's technology for performing extract,
transform, and load (ETL) operations for data migrations and data warehouse loads, among
other applications. Although most of the work that occurs in SSIS packages typically takes place
in its Data Flow, one can also use SSIS to address a number of requirements outside of normal
ETL activities.
In this demonstration, I illustrate how one might use SSIS to maintain a backup copy of one's
user data on an individual machine. Of course, one could use these techniques as part of a
regularly-executed process that loads data into a destination system or any other application
that requires interacting with a Windows-based file system.
Before I proceed, allow me to acknowledge Brian Knight, Erik Veerman, Grant Dickinson,
Douglas Hinson, and Darren Herbold in their exhausive book
Professional Microsoft SQL Server 2008 Integration Services (Wrox, 2008) and the work of Lynn Langit,
Kevin S. Goff, Davide Mauri, Sahil Malik, and John Welch in their book
Smart Business Intelligence
Solutions with Microsoft SQL Server 2008 (Microsoft Press, 2009). Both of these volumes were
very helpful as I prepared the content of this demonstration, and I highly recommend them to anyone
who is developing any kind of ETL or business intelligent project using SQL Server 2008.
The various projects that I use in this demo are
available for download here
(.zip file, 31 KB).
The bulk of the work that this SSIS package performs takes place in its Control Flow. Specifically,
my package begins with an Execute SQL task that logs the beginning of the package's execution in a
SQL Server database that I run locally. Next, another Execute SQL task loads a dataset
(particularly a list of file system directories that I seek to backup) into an SSIS package variable that
I have typed as an object. A Script Task then checks the character length of each file path string in my
target directories to ensure that no errors occur when those files are copied to my backup drive.
The actual work of copying files occurs inside a Foreach Loop container that copies files in each
directory listed in the object variable and that logs the completion of that copy operation.
Finally, the package logs the end of its processing, and, for added documentation, it copies all log
entries that this package makes over the course of its execution into a flat text file on the backup drive
(click on image for full-size view):
Next page: database structure
