Last week I struggled with issue that had been causing many problems in our work. We have implemented SSIS packages that synchronize Sybase and SQL Server databases. Some of tables contain text (on Sybase) columns that have to be passed to varchar(max) columns on SQL Server. It wasn't apparent on the beginning, that only the first 32k of data are passed though.
I looked for an answer in many places. I tried to see if moving blobs via temporary files will help. Nope. Max size of such file was also 32k. Then I looked at script components, trying to find out if there is possibly an error truncating these data. There is a function GetBlobData that reads data from source. Max length of data returned by this function was 32k also. I thought, maybe it is max size of internal buffer? This function takes three parameters: column index, starting index of data to read and data length. I tried to implement moving window, and call this method in a loop, till all data are read, but no success. It was reading only 32k and not a single byte more. I knew the actual length of data because I read it in source query on Sybase side.
Looking for information, I came across textsize variable. Well, this seemed to be promising. But how to apply change to it in SSIS? If I put SET TEXTSIZE 100000 in front of the feeding select in data source, even though there was no syntax error, data source could no longer retrieve column information and the whole data flow couldn't work. I tried to create Execute SQL task before Data Flow task, but it didn't help. It seemed that what works when you connect using any sort of console, doesn't work if OLE DB is used. I reached to my mossy memory banks - I remembered that about 8 years ago, when I worked with COM+, I read about various parameters that OLEDB can accept, depending on the driver used. So, I found that indeed you can modify textsize in OLE DB parameters. The last issue was how to apply it to connection manager in SSIS? The edit dialog doesn't contain place to specify this value. Extended properies did not work. If you append TextSize=10000000; to connection string in properties of connection manager, it forgets the password. If you open the Edit window, the connection string is generated from scratch and you loose the setting. Catch 22.
We have a solution that reads connection strings from external windows config file. I added the parameter to the connection string in this file and verified, that this is really the right way - BLOB fields where passed properly. But in design time, though it wasn't that important, I wanted to find a way how to pass this parameter and maintain usable connection manager. I tried to imagine how such edit window of connection manager may work. When you press OK, all properties you set are potentially verified and connection string is created. I hoped that the verification is not too strong, because I decided to attach TextSize parameter to one of properties in the dialog. I chose server name :). Guess what - there is no verification whatsoever of the server name on that dialog. So, my server name is now like
sybase_server,5335;TextSize=100000;
It works. It turned out that the solution is easy (as ususal), just pity that I spent so much time trying to nail it down.