Truncating HTML.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Truncating HTML.

Truncating HTML.

Rate Topic

Display Mode

Topic Options

Author

Message

sbrocklehurst

Posted Monday, November 12, 2012 7:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 12, 2012 7:59 AM
Points: 5, Visits: 2

I've an odd problem truncating HTML.

It's taken from as NTEXT which I'm then attempting to truncate by replacing the field in a Derived Column

(DT_WSTR,32000)EmailBody

Which keeps it as an NTEXT field. This generally works, but hasn't for a handful of cases, where the length of the field afterwards appears to be twice that of what I've suggested (ie 64000) - which is too big.

Any ideas?

Stephen.

Post #1383677

Koen Verbeeck

Posted Monday, November 12, 2012 7:20 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378, Visits: 6,473

Replacing a field doesn't actually do the conversion.
You need to create a new column and add the expression (DT_WSTR,32000)EmailBody there.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1383687

sbrocklehurst

Posted Monday, November 12, 2012 7:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 12, 2012 7:59 AM
Points: 5, Visits: 2

Tried that!
That's a problem in itself, as it'll then try to conform it to WSTR which is only 4000 characters long.

Still thanks for the reply Smile

It does look as if it's truncating some records, but not others. Could this relate to how many lines of text it'd translate to?

Stephen.

Post #1383690

Koen Verbeeck

Posted Monday, November 12, 2012 7:25 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378, Visits: 6,473

edit: never mind, you cannot specify a length for DT_NTEXT.
What is the datatype of the destination?

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1383692

sbrocklehurst

Posted Monday, November 12, 2012 7:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 12, 2012 7:59 AM
Points: 5, Visits: 2

Can't stick a number in the DT_NEXT command unfortunately.

Post #1383695

sbrocklehurst

Posted Monday, November 12, 2012 7:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 12, 2012 7:59 AM
Points: 5, Visits: 2

Rich Text format 32768chars.

I'm trying the following based on your idea.
1. Create a dummy ntext field earlier in the process
2. Truncate the field into the dummy field
3. Load from the dummy field.

Stephen.

Post #1383700

sbrocklehurst

Posted Monday, November 12, 2012 8:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 12, 2012 7:59 AM
Points: 5, Visits: 2

nope - didn't work - same error.

Post #1383717

Koen Verbeeck

Posted Tuesday, November 13, 2012 1:20 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378, Visits: 6,473

What should work:

Read the column into a script component as a transformation. In the .NET script, cast the DT_NTEXT (which is a BLOB column) to a string. Apply the substring function to truncate the string. Recast it to a BLOB and output it.

I have code at work that's similar to this. I'll see if I can track it down tomorrow.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1384273

« Prev Topic | Next Topic »

Permissions