Now open the transformation’s editor. You’ll
need to convert the BookID, Title, and NumInStock
columns, as Figure 2 shows. As you can see, you must
convert the unsigned integers to signed integers of the
same size and convert the Unicode string to a regular
string. When you convert the string, leave the length as
60. This is the column length in both the source and
destination databases. Also assign new column names
to the outputted data. I simply added the number 2 to
the names.
Finally, after you’ve converted the data, you can
send it to SQL Server. To do so, add a SQL Server
destination component to the data flow and connect
the transformation to the destination. In the component’s
editor, select the connection manager for the
SQL Server database and the Books table from that database. For this solution, I’ve named the connection
manager BookstoreDB.
Now make sure that you correctly map the columns
from the transformation to the destination. Look at
the Mappings page of the destination’s editor, and
you’ll see that I mapped the converted columns to their respective destination columns but left the Date
Modified column with its default mapping.
That’s all there is to it. As with any SSIS solution,
the key is to make certain that the source data is properly
converted to the format necessary to be inserted
into the destination database. You can see the final
Books data flow in Figure 3. Now let’s look
at the Authors data flow.
Step 4: Configuring the
Authors Data Flow
As you did for the Books data flow, your first step is
to add a DataReader source component, configure
it with the correct connection manager (in this case,
MySQL_ODBC), and define the SQL statement
needed to retrieve data:
select * from Authors;
Now look at the Input and Output Properties tab.
This is where things get interesting when using the
Connector/ODBC driver. You can see the AuthorID
column properties in Figure 4. Notice that,
unlike the BookID column, the data type is now an
eight-byte signed integer, although the AuthorID
definition is the same as BookID. Thus, you’ll need to
convert AuthorID to a four-byte signed integer data
type. Now look at the FName column’s properties.
As with the Title column, the data type is a Unicode
string; however, notice that the length is 21, although
the column definition in MySQL specifies 20. The
MName and LName columns exhibit the same
behavior.
When you find a discrepancy of this nature, keep an
eye out for the driver adding an extra space before or
after each value. To protect against this possibility, you
can use a Derived Column transformation to trim the
extra spaces. Figure 5 shows how you’d trim the three
columns that contain a string value. Notice that you
use a TRIM function and simply replace the column
with the trimmed values.
After you create your derived columns for the
string values, you can then add a Data Conversion
transformation and connect the Derived Column
transformation to the Data Conversion transformation.
In the Data Conversion transformation, convert
the AuthorID column to a four-byte signed integer and
convert the three name columns to a regular string with
a length of 20. You don’t have to worry about the Date-
Born or DateDied columns. Although they’re defined
as DATE columns in MySQL, SSIS imports them as
timestamp columns. From there, add a SQL Server
source component and be sure to map the converted
columns to their respective destination columns. When
you’re finished, open the Data Flow tab to view the
Authors data flow that you’ve configured, as Figure 6 shows.
On Your Way to
MySQL Integration
The solution I’ve shown you is, of course, a basic one,
but it does give you an overview of how to retrieve
data from a MySQL database and some of the issues
you might have to address when you retrieve that data.
Although I showed you how to use both MySQL
drivers, you’ll no doubt use only one in your SSIS
package, and for the most part, you’ll probably want
to go with Connector/Net. However, I wanted to show
you both connectors in case you encounter situations
in which Connector/ODBC has already been implemented
or you have another reason to use ODBC.
Regardless of the solution you ultimately implement,
you should now have a good idea of how to access a
MySQL database from SSIS.