MySQL text field and SSIS

I still hate MySQL. From a language perspective, while I certainly
think particular languages are more elegant, expressive and enjoyable
compared to others, at the end of the day I can code in anything I've
been exposed to. Databases on the otherhand, you'll have to pry SQL
Server out of my cold dead hands. It's probably not entirely fair for
the two reference implementations I've had to work against for MySQL.
One was running on a windows 98 or some trashy OS which made
management challenging. The other, is this ancient 4.x instance we
have running at work.

But, to get the to the point of this blog post, The "component
"DataReader Source" (1)" failed because error code 0x80131937
occurred, and the error row disposition on "output column "X" (419)"
specifies failure on error. An error occurred on the specified object
of the specified component.

The table I'm working against has text columns. I had a simple query,
wired it up to a DataReader Source with an ODBC connection
and wammo, the above error.
<pre>
SELECT
T.table_id
, T.some_text_field
FROM
MyTable T
</pre>

Initial pass with google led me to believe it didn't like the binary
data type so I wrapped it with a CAST function but MySQL didn't like
that. Convert, also not a fan. More digging and a site suggested
just grabbing the first N characters via substring. That would at
least run through the web query tool but of course, the data reader
wasn't having anything to do with it. More reading, "Does it have
nulls?" Yes... oh, connect article about an Ingres odbc reader that
was throwing chunks on an empty nvarchar field with the above error
code. Wrap with coalesce and still no dice. At this point, I've
given up on caring why it's behaving as such and just want it fixed
(damnit!)

The solution I ended up taking was a hybrid approach and it seems to
be working for me. My query has the substring and coalesce calls to
it but to get the data out, I went to the bare metal with a Script
Component acting as Data Source. Plenty of implementations of it out
there but an abbreviated version would look like
<pre>
Imports System
Imports System.Data
Imports System.Data.Odbc
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim connection As Odbc.OdbcConnection
Dim command As Odbc.OdbcCommand
Dim reader As Odbc.OdbcDataReader
Dim query As String

Public Overrides Sub PreExecute()
MyBase.PreExecute()
query = "SELECT T.table_id, SUBSTRING(T.some_text_field, 1,
2000) AS some_text_field FROM MyTable T"
' TODO: pull this from the connection manager
connection = New Odbc.OdbcConnection("DSN=MySQL")
connection.Open()
command = New Odbc.OdbcCommand(query, connection)
End Sub

Public Overrides Sub CreateNewOutputRows()
reader = command.ExecuteReader()
While (reader.Read())
OBADataBuffer.AddRow()
OBADataBuffer.tableid = CInt(reader(0))
OBADataBuffer.sometextfield = reader(1).ToString()
End While
reader = Nothing
End Sub
Public Overrides Sub PostExecute()
MyBase.PostExecute()

command = Nothing
connection.Close()
connection = Nothing
End Sub

End Class
</pre>