How to read different files with different formats and insert data into different tables using single SSIS package?

0
votes

I need create single SSIS package to read data from multiple files of different formats(number of columns, different delimiters, different column names) and insert into different tables.

For example: I will receive

file1, file2...file10

And each has different format and I need to insert into different tables

Tabl1,Table2 ,....Table10.

All this operation should be done using single SSIS package dynamically.

1
ssis
Welcome to Stackoverflow. Please, read MCVE and on-topic sections from Help Center, to understand how to ask questions. Thanks. – Farside
What do you mean by "dynamically?" Where should this dynacism take place? – billinkc
I mean to say, Single package should be able to load data from multiple files of different formats into loading tables. – Chandu Vemula

1 Answers

0
votes

One way would be to store metadata about each of the inbound files in a database table, access that metadata during package execution, and then use expression-based precedence constraints to determine which Dataflow (and corresponding flat file connection manager) to use for loading each file format.enter image description here

1) Use a SQL Script Task to load the file metadata into a system.object variable (set via the SQL Script Task's ResultSet property)

2) Use a Foreach Loop Container (specifically a "Foreach ADO Enumerator") to iterate over the metadata table, loading key metadata elements into package variables (value index, number of header rows, delimiters, file path/name, etc)

3) Add an expression task to to the Foreach container to serve as a precedence entity (setting the expression to something that always evaluates to true like "1==1" will suffice - we just need this task to serve as an entry point to the foreach container).

4) Add a data flow for each of the different file formats. Each dataflow will contain a Flat File Connection Manager whose "Expressions" property can then be used to dynamically set properties such as filename, path, number of header rows, delimiter, etc.

5) Add expression-based precedence constraints to the data flows to evaluate the file's metadata and determine which DataFlow task it should be sent to for processing.