February 7, 2009 at 5:49 am
I have a text file with records in the format:
[###]
Text line 1
Text line 2
Text line 3
[###]
Text line 1
Text line 2
Text line 3
etc.
where ### is a unique integer id for the following 3 lines of text
Could anybody suggest an easy way of parsing this file so that i could pivot each entry into a row (id, line1, line2, line3)
Any solutions/ideas would be appreciated.
February 7, 2009 at 7:21 am
You can do this in many similar ways, here I am providing one of the ways
Variables:
@LastID (String): will contain the last unique integer id
Data Flow Task
>> Flat File Source
>> Derived Column [CurrentID] (This should check if the current row is unique integer id or not. If yes then the derived column should have the value of current unique intger id otherwise it should hold the value of @LastID)
>> Conditional Split (Direct the current row to outputs "DataOutput" & "IDOutput" based on the condition ([CurrentID] != @LastID && @LastID != "") & ( [CurrentID] = @LastID || @LastID = "") resp.)
>> Script Component (this should update the value of the variable @LastID to [CurrentID]
>> DataOutput (of Conditional Split)
>> OLE DB Destination (to a temp. staging table)
>> ID Row (of Conditional Split)
Execute SQL Task (Will contain the INSERT statement to main table from the temp. staging table)
--Ramesh
February 8, 2009 at 6:45 am
Thanks for the response Ramesh.
However, I've got a problem with the Script Component
>> Script Component (this should update the value of the variable @LastID to [CurrentID]
In that the variable can only be set within the PostExecute() method whereas the idea is that @LastId be set during row processing so that it can persist the value between subsequent rows.
I'm new to SSIS to might be missing something obvious to an SSIS guru.
Any ideas?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply