none
Data Load from AX to Snowflake using SSIS using Blob storage. RRS feed

  • Question

  • Hi All,

    I must do a POC on Snowflake where I have to load data from AX Source to Snowflake. It is recommended that It is better to have Data loaded 1st to CSV file and then bulk copy into Snowflake for faster performance.

    So here Data load design should be something like this:

    1. Extract from AX and load to CSV and upload CSV to Azure Blob Storage [In order to optimize the number of parallel loads into Snowflake, it is recommended to create compressed data files that are roughly 10 MB to 100 MB in size] . https://hevodata.com/blog/snowflake-etl-best-practices-cloud-data-warehouse/
    2. Then from CSV to Snowflake is easy

    Challenges: I have 100 tables and out of 100 tables 15-20 tables are having 150 million records. If have to go with above approach, Table to Flat-file, I may need to split a single table data into multiple files with size max 100 MB. 

    What you be best way to do this using SSIS?


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Thursday, November 14, 2019 4:29 AM

All replies

  • Hi Shiven,

    • *.csv files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, and line breaks are in the middle of the actual data.
    • The most reliable format for data feeds is XML.
      I participated in one of the Snowflake webinars where I asked a question if they can ingest XML files.
      The answer I received was definitive YES.
      Please check it out on your end if that approach will work for you.

     

    P.S. You can find me on LinkedIn and Skype.

    Thursday, November 14, 2019 4:53 AM
  • Hi Shiven,

    • *.csv files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, and line breaks are in the middle of the actual data.
    • The most reliable format for data feeds is XML.
      I participated in one of the Snowflake webinars where I asked a question if they can ingest XML files.
      The answer I received was definitive YES.
      Please check it out on your end if that approach will work for you.

     

    P.S. You can find me on LinkedIn and Skype.

    Thanks for response.
    As we  don't have XML Destination in SSIS. How I am going to convert from table to XML file..?

    I found this link but it says workaround has limitation that can't handle larger volume of data. 

    https://www.sqlshack.com/ssis-xml-destination/


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Thursday, November 14, 2019 5:44 AM
  • Hi Shiven,

    It is a shame that even SSIS 2019 still doesn't have a built-in XML Destination Adapter.

    There are many ways to do it. Including very performant and scalable streaming.

    I have my own implementation of the XML Destination Adapter.

     

    P.S. You can find me on LinkedIn and Skype.

    Thursday, November 14, 2019 5:56 AM
  • Hi Shiven,

    It is a shame that even SSIS 2019 still doesn't have a built-in XML Destination Adapter.

    There are many ways to do it. Including very performant and scalable streaming.

    I have my own implementation of the XML Destination Adapter.

     

    P.S. You can find me on LinkedIn and Skype.

    I loaded around 40 million records (having 8 columns) to a flat files(.text) and file size is around 8 GB. I don't think XML file size going to be lesser than 8 GB for the same data volumes. 

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Thursday, November 14, 2019 6:49 AM
  • Hi Shiven,

    As Snowflake suggests it is better to split one huge file into few smaller files.

    This way the source table size doesn't matter, i.e. # of rows, # of columns and their data types.



    Thursday, November 14, 2019 6:54 AM
  • Hi Shiven,

    As Snowflake suggests it is better to split one huge file into few smaller files.

    This way the source table size doesn't matter, i.e. # of rows, # of columns and their data types.



    Do you know better method to split the files in smaller size? 

    https://stackoverflow.com/questions/24612375/ssis-export-all-data-from-one-table-into-multiple-files

    I can achieve this buy using Row_NUMBER() as Source is SQL and then based on this column can split the files using Foreachloop in SSIS. I am not sure about performance..

    If somebody knows better way to do this I will prefer checking options. 


    Thanks Shiven:) If Answer is Helpful, Please Vote


    Thursday, November 14, 2019 11:28 PM
  • Hi Shivendoo,

    The following link will be helpful:

    Using The SSIS Script Component With Multiple Outputs

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, November 15, 2019 7:54 AM