locked
Strange data truncation from source RRS feed

  • Question

  • Hi,

    I'm new to ETL and I'm experiencing something that I don't understand. I did a lot of research without success.

    I have a MySQL source from which a use the following query to get data :

    select
    	d.ID, 
    	ifnull((select group_concat(SKU) from MyTable where TABLE_ID = d.ID group by TABLE_ID ),'') as SKU
    from MyTable d
    group by d.ID
    order by d.ID

    When I used the query like that, to get every row from the table, the ETL succeed. BUT, the original data length is 559, but in the target column, in a MSSQL table, the data length is only 341. Becareful, I'm not talking about the column length. If a run the query on a mysql server, the result is fine and every data of the SKU column is there, so this is not a problem on the MySQL server side. Now, if a add a parameter to the query like that :

    select
    	d.ID, 
    	ifnull((select group_concat(SKU) from MyTable where TABLE_ID = d.ID group by TABLE_ID ),'') as SKU
    from MyTable d
    where d.ID = 1337212
    group by d.ID
    order by d.ID

    Like that, I'm targeting only one row, and now I'm having a full SKU of 559 length. I have to admit that I don't understand why I'm having this trouble. Is there some sort of buffer somewhere that need to be set ?

    My column in MSSQL is of type varchar(4000) in the ETL I have an ADO.NET source + a Data conversion that convert my SKU to a DT_STR of 4000 + an ADO.NET destination. Everything run fine whitout even a warning.

    Any help will be welcome :)

    Thank you.

    Daniel



    • Edited by LETL Wednesday, April 15, 2015 5:59 PM
    Wednesday, April 15, 2015 5:56 PM

Answers

  • select
    	d.ID, 
    	CAST(ifnull((select group_concat(SKU) from MyTable where TABLE_ID = d.ID group by TABLE_ID ),'') AS VARCHAR(600)) as SKU
    from MyTable d
    group by d.ID
    order by d.ID

    Good, that did the trick. Just for your information the type must be char() and not varchar()

    CAST(ifnull((select group_concat(SKU) from MyTable where TABLE_ID = d.ID group by TABLE_ID ),'') AS CHAR(600))

    Thank you very much.

    • Marked as answer by LETL Wednesday, April 15, 2015 6:32 PM
    Wednesday, April 15, 2015 6:32 PM

All replies

  • SQL Server uses the first 100 rows of the result set to determine the size of the field.  My guess is the max length in the first 100 rows is 341.

    If you want it a specific size, you need to create it that size.

    Wednesday, April 15, 2015 6:00 PM
  • I use this number as an exemple, but in the 30 first row I have a 600+ characters value in the SKU. It's not always the same number of characters in the column. But. how do you create it that size - what do you mean ?

    Thanks for the answer.


    • Edited by LETL Wednesday, April 15, 2015 6:07 PM
    Wednesday, April 15, 2015 6:06 PM
  • select
    	d.ID, 
    	CAST(ifnull((select group_concat(SKU) from MyTable where TABLE_ID = d.ID group by TABLE_ID ),'') AS VARCHAR(600)) as SKU
    from MyTable d
    group by d.ID
    order by d.ID

    Wednesday, April 15, 2015 6:14 PM
  • select
    	d.ID, 
    	CAST(ifnull((select group_concat(SKU) from MyTable where TABLE_ID = d.ID group by TABLE_ID ),'') AS VARCHAR(600)) as SKU
    from MyTable d
    group by d.ID
    order by d.ID

    Good, that did the trick. Just for your information the type must be char() and not varchar()

    CAST(ifnull((select group_concat(SKU) from MyTable where TABLE_ID = d.ID group by TABLE_ID ),'') AS CHAR(600))

    Thank you very much.

    • Marked as answer by LETL Wednesday, April 15, 2015 6:32 PM
    Wednesday, April 15, 2015 6:32 PM