SSIS row delimiter with % and ()

meroazure1244 96 Reputation points
2021-01-13T01:05:55.11+00:00

Hi,

I have a flat file with text like these:

( This rule of usa)

(This rule is for UK)

%------%

%--------

(

USA rule

If :x in 'abc'

Then 'apple'

)

%--------------

%--------------

(UK rule

If :x in 'MNO'

Then 'Mango'

)

%------%

%--------

I want to extract data from this flat file by keep all text with () into one row and one column

Like

( This rule of usa)

(This rule is for UK)

(USA ruleIf :x in 'abc' Then 'apple')

(UK rule If :x in 'MNO' Then 'Mango')

Can we do this in SSIS package?

%---------------------------

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,590 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Monalv-MSFT 5,901 Reputation points
    2021-01-13T02:42:12.743+00:00

    Hi @OmS-3269 ,

    1.Please set Format as Fixed width in the General page of Flat File Connection Manager.

    2.Please set the Row width as 239 in Columns page(239 is the whole length of data).

    3.Please use the Token expression in Derived Column Transformation.
    TOKEN(REPLACE([Column 0],"%","-"),"-",1) + TOKEN(REPLACE([Column 0],"%","-"),"-",3) + TOKEN(REPLACE([Column 0],"%","-"),"-",5)

    4.Please refer to the following pictures:
    56042-ffcm-general.png
    56043-ffcm-columns.png
    55918-ffsou.png
    56044-derivedcolumn.png
    56045-output.png

    Best Regards,
    Mona


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. meroazure1244 96 Reputation points
    2021-01-20T20:26:15.503+00:00

    Thanks for your reply. The row width is not fixed. It can by any as I have 100s of message with different width for rows. How can i make that auto detect. That was just sample message.

    %----------------------------------------------------------------------
    % FASDADFUX-IGNORE_UY732012250_SYN_S rule definition
    %----------------------------------------------------------------------
    (Rule FASDADFUX-IGNORE_UY732012250_SYN_S FMLU-RULES_FOR_US
    IF :HIT:OID: = "UY732012250";
    :HIT:HNM: = "SD";
    THEN :KEEPHIT: = 0;
    :RUSER-NAME: = "IGNORE_UY732012250_SYN_S";
    :HIT: = "0";
    :COHIT: = "0";
    :COMTHIT: = "";

    )

    %----------------------------------------------------------------------
    % FASDADFUX-IGNORE_1111111111111111111111111111111111111UY732012250_SYN_S rule definition
    %----------------------------------------------------------------------
    (Rule FASDADFUX-IGNORE_1111111111111111111111111111111111111UY732012250_SYN_S FMLU-RULES_FOR_US
    IF :HIT:OID: = "UY732012250";
    :HIT:HNM: = "SD";
    THEN :KEEPHIT: = 0;
    :RUSER-NAME: = "IGNORE_UY732012250_SYN_S";

    )

    %----------------------------------------------------------------------
    % FASDADFUX-IGNORE_UY732012250_SYN_S rule definition
    %----------------------------------------------------------------------
    (Rule FASDADFUX-IGNORE_xyz FMLU-RULES_FOR_US
    IF :HIT:OID: = "UY7320122501111";
    :HIT:HNM: = "SD";
    THEN :KEEPHIT: = 0;
    :RUSER-NAME: = "IGNORE_IGNORE_xyz";
    :COMTHIT: = "";

    )


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.