Free SQL Help for Devs, DBAs, and the Curious

When working with flat files in SQL Server Integration Services (SSIS), one of the most common challenges is dealing with irregular delimiters. An irregular delimiter is a character or sequence of characters that separates the fields in a flat file, but that may not be the same from one record to the next. In this blog post, we will discuss how to use SSIS to transform flat files with irregular delimiters.

The first step in handling irregular delimiters in SSIS is to identify what the possible delimiters are. For instance, in many cases, fields in a flat file may be separated by a comma, a space, a tab, or a semicolon. However, in some cases, the delimiter may be a sequence of characters, such as "::" or "|~|". Once you have identified the possible delimiters, you can then set up your SSIS package to recognize and handle these delimiters.

The Flat File Source component in SSIS is designed to handle regular delimiters. However, it can also be used to handle irregular delimiters by setting the property "TextQualified" to false, and specifying the irregular delimiter in the "ColumnDelimiter" property. This can be done using a SQL Server 2016 or 2017 T-SQL script. For example:

-- T-SQL code for SQL Server 2016 or 2017

-- Set TextQualified to false and ColumnDelimiter to irregular delimiter

UPDATE SSISDB.catalog.set_object_parameter_value @folder_name=N'Folder',

@project_name=N'Project',

@parameter_name=N'TextQualified',

@parameter_value=N'false';

UPDATE SSISDB.catalog.set_object_parameter_value @folder_name=N'Folder',

@project_name=N'Project',

@parameter_name=N'ColumnDelimiter',

@parameter_value=N'::';

In MySQL 5.7, the LOAD DATA INFILE statement can be used to import a flat file with irregular delimiters. The FIELDS TERMINATED BY clause allows you to specify the delimiter. For example:

-- MySQL 5.7 code

LOAD DATA INFILE 'data.txt' INTO TABLE tbl

FIELDS TERMINATED BY '|~|';

In DB2 11.1, the IMPORT command can be used to import a flat file with irregular delimiters. The DELIMITED BY clause allows you to specify the delimiter. For example:

-- DB2 11.1 code

IMPORT FROM 'data.txt' OF DEL MODIFIED BY COLDEL|~| METHOD P (1, 2, 3) INSERT INTO tbl;

In Azure SQL, the BULK INSERT statement can be used to import a flat file with irregular delimiters. The FIELDTERMINATOR clause allows you to specify the delimiter. For example:

-- Azure SQL code

BULK INSERT tbl

FROM 'data.txt'

WITH (FIELDTERMINATOR = '|~|');

In conclusion, while handling irregular delimiters in flat files can be a challenge, SSIS and various SQL technologies provide tools and techniques for dealing with this issue. By using the right combination of settings and commands, you can successfully import and transform flat files with irregular delimiters in SSIS.