Post reply

Data Conversion-Best practice

  • January 18, 2018 at 10:05 am

    #402847

    Hi,

    I have spend lots of time reading the articles but  not finding it very conclusive. There are various ways we can extract the data and push in the final tables. 
    One way is to load the raw data as-it-is and do the data conversions and other transformations in t-sql. Another way is to do it all in SSIS package. I am finding it difficult to identify if we should first load completely raw data as-it-is to the stage1 table and then pull it back to do data-type conversions to push to stage2tables  or should it be converted first and then pushed to stage1 table. 
    What do the best practice by experienced t-sql +ssis developers say. 

    Any suggestions are appreciated.

    Thanks a lot in advance.
    SQLSniffer

  • January 18, 2018 at 10:37 am

    #1976383

    sqlsniffer - Thursday, January 18, 2018 10:05 AM

    Hi,

    I have spend lots of time reading the articles but  not finding it very conclusive. There are various ways we can extract the data and push in the final tables. 
    One way is to load the raw data as-it-is and do the data conversions and other transformations in t-sql. Another way is to do it all in SSIS package. I am finding it difficult to identify if we should first load completely raw data as-it-is to the stage1 table and then pull it back to do data-type conversions to push to stage2tables  or should it be converted first and then pushed to stage1 table. 
    What do the best practice by experienced t-sql +ssis developers say. 

    Any suggestions are appreciated.

    Thanks a lot in advance.
    SQLSniffer

    I'd say minimise the amount of converting and calculating you do in SSIS and do it in T-SQL.

    Use SSIS as a tool to control processing logic and move data around.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 19, 2018 at 3:04 am

    #1976462

    Phil Parkin - Thursday, January 18, 2018 10:37 AM

    sqlsniffer - Thursday, January 18, 2018 10:05 AM

    Hi,

    I have spend lots of time reading the articles but  not finding it very conclusive. There are various ways we can extract the data and push in the final tables. 
    One way is to load the raw data as-it-is and do the data conversions and other transformations in t-sql. Another way is to do it all in SSIS package. I am finding it difficult to identify if we should first load completely raw data as-it-is to the stage1 table and then pull it back to do data-type conversions to push to stage2tables  or should it be converted first and then pushed to stage1 table. 
    What do the best practice by experienced t-sql +ssis developers say. 

    Any suggestions are appreciated.

    Thanks a lot in advance.
    SQLSniffer

    I'd say minimise the amount of converting and calculating you do in SSIS and do it in T-SQL.

    Use SSIS as a tool to control processing logic and move data around.

    Thanks Phil, its pretty clear now and removes the ambiguity.

  • January 25, 2018 at 7:16 am

    #1977321

    sqlsniffer it depends on what you want you to convert (datatype conversions or the data), how it's being converted, if you want to process records further (data cleansing/conditional logic/error handling) and if you want to retain the original structure (or format) of your source data. The beauty of SSIS is you don't need staging tables and it eases burden from the RDBMS system. SSIS can be very fast as processing happens in-memory and can be done in parallel streams (loading and converting simultaneously).

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply