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.
2,504 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,201 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);
    1 person found this answer helpful.
  2. Yitzhak Khabinsky 25,726 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
    1 person found this answer helpful.
  3. Monalv-MSFT 5,896 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.

    1 person found this answer helpful.