For text columns from SQL Server, we have been using the LongVarChar data type within the OLEDB stage. This has worked fine with a length of 128K. But recently some of the users have decided to write complete novels in the text field.
A SQL Server text field can be up to 2GB (2^31) but the DS help is not very forthcoming on how long a LongVarChar can be. At one point, I was unable to increase the field length beyond 944,745. Attempts to use a larger field length (944,746 or more) immediately produced this abort:
Code: Select all
Program "DSP.Open": Line 122, Exception raised in GCI subroutine:
Access violation.
Attempting to Cleanup after ABORT raised in stage jobExportCaseNotes..vision_LoadCase
DataStage Phantom Aborting with @ABORT.CODE = 3
Experimenting, I found that the maximum length is actually a factor of the total length of the record buffer (makes sense). I changed one column from an Integer(1) to a VarChar(20) and had to reduce the length of the LongVarChar to 944,721 before it would run. Increasing the "Buffer Size" on the job's "Performance" tab from 128KB to 1024KB didn't seem to have any affect on what I could do with the length. However, dropping the array size did allow me to increase the length. For example, dropping the array size from 500 to 200 let me increase the length to 2,100,000.
Anyone have a working alternative for handling text columns of great length?