Skip to content
Advertisement

Use SSIS to split a single field value into multiple rows in a second table

So the situation is I am writing an SSIS package to migrate data from an old database to a new database. In the old database we have a Text column called comments that is filled with sometimes 30MB of text. Most of these are comment threads that have time stamps. I would like to use the timestamps by using a regex or some such thing to split the data up and move it to a second child table called comments. It then needs to reference the PK of the original record as well. Thanks!

So

Table1 [Profile]
PK | Comments
1  | '<timestamp> blah <timestamp> blah blah'
will turn into 
Table1 [Profile]
PK | Comments
1  | ''
Table2 [Comments]
PK | FK | Comment
1  | 1  | '<timestamp> blah'
2  | 1  | '<timestamp> blah blah'

Advertisement

Answer

As wp78de suggested I resolved this by creating a script task and modified the output as it copies.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement