Using Data File Messages to Collect Data
The Load Expenses package uses a data file message to take a spreadsheet from a queue, extract expense data, and load it into a database.
Your traveling sales force uses a standard Microsoft® Excel template to enter expenses on their laptops. Every week, a Data Transformation Services (DTS) package on the laptop sends the completed worksheet in a data file message addressed to a queue at corporate. When the laptop synchs with the network, the message is delivered. The Load Expenses package processes these messages on arrival.
Follow these steps to create this package on your computer.
Creating and Configuring the Load Expenses Package
The new package must be created and named. In addition, package properties must be set so that a single failure does not shut down the service.
To create and configure the Load Expenses package
Creating Three Connections
As one expense report is processed, information passes through three stages.
- On arrival, expenses are saved in a Microsoft Excel worksheet named C:\Temp\Expense.xls. This worksheet includes a range named Expenses. The first row of that range contains column headings: EmployeeID, ExpenseDate, Expense, Code, and Amount. Succeeding rows contain data for individual expenditures. Some rows may be empty. On the sheet, the Expense Report connection refers to this worksheet.
- Expenses are first loaded into a table in the Corporate database named RawExpense. This table also contains EmployeeID, ExpenseDate, Expense, Code, and Amount columns. No constraints are placed on these entries. On the design sheet, the Raw Data connection is used to refer to the RawExpense table.
- Raw data is then filtered and loaded into the Expense table in the Corporate database. This table has an identity column named ExpenseID in addition to those columns that appear in RawExpense. It also has constraints. In particular, no nulls are allowed in any of the fields. On the design sheet, the Corporate connection is used to refer to the Expense table.
Create these data sources and place connections. Create an additional copy of the spreadsheet to serve as input for this package.
Configuring the Spreadsheet Wait Task
Spreadsheet Wait is a Message Queue task that waits indefinitely for an incoming data file message. When the message arrives, the contents are saved on disk as a Microsoft Excel worksheet and the next task is triggered.
To configure the Spreadsheet Wait task
Configuring the Delete Raw Data Task
Before a new spreadsheet is processed, the residue of the last one is removed. The Delete Raw Data task is an Execute SQL task that removes all the rows from the RawExpense table.
To configure the Delete Raw Data task
Configuring the Load Raw Data Task
After successful receipt of a worksheet, a Transform Data task extracts the rows from the spreadsheet and loads them into the RawExpense table. If the task fails, the spreadsheet is forwarded to another queue for later review. If the task succeeds, another Transform Data task is triggered to perform the final load. Configuration of the Load Raw Data task is relatively straightforward.
To configure the Load Raw Data task
Configuring the Load Filtered Data Task
After the raw data is loaded, another Transform Data task, Load Filtered Data, takes rows from RawExpense and loads them into the Expense table. If not caught, any constraint violations will fail the Load Filtered Data task, with no update having taken place. If this happens, the spreadsheet is forwarded to another queue for later review. If the load is successful, the transaction is committed.
To configure the Load Filtered Data task
Configuring the Failed Expense Load Task
When a load fails, the package must continue to process other worksheets without interruption. Failed XLS Load and Failed Expense Load are two Message Queue tasks. When a spreadsheet load fails, one of these two tasks is triggered to forward the offending spreadsheet to another queue where it can be reviewed by another package. Both tasks commit the package transaction. Configuration of the two tasks is identical except for their descriptions and precedent tasks.
To configure the Failed Expense Load (or Failed XLS Load) task
Creating and Configuring Three Loop Tasks
Three Loop tasks must be created. You cause the package to loop by accessing the first step with a Dynamic Properties task and changing its status to waiting. The only difference between the three loop tasks is the identity of the precedent task.
To create and configure three Loop tasks
Running the Package
Build another package that sends a copy of the Expense worksheet to your queue. (Use the Failed Expense Load task as a model.) Execute this package and then run Load Expenses. You should be able to observe as the worksheet is received, data is loaded, and control is returned to the first step, where it waits for another worksheet. If expense data fails constraint checks, perhaps because of null entries, you should see the spreadsheet sent on to the error queue. If you send worksheets from another computer, you can watch Load Expenses wake up and process them. Load Expenses continues to run until manually canceled or an error is encountered deleting raw data or processing messages.