When I hit PREVIEW in SSIS DB2 OLE DB Source component, I can see the data correctly but when I load it into some destination file or database table, the content will be lost. NULL. Other rows are OK but the ones with some special characters will get lost.
http://hot.ee/phil/work/DB2_zero_character.png
How to fix this? How to load correctly from DB2 with SQL Server Integration Services?
I have tried Microsoft OLEDB driver for DB2 and also IBM OLEDB provider for DB2, they both have the same issue.
Any example or reference to "special characters ("zero characters")"?
Why the image shows PowerBI?
You need to find at what stage the chars disappear
I propose you use the Data Viewer initially (double-click in the connector) to inspect for values, both on the source and destination.
DB2 administrator told me that some of those values on the picture (first 5 rows) contain "null character" which terminates the string, but with fastload script (OLEload script) they were translated to spaces. This was OK.
Now with SSIS they are converted to NULL and the values are lost (' 1', ' 2', ' 5', ' 6', ' 8'). But this is an identity column and I need those.
Might be this one: http://en.wikipedia.org/wiki/Null_character
Preview in SSIS shows the correct result but when I tried to load it into different destinations and with different options that I could think of, the result was that those values on the first 5 rows were converted to NULL.