The unacceptable data is
routed to a recordset destination. In doing this exercise, you will learn the usage of
an Excel Data Source besides configuring the Data Conversion Data transformation.
You will also learn how to handle rows that cannot be loaded to the destination.
Source and Destination for the Exercise
The Excel source and the MS Access table will be described. As a preparation for this
exercise, you should construct these simple files. This exercise will be using these
files in the data conversion process.
On Using a Data Conversion Data Flow Transformation
[ 160 ]
Excel Source
1. Create a MS Excel 2003 Spreadsheet file in C:\ drive, DataCnvrt.xls,
with entries as shown in the following screenshot and rename the sheet as
DataConvert.
It has four different data types with an erroneous entry in one of its rows.
This file is created in C:\ drive for convenience.
The data types in Excel columns are based on the OLE DB Specifications such
as OleDbType.double for Numeric data like in the Height column; OleDb.
Currency for the Cost column, etc. These can be determined by calling the
appropriate OLE DB provider programmatically. For details, the KB article
(http://support.microsoft.com/kb/318373/en-us) may be useful.
OLE DB Data Destination
1. Create a MS Access application DataConvert.mdb. In this database, create
a table, Access Destination, having the following design. This table will
be empty to start with, but will be filled with data from the XLS file after
the data conversion.
Pages:
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123