13

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.

Improve this question
2
  • 3
    When you explicitly cast them in the source system to a non-max sized, was that in an existing data flow or did you create a new one, or at least a new source component for it? When you provide the a query with the same column names and just skinnier types, some times that doesn't register as a change so the editor doesn't fire off a metadata collection process (which can be expensive). Also, a varchar(max) is going to be treated as a LOB for an SSIS data flow and that can hurt agilebi.com/jwelch/2010/05/11/…
    – billinkc
    Commented Jun 1, 2015 at 16:58
  • In the ODBC data source component, you have the option to select a table or use a query. That is where I'm doing the casting: in a custom query. I mentioned varchar(max) as just shorthand for saying that the column data can fit within the maximum varchar size, which is around 4000, for SSIS's purposes, I think. I'm not actually casting anything to varchar(max); though, I did cast some columns to varchar(4000), just to be safe.
    – Chris Pratt
    Commented Jun 2, 2015 at 13:57

4 Answers 4

Reset to default
4

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.

Improve this answer
4

I used Data Conversion for the varchar larger than 128 as NTEXT but what removed the error for me eventually is the set Validate External Data to False.

Improve this answer
0

This solution worked for me :

I removed the error by changing the Max Varchar parameter in the datasource property. Go the the connection manager. Select the build option next to the connection string. Click on the connection button to access more option. Change the value of the Max Varchar.

enter image description here

Improve this answer
0

In my case the source is Filemaker ODBC which also treats long text as LOB datatype. My package used to hang for a long time due to the extreme decrease in performance for Row by Row fetch method is enforced because the table has LOB column(s). Thus, while being deployed it used to timeout after a long time and fail eventually.

I am sharing the actual solution that worked like a charm for me. One day worth over 30k LOB type data pull took approx 10 minutes for me::

Lower the DefaultBufferMaxRows down to 1 and increase DefaultBufferSize to maximum i.e. 100 MB. Then change the source ODBC DSN by checking the option 'treat text as long varchar'. And map the datatypes as is from source to target (without any change in advanced editor in source).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.