Splitting Text
-
Consider the following table:
CREATE TABLE source_data
(
source_ID int,
source_description varchar(8000)
)
I need to output data from this table to a text file with the following structure, where target_description cannot exceed 255 characters:
target_ID, target_sequence, target_descriptionIf the value of source_ID is 1, and source_description exceeds 255 characters, I need to create multiple rows in the text file, with target_sequence being an incremented value for each new row. So if source_description contained 1020 characters, the result would be 4 rows, with target_ID being 1, and target_sequence being 1, 2, 3, and 4, and target_description being 255 character chunks from source_data.source_description.
So the question is: what is the most efficient way of doing this in SSIS? The source_data table could potentially have millions of rows.
Any help is appreciated!
TIA
-
Do you have a Numbers/Tally table?
If so, this is pretty easy to do.
select *,substring(LargeColumn, Number*255, 255) as SplitColumn
from dbo.MyTable
inner join dbo.Numbers
on Number <= len(LargeColumn)/255;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
I didn't, but I do now! So obvious when you see it. Thanks!
-
Did you build it properly? You might want to check out the article The "Numbers" or "Tally" Table: What it is and how it replaces a loop. [/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
You can implement this requirement using data flow script, where based on the description column the input row is split into multiple rows according to the algorithm you have described. However if the GSquared solution works for you, it will be better and easier to maintain.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply