sql server integration services importing 2 tsv

Question

I have two .tsv files and I need to import them to sql server: one has the data (data.tsv) and the other is column_headers.tsv

I need to import it to a table, the table must be created with column_headers.tsv columns and the data.tsv should be inserted to that table.

I will need to do this a few times, so I would like to automate it… Can I achieve this with SSIS?

is there a standard process on how to import data to sql server using 2 tsv files: data.tsv and column_headers.tsv ? is there a standard process on how to create tables based on column names provided from a .tsv?

I know nothing about the data itself… Extra information I have for the data:

Datafeed-Manifest-Version: 1.0

Lookup-Files: 1

Data-Files: 1

Total-Records: 18,584

Lookup-File: c-it_2016-09-30-lookup_data.tar.gz

MD5-Digest: 7efa8c4e901bb666

File-Size: 3663715

Data-File: c-it_2016-09-30.tsv.gz

MD5-Digest: 6ece62faaaaa084f8a3d73b55

File-Size: 1066972

Record-Count: 18,584

Answer 1

No, there is no standard process for this.

You can write a script task that reads the column_headers file and generates a CREATE TABLE statement that it then executes.

Then you can follow that with a Data Flow Task that uses the data file as a source and imports the data to the table you created.