HI, I working on functionality in which the DB2 to SQL migration where DB2 data is provided in flat files. while executing my package, it is missing some of the rows to load. Later on found that for those rows, some of the column values are starting with low values(they appear as . or blank). Is there anyway to find the low values,handle and load them. Please suggest. Thanks.Porus

Hi, Load them where? Into a character field? There should be no problem loading a blank or a dot into a character field. However, if you try to load them into numeric fields there will be trouble. You'll have to either load data "as is" into a temporary table, then manipulate its content via SSIS expressions or T-SQL views. Or just design an SSIS process with all the logic inside.Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Thanks Sebastian. I am loading into character columns. But somehow, they are missing to load. Is there anyway to find them.Porus

What do you mean with "missing"? Is the entire row missing? Or just a column value? If the last one, what value does the column have? NULL?MCTS, MCITP - Please mark posts as answered where appropriate.

Hi, Open your text files with an hexa editor (like Notepad++ or Ultraedit) and look for non-printable characters in the middle of those "trouble" rows. Typically, rogue tabs (0x09), line feeds (0x0A), carriage returns (0x0D) or ASCII nulls (0x00) can be misinterpreted by SQL as row or field delimiters. That may happen in AS/400 : users enter 'John' then hit Tab (by accident) and finally 'Doe'. DB2/400 will store that field as [J][O][H][N][Tab control character][D][O][E], when you export to flat file you'll be left with an 0x09 (Tab character) in the middle of your text. Other databases (or applications) will store [J][O][H][N][spaces][D][O][E] which is easier to deal with. Finally, look at your MS-SQL table definition script and check that there are no constraints like YourField > SPACE(1) OR YourField<>'.' Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Hi, Open your text files with an hexa editor (like Notepad++ or Ultraedit) and look for non-printable characters in the middle of those "trouble" rows. Typically, rogue tabs (0x09), line feeds (0x0A), carriage returns (0x0D) or ASCII nulls (0x00) can be misinterpreted by SQL as row or field delimiters. That may happen in AS/400 : users enter 'John' then hit Tab (by accident) and finally 'Doe'. DB2/400 will store that field as [J][O][H][N][Tab control character][D][O][E], when you export to flat file you'll be left with an 0x09 (Tab character) in the middle of your text. Other databases (or applications) will store [J][O][H][N][spaces][D][O][E] which is easier to deal with. Finally, look at your MS-SQL table definition script and check that there are no constraints like YourField > SPACE(1) OR YourField<>'.' Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Hi, Open your text files with an hexa editor (like Notepad++ or Ultraedit) and look for non-printable characters in the middle of those "trouble" rows. Typically, rogue tabs (0x09), line feeds (0x0A), carriage returns (0x0D) or ASCII nulls (0x00) can be misinterpreted by SQL as row or field delimiters. That may happen in AS/400 : users enter 'John' then hit Tab (by accident) and finally 'Doe'. DB2/400 will store that field as [J][O][H][N][Tab control character][D][O][E], when you export to flat file you'll be left with an 0x09 (Tab character) in the middle of your text. Other databases (or applications) will store [J][O][H][N][spaces][D][O][E] which is easier to deal with. Finally, look at your MS-SQL table definition script and check that there are no constraints like YourField > SPACE(1) OR YourField<>'.' Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu