How to import multiple csv files with different headers into database using SSIS?

0
votes

I am writing a SSIS package to import multiple files in a folder with variation in the columns into a database. But cant find solution as one table has 12 columns and other 2 columns and while setting up the data flow not getting how to make the tables dynamic irrespective of the columns. Any other solution better than SSIS is also appreciated

1
sql-serverssisbuild-automation
SSIS doesn't support dynamic columns. If your source or destination tables could have the definition of their columns change then the package will fail, as the metadata is holds on the table/file is stale. Why is the number of columns in your table changing on a regular basis though; that sounds more like bad table design. I'm used to files having their definition change from time to time, but tables generally don't change unless going through your development cycle; and then updating your package would be part of that. – Larnu
Agreed with Larnu with one caveat... it is possible that there are 2 or 3 different structures to import. On that case, you should use different subfolders for each file type. – Angel M.
Just having a reread: If, however, you mean you have different files which need to map to different tables, but the definitions of those tables/files are statics then it seems you're trying to create a "one size fits all" Source/Destination. You can't do that. You can interact with multiple sources and destinations in a single dataflow, but the definitions of the data within each flow needs to be defined. You could have, for example, 3 flows within a data flow task, all with definition definitions, and that would be fine; you could even make them the same using transformations and Union them. – Larnu
"subfolders" @AngelM.? What does the structure of the directories have to do with this? – Larnu
I mean, if there are 2 different type of files, need to create one subfolder for each and the same with the SSIS, linking each SSIS to one subfolder and importing in the corresponding Table. – Angel M.

1 Answers

0
votes

See the discussion in this link: Can SSIS import data from multiple files to multiple tables in loop?

You can use BCP or Bulk Insert to load files into different table.

From you description:

database has fix number of tables which are same in every database, but the 18 tables have different columns.... for example: DB1 has [db1].[T1],[db1].[T2] DB2 has [db2].[T1],[db2].[T2] So [db1].[T1] structure is same as [db2].[T1] but [db1].[T1] has diff structure than [db1].[T2]

What you need to do is to store File-Table mapping in a control table and then use Script Task to concatenate dynamic SQL Statement passed to Server.

sample