Extracting part of string and loading

Remo522 21 Reputation points
2020-09-21T15:11:13.61+00:00

hello folks,

i have a column i trying to load into destination but not sure how to do it in ssis?

the data is like this below

column1 :

<div class="ExternalClassDB075A28F1AB44DA882ED25EFAA3FD94">Take trial testimony & attempt to settle up to 175k</div>

just want to only extract this part - 'Take trial testimony & attempt to settle up to 175k' and load it to destination.

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

3 answers

Sort by: Most helpful
  1. Guoxiong 7,681 Reputation points
    2020-09-21T19:24:50.05+00:00

    You can create a view to retrieve the text from the tag <div>...</div>, and then use SSIS to load that column to the destination table.

    DECLARE @c varchar(1000) = '<div class="ExternalClassDB075A28F1AB44DA882ED25EFAA3FD94">Take trial testimony &amp; attempt to settle up to 175k</div>';
    SELECT SUBSTRING(@c, CHARINDEX('>', @c) + 1, CHARINDEX('</div>', @c) - CHARINDEX('>', @c) - 1);
    

  2. Yitzhak Khabinsky 20,011 Reputation points
    2020-09-21T20:22:45.21+00:00

    Hi @Remo522 ,

    What data type is your source column?
    Is it XML by any chance?

    Check it out.

    T-SQL

    -- DDL and sample data population, start  
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col NVARCHAR(MAX));  
    INSERT INTO @tbl (col) VALUES  
    (N'<div class="ExternalClassDB075A28F1AB44DA882ED25EFAA3FD94">Take trial testimony &amp; attempt to settle up to 175k</div>'),  
    (N'<div class="ExternalClass5D1ECD3D7E8E4613B043854E073CD5E9">  
    	<span id="ms-rterangepaste-start">  
    	</span>Continuance is being filed as we have a May 4th trial setting. Authority to settle Joshua Francis up to 240,000<span id="ms-rterangecursor-start">  
    	</span><span id="ms-rterangecursor-end"></span></div>');  
    -- DDL and sample data population, end  
      
    ;WITH rs AS  
    (  
    	SELECT *   
    		, colXML = TRY_CAST(col AS XML)  
    	FROM @tbl  
    )  
    SELECT rs.ID  
    	, c.value('(./text())[1]','VARCHAR(100)') AS colText  
    FROM rs CROSS APPLY colXML.nodes('/div') AS t(c);  
    

    Output

    ID colText  
    1 Take trial testimony & attempt to settle up to 175k  
    2 Continuance is being filed as we have a May 4th trial setting. Authority to settle Joshua Francis up  
    

  3. Monalv-MSFT 5,686 Reputation points
    2020-09-22T02:54:42.77+00:00

    Hi @Remo522 ,

    Please use TOKEN(@[User::Variable],"><",2) in Derived Column Transformation to get your desired part of data.

    I have tested successfully in Derived Column Transformation.

    I have also tested successfully in the Expression Builder of the variable.

    Please refer to the following link and pictures:
    TOKEN (SSIS Expression)

    26293-derivedcolumntoken.png
    26294-derivedcolumnoutput.png
    26140-variables.png
    26295-expressionbuilder.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.