Problem Importing Text Files With Binary Zeros (0x00) Via SSIS(SQL2005). It Is All Fine When Using DTS(SQL2000) Though.

Hi.

There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following:
A17 123.17 first row
BB29 493.19 second
ZZ3 18947.1 third row is longer
And in hex format:
00: 41 31 37 20 20 20 20 20 ”‚ 31 32 33 2E 31 37 20 20 A17 123.17
10: 66 69 72 73 74 20 72 6F ”‚ 77 0D 0A 42 42 32 39 20 first row™ª—™BB29
20: 20 00 20 34 39 33 2E 31 ”‚ 39 20 20 73 65 63 6F 6E 493.19 secon
30: 64 0D 0A 5A 5A 33 20 20 ”‚ 20 20 20 31 38 39 34 37 d™ª—™ZZ3 18947
40: 2E 31 20 74 68 69 72 64 ”‚ 20 72 6F 77 00 69 73 20 .1 third row is
50: 6C 6F 6E 67 65 72 ”‚ longer

I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).

The table is:

CREATE TABLE eng (


src varchar (512)

)

When i upload this file into SQL2000 using DTS or Import wizard, the table contains:

select src, substring(src,9,8), len(src) from eng
< src ><substr> <len>
A17 123.17 first row 123.17 25
BB29 493.19 22
ZZ3 18947.1 third row 18947.1 35

As one can see, everything was imported, including binary zeros. And though SELECT * in SSMS truncates strings upon reaching 0x00's, still all information is stored in tables - SUBSTRINGs show that.

When i upload this file into SQL2005 using SSIS or Import wizard the result is following:
< src ><substr> <len>
A17 123.17 first row 123.17 25
BB29 4
ZZ3 18947.1 third row 18947.1 25

This time table is half-empty - all characters behind binary zeros in respective rows are lost.

I stumbled upon this problem while migrating my DTSes to SSIS packages. Do you think there is some workaround, or i need to turn on some checkbox or smth else could help? Please...