SSIS Bulk Import for files with different column names

Question

Problem.

I regularly receive files with different columns. Although the column names are consistent the problem comes when some text files with more or less columns.

Furthermore the arrangement of these files are inconsistent, I would like to import everything for each file in my database table.

I tried importing them using ssis package but it keeps of failing when it has to import other files, how do I go about importing these files.

Is it something possible or am I wasting my time with it? I'm fairly new on ssis.

File 1

Name|Surname|ID|Address

File 2

DB Table after importing

on top is just a simple example but the original files has much more complicated number of columns.

Subbu Subbu · Accepted Answer · 2017-09-10T16:30:11

The simplest way to insert the contents of a file into the SQL Server database is to use the Bulk Insert process

The following is an example from a popular article

BULK INSERT EmployeeDB.dbo.Employees
FROM 'C:\Data\EmployeeData_c.dat'
WITH
  (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\r\n'
  );

Since your file formats are different, one of the simplest approach is the following:

(1) For each of your file format, have a table. You can call it a staging table.

(2) You do a bulk insert of your file contents to this staging table

(3) After doing a bulk insert into this staging table, you can insert into your main table

You can achieve the above, by using command line tools like SQLCMD. You can implement via SSIS also. Generally, you would go to SSIS if you want more complex processing for your files. In you case, by preference would be do use the command line tools itself.

SSIS Bulk Import for files with different column names

1 Answers