How to import flat files with a varying number of columns in SQL Server

Posted on Posted in custom script task, Database, Development, Script, SQL 2012, SQL 2014, SQL 2016, T-SQL

Ever been as frustrated as I have when importing flat files to a SQL Server and the format suddenly changes in production?

Commonly used integration tools (like SSIS) are very dependent on the correct, consistent and same metadata when working with flat files.

So I’ve come up with an alternative solution that I would like to share with you.

When implemented, the process of importing flat files with changing metadata is handled in a structured, and most important, resiliant way. Even if the columns change order or existing columns are missing.

Background

When importing flat files to SQL server almost every standard integration tool (including TSQL bulkload) requires fixed metadata from the files in order to work with them.

This is quite understandable, as the process of data transportation from the source to the destination needs to know where to map every column from the source to the defined destination.

Read the full blogpost HERE.

Leave a Reply

Your email address will not be published. Required fields are marked *