Post reply

How to load different CSV files to different SQL tables

  • December 17, 2015 at 10:54 am

    #314258

    Hi All,

    I have a requirement in which I have multiple CSV files ( columns and data are different in each file ) and I need to load this into different SQL tables.

    One option that I can think of is; Use one DFT and inside that DFT use different set of FilatFileSource,DataConvertion,OLED Destination etc...

    i.e For file one FlatFileSource1, DataConvertion1, OLEDB Table1

    For file two FlatFileSource2, DataConvertion2, OLEDB Table2

    etc..

    But this option is not good if I have more number of files say 20 or 40 files...

    Is there any other way of achieving it ? Say by using For Each Container ? I can loop thorough and pick file one by one.. but how can I map it to the corresponding destination table dynamically?

    can some suggest any solution?

    Thanks & Regards,
    MC

  • December 17, 2015 at 11:12 am

    #1846632

    only4mithunc (12/17/2015)

    Hi All,

    I have a requirement in which I have multiple CSV files ( columns and data are different in each file ) and I need to load this into different SQL tables.

    One option that I can think of is; Use one DFT and inside that DFT use different set of FilatFileSource,DataConvertion,OLED Destination etc...

    i.e For file one FlatFileSource1, DataConvertion1, OLEDB Table1

    For file two FlatFileSource2, DataConvertion2, OLEDB Table2

    etc..

    But this option is not good if I have more number of files say 20 or 40 files...

    Is there any other way of achieving it ? Say by using For Each Container ? I can loop thorough and pick file one by one.. but how can I map it to the corresponding destination table dynamically?

    can some suggest any solution?

    There is no 'dynamic mapping' capability in SSIS at run time. You'll need (at minimum) as many data flows as you have file formats.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 17, 2015 at 11:21 am

    #1846633

    Phil Parkin (12/17/2015)

    only4mithunc (12/17/2015)

    Hi All,

    I have a requirement in which I have multiple CSV files ( columns and data are different in each file ) and I need to load this into different SQL tables.

    One option that I can think of is; Use one DFT and inside that DFT use different set of FilatFileSource,DataConvertion,OLED Destination etc...

    i.e For file one FlatFileSource1, DataConvertion1, OLEDB Table1

    For file two FlatFileSource2, DataConvertion2, OLEDB Table2

    etc..

    But this option is not good if I have more number of files say 20 or 40 files...

    Is there any other way of achieving it ? Say by using For Each Container ? I can loop thorough and pick file one by one.. but how can I map it to the corresponding destination table dynamically?

    can some suggest any solution?

    There is no 'dynamic mapping' capability in SSIS at run time. You'll need (at minimum) as many data flows as you have file formats.

    Agree.

    What you'll need to do is for each file, determine the file format, then execute the corresponding dataflow.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • December 21, 2015 at 8:39 am

    #1847158

    Thanks for your response.

    Thanks & Regards,
    MC

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

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