Replace web links (https or www) if exist in the column through SSIS

Question

Replace web links (https or www) if exist in the column through SSIS

PANDEY Prashant 145

Hello Experts,

Having requirement to change the web addresses (either http/https/www) wherever they are appearing in a column so that on click the link will get open in the new tab.

The data type of existing attribute is changed from "text format" to rich text box within CRM, the agenda is to convert all existing data if they have any web links, they must be clickable.

I came to know that if an anchor tab can be added before the actual web address that link then get converted to clickable, without disturbing other existing sentence/words.

an example mentioned below

https://www.google.com

<a href="https://www.google.com/">https://www.google.com/</a>

the existing data have all possible scenarios

like

can have a sentence before any web address, like British Woodworking Federation: https://www.bwf.org.uk/
can have only a single web address like https://www.bwf.org.uk/
can have multiple web address being used within sentences, like ACAD (Asbestos Control and Abatement Division): http://acad.tica-acad.co.uk/
ARCA (Asbestos Removal Contractors Association: http://www.arca.org.uk/
HSE (Health & Safety Executive): http://www.hse.gov.uk/
NFDC (National Federation of Demolition Contractors): http://demolition-nfdc.com/
UKATA (UK Asbestos Training Association): http://www.ukata.org.uk/
IATP (Independent Asbestos Training Providers): https://www.iatp.org.uk/
BOHS (British Occupational Hygiene Society): http://www.bohs.org/
RSPH (Royal Society for Public Health): https://www.rsph.org.uk/

so my requirement is to add anchor tag before and after the web address irrespective the recurrence and rest of the sentence remain same.

Thanks

Prashant Pandey

Yitzhak Khabinsky 26,586 Reputation points

2023-05-15T16:23:02.47+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
PANDEY Prashant 145 Reputation points

2023-05-16T09:39:40.8433333+00:00

Thanks Yitzhak for looking into this.

The data migration have to perform in SSIS where source/destination both are MS Dynamics, and SSIS/KingswaySoft is being used to read/transform data. NO SQL table/queries.

The expected outcome are mentioned below

Input Example: British Woodworking Federation: https://www.bwf.org.uk/

Output: British Woodworking Federation: <a href="https://www.bwf.org.uk/">https://www.bwf.org.uk/</a>

Input: [http://acad.tica-acad.co.uk/]

Output: <a href="http://acad.tica-acad.co.uk/">http://acad.tica-acad.co.uk/</a>

etc..

1 answer

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2023-05-15T16:23:02.47+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
PANDEY Prashant 145 Reputation points

2023-05-16T09:39:40.8433333+00:00

Thanks Yitzhak for looking into this.

The data migration have to perform in SSIS where source/destination both are MS Dynamics, and SSIS/KingswaySoft is being used to read/transform data. NO SQL table/queries.

The expected outcome are mentioned below

Input Example: British Woodworking Federation: https://www.bwf.org.uk/

Output: British Woodworking Federation: <a href="https://www.bwf.org.uk/">https://www.bwf.org.uk/</a>

Input: [http://acad.tica-acad.co.uk/]

Output: <a href="http://acad.tica-acad.co.uk/">http://acad.tica-acad.co.uk/</a>

etc..

Answer 1

ZoeHui-MSFT 41,491

Hi @PANDEY Prashant,

Please try with below steps.

Frist add a Derived Column:

first part: TOKEN(data,":",1)

second part: RIGHT(data,FINDSTRING(REVERSE(data),":",2) - 1)

User's image

And then add another Derived Column after that.

target:[first part] + ":" + "<a href=" + """ + [second part] + """ + ">" + [second part] + "</a>"

User's image

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

PANDEY Prashant 145 Reputation points

2023-05-18T14:27:35.8633333+00:00

Thanks ZoeHui,

this is working fine when the record have a single web address.

but failing when the record have many web address, like below case. All are in a single record only.

ACAD (Asbestos Control and Abatement Division): http://acad.tica-acad.co.uk/
ARCA (Asbestos Removal Contractors Association: http://www.arca.org.uk/
HSE (Health & Safety Executive): http://www.hse.gov.uk/
NFDC (National Federation of Demolition Contractors): http://demolition-nfdc.com/
UKATA (UK Asbestos Training Association): http://www.ukata.org.uk/
IATP (Independent Asbestos Training Providers): https://www.iatp.org.uk/
BOHS (British Occupational Hygiene Society): http://www.bohs.org/
RSPH (Royal Society for Public Health): https://www.rsph.org.uk/

all of the web address (can be one or more in a single record) need to add anchored tab. see another examples below

Kindly suggest.

Thanks.
ZoeHui-MSFT 41,491 Reputation points

2023-05-22T01:45:36.2466667+00:00

Hi @PANDEY Prashant,

The new you provided is a bit complex and i do not think we could not meet the requirement with SSIS.
PANDEY Prashant 145 Reputation points

2023-05-22T10:02:24.82+00:00

Thanks @ZoeHui-MSFT

Is it possible to do this through SQL queries, I can pull and store data in temp sql table.

Thanks
ZoeHui-MSFT 41,491 Reputation points

2023-05-23T01:49:33.21+00:00

Hi @PANDEY Prashant,

As the data you provided is no pattern, it is also hard to do that with tsql. Maybe you may do that via C# with script task but code writing is out of my ability.

Share via

Replace web links (https or www) if exist in the column through SSIS

1 answer

Your answer