Multiple different structured CSV files load into single table using SSIS

Question

For example,

I need to load all loan CSV files into a single table

I am getting loan contact feed from one third party, getting loan amount feed from another third party, like that I am getting each set of fields from different third party in multiple CSV file. I need to do load all CSV files into single table (Loan_Full_Details).

Note: Each CSV structure different but it won't change dynamically.

CSV1 - ID, Name, Contact_Number
CSV2 - ID, Sanction amount, Sanction date
Destination Table - Loan_Full_Det

Please help me on this request.

Answer 1

Hi @Govarthanan Venkatesan ,

The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output.

So we can use many Flat File Sources and one Union All Transformation in SSIS Data Flow Task.

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 2

There are two options you can try. Option one is to load different CSV files to the different staging tables and then to use T-Sql to merge data from those staging tables to the destination table. The other one is to load different CSV files in the Flat File Sources in the Data Flow and then to use Merge Join task to import data to the destination table.