locked
Loading hundreds of flat files with different schema into SQL SERVER RRS feed

  • Question

  • hi folks:

      I am working on a project to retire an ERP system . One of the task is to output the ERP data  into different flat files and transport to a database. As a result, there are hundreds of flat files and each of them represents a table.  

      I am wondering if there are any automated way to load these flat files into sql server as the manual process to create one file manager at a time is just not doable.  Each flat files contains data types such as string, integer, boolean, etc. 

     Thanks

     Hui


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Wednesday, March 2, 2016 7:13 PM

Answers

  • Hi cat_ca,

    If you could tell what is this ERP system we could potentially recommend a better approach.

    With the ERP systems you need to follow a proper order of loading the data. And to make it automated you need to create a program say in C# that discovers the file structure and creates the package.

    You need to check the files and the destination so it is quite tedious. Some data may not fit into the file columns, any binary data would not upload (as say employee images).


    Arthur

    MyBlog


    Twitter

    • Marked as answer by cat_ca Thursday, March 3, 2016 9:47 PM
    Wednesday, March 2, 2016 7:39 PM
  • Yes. But it's a bunch of work.

    I've built an SSIS package which when pointed at a folder will attempt to load all the files it finds into the targeted database (using parameters for server, database and schema, and the file/worksheet name as the table name).

    SSIS does not like dynamic meta data. It'll just balk at you if you try.

    However, you can fake an UNPIVOT and load your data into a single table if you're sneaky. You can then take that table, and with a little clever dynamic PIVOT spin back on it's side, and load the values into the separate tables from there.

    Here's a screen grab of the concept.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by cat_ca Thursday, March 3, 2016 9:47 PM
    Wednesday, March 2, 2016 7:47 PM
  • Hi Hui,

    What you can do is to build the dataflowtask dynamically, by this way you can have dynamic metadata and use it as your wish.

    Please see the below link to understand on how to develop dataflow task programatically.

    https://msdn.microsoft.com/en-us/library/ms135997.aspx

    Regards

    Naveen



    • Edited by Naveen Kumar K.R Thursday, March 3, 2016 6:02 AM
    • Marked as answer by cat_ca Thursday, March 3, 2016 9:47 PM
    Thursday, March 3, 2016 6:02 AM

All replies

  • Hi cat_ca,

    If you could tell what is this ERP system we could potentially recommend a better approach.

    With the ERP systems you need to follow a proper order of loading the data. And to make it automated you need to create a program say in C# that discovers the file structure and creates the package.

    You need to check the files and the destination so it is quite tedious. Some data may not fit into the file columns, any binary data would not upload (as say employee images).


    Arthur

    MyBlog


    Twitter

    • Marked as answer by cat_ca Thursday, March 3, 2016 9:47 PM
    Wednesday, March 2, 2016 7:39 PM
  • Yes. But it's a bunch of work.

    I've built an SSIS package which when pointed at a folder will attempt to load all the files it finds into the targeted database (using parameters for server, database and schema, and the file/worksheet name as the table name).

    SSIS does not like dynamic meta data. It'll just balk at you if you try.

    However, you can fake an UNPIVOT and load your data into a single table if you're sneaky. You can then take that table, and with a little clever dynamic PIVOT spin back on it's side, and load the values into the separate tables from there.

    Here's a screen grab of the concept.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by cat_ca Thursday, March 3, 2016 9:47 PM
    Wednesday, March 2, 2016 7:47 PM
  • Hi Hui,

    What you can do is to build the dataflowtask dynamically, by this way you can have dynamic metadata and use it as your wish.

    Please see the below link to understand on how to develop dataflow task programatically.

    https://msdn.microsoft.com/en-us/library/ms135997.aspx

    Regards

    Naveen



    • Edited by Naveen Kumar K.R Thursday, March 3, 2016 6:02 AM
    • Marked as answer by cat_ca Thursday, March 3, 2016 9:47 PM
    Thursday, March 3, 2016 6:02 AM
  • Thanks ArthurZ, Patrick Hurst and Naveen, seems I have to build the dataflowtask dynamically, will

    dig deeper to understand...


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Thursday, March 3, 2016 5:55 PM
  • ERP was called Navision

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    • Marked as answer by cat_ca Thursday, March 3, 2016 9:47 PM
    • Unmarked as answer by cat_ca Thursday, March 3, 2016 9:47 PM
    Thursday, March 3, 2016 9:46 PM