patternsqlMinor
Avoiding "Row by row" fetch method when dealing with source LOB columns
Viewed 0 times
methodcolumnswithsourcefetchdealinglobwhenrowavoiding
Problem
I have a legacy PostgreSQL database source (ODBC) that I'm attempting to migrate to new SQL Server schema using SSIS. I'm getting a warning saying:
'Row by Row' fetch method is enforced because the table has LOB column(s). Column content is LOB
The thing is, none of the column really need to be LOBs. There's a few that are TEXT types, but could fit easily within a varchar(max). Even stranger, though, most already are varchars, but it seems anything over varchar(128) is being treated as if it was a LOB (in advance properties, the data type is DT_NTEXT).
I event tried doing a manual SQL command where I explicitly casted every string type to a varchar of an appropriate length in the select statement, and they're still being set as DT_NTEXT in the ODBC source.
I'm not a DBA, so it's entirely possible I'm doing something really stupid. I would just like to know the best way to ensure that the types end up as varchars so I can batch fetch. Any ideas?
In case it matters, I'm using SSIS-BI 2014 inside Visual Studio 2013.
'Row by Row' fetch method is enforced because the table has LOB column(s). Column content is LOB
The thing is, none of the column really need to be LOBs. There's a few that are TEXT types, but could fit easily within a varchar(max). Even stranger, though, most already are varchars, but it seems anything over varchar(128) is being treated as if it was a LOB (in advance properties, the data type is DT_NTEXT).
I event tried doing a manual SQL command where I explicitly casted every string type to a varchar of an appropriate length in the select statement, and they're still being set as DT_NTEXT in the ODBC source.
I'm not a DBA, so it's entirely possible I'm doing something really stupid. I would just like to know the best way to ensure that the types end up as varchars so I can batch fetch. Any ideas?
In case it matters, I'm using SSIS-BI 2014 inside Visual Studio 2013.
Solution
Apparently this just boils down to SSIS treating any varchar larger than 128 as NTEXT. Not sure why. I can, however, go into the advanced properties of the ODBC source and change the types back to something like DT_WSTR. Which seems to work for the most part.
However, I did determine that a few of the tables I'm dealing with actually are carrying upwards of 4000 bytes in some of the their TEXT columns, so I unfortunately have to leave those colums as DT_NTEXT to prevent truncation (SSIS won't let you set a DT_WSTR type with more than 4000 bytes). I suppose in these instances, I'm just stuck with row-by-row fetch, but at least I was able to fix a few tables.
However, I did determine that a few of the tables I'm dealing with actually are carrying upwards of 4000 bytes in some of the their TEXT columns, so I unfortunately have to leave those colums as DT_NTEXT to prevent truncation (SSIS won't let you set a DT_WSTR type with more than 4000 bytes). I suppose in these instances, I'm just stuck with row-by-row fetch, but at least I was able to fix a few tables.
Context
StackExchange Database Administrators Q#102830, answer score: 4
Revisions (0)
No revisions yet.