loading multiple file types into one table

  • We have scenario where we want to load different types of files(csv, xls, txt) into one table using SSIS.

    How can we implement it dynamically?

    The challenge is each file has different layout, but we have mapping available for each file. (apporx # of file 100+)

    Abhijit - http://abhijitmore.wordpress.com

  • The book Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution describes such a scenario.

    Basically you do everything in a .NET script task.

    You check the file type, choose the corresponding OLE DB adaptor, read the data into a file table and use the SQLBulkCopy class to bulk load it into the SQL database (which is feasible since you already have the mapping).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • that pretty awesome Koen, but do you have any sample for reference.

    Abhijit - http://abhijitmore.wordpress.com

  • Abhijit More (4/3/2014)


    that pretty awesome Koen, but do you have any sample for reference.

    No, sorry.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • We are saving mapping in below format. The other approach is saving it in vertical format.

    File NameCol1Col2Col3Col4

    File 1.xlsField1Field2Field3Field4

    File 2.csvField1Field3Field2Field4

    File 3.xlsField4Field3Field2Field1

    File 4.tabField2Field4Field3Field1

    The only constraint we faced here is using mapping how to achieve it through SSIS?

    Abhijit - http://abhijitmore.wordpress.com

  • Abhijit More (4/3/2014)


    We are saving mapping in below format. The other approach is saving it in vertical format.

    File NameCol1Col2Col3Col4

    File 1.xlsField1Field2Field3Field4

    File 2.csvField1Field3Field2Field4

    File 3.xlsField4Field3Field2Field1

    File 4.tabField2Field4Field3Field1

    The only constraint we faced here is using mapping how to achieve it through SSIS?

    To use this natively in SSIS you'd have to generate your SSIS packages (either programatically or by BIML).

    You could use the mapping if you use .NET to transfer the data, using the SQLBulkCopy class.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • What datatype are you using for 'File Name'?

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • FileName VARCHAR(255)

    Abhijit - http://abhijitmore.wordpress.com

  • Are you using FILESTREAM?

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • free_mascot (4/3/2014)


    Are you using FILESTREAM?

    FILESTREAM is for storing BLOB data, such as images or PDFs, not for storing the data in individual columns.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Why not use a staging area to load 4 tables and later use a union to create one table

    Raunak J

Viewing 11 posts - 1 through 10 (of 10 total)

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