sql server - SSIS Bulk Import for files with different column names -

problem.

i regularly receive files different columns. although column names consistent problem comes when text files more or less columns.

furthermore arrangement of these files inconsistent, import each file in database table.

i tried importing them using ssis package keeps of failing when has import other files, how go importing these files.

is possible or wasting time it? i'm new on ssis.

file 1

name|surname|id|address

file 2

name|surname|address|spouse|work|child1|child2

db table after importing

name|surname|id|address|spouse|work|child1|child2

on top simple example original files has more complicated number of columns.

the simplest way insert contents of file sql server database use bulk insert process

the following example popular article

bulk insert employeedb.dbo.employees 'c:\data\employeedata_c.dat'   (     datafiletype = 'char',     fieldterminator = '|',     rowterminator = '\r\n'   ); 

since file formats different, 1 of simplest approach following:

(1) each of file format, have table. can call staging table.

(2) bulk insert of file contents staging table

(3) after doing bulk insert staging table, can insert main table

you can achieve above, using command line tools sqlcmd. can implement via ssis also. generally, go ssis if want more complex processing files. in case, preference use command line tools itself.