SSIS expression substring and if else condition to separate data into two columns

Question

Source Table

            +----------------+
            | Identification |
            +----------------+
            | AB1234567      |
            | A234B5678      |
            +----------------+

Expected Result

            +-----------+-----------+
            |    ID1    |    ID2    |
            +-----------+-----------+
            | AB1234567 |           |
            |           | A234B5678 |
            +-----------+-----------+

Logic: An ID1 has two letters at the beginning followed by 7 numbers. An ID2 is a combination of letters and numbers in no particular order

What have you tried so far? – Chris Albert Jan 13 '22 at 21:58 — Chris Albert, Jan 13 '22 at 21:58

score 0 · Answer 1 · answered Jan 13 '22 at 22:13

This would be easier to accomplish with a query. If the source is SQL Server then query it directly. If the source is not SQL Server then load the data to a staging table and query it. The query sample below gives you the desired output.

CREATE TABLE #MyTable
(
    Identification VARCHAR(50)
);

INSERT INTO #MyTable VALUES ('AB1234567'), ('A234B5678');

SELECT
    CASE
        WHEN Identification LIKE '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
            THEN Identification
        ELSE NULL
        END AS ID1
    , CASE
        WHEN Identification LIKE '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
            THEN NULL
        ELSE Identification
        END AS ID2
FROM
    #MyTable AS MT;

DROP TABLE IF EXISTS #MyTable;

SSIS expression substring and if else condition to separate data into two columns

1 Answers1