question

trinityr avatar image
trinityr asked

Parsing a Text File SSIS

I have a complicated task and I am looking for direction. I have a test file that returns a set of records and I need to be able to group the text into SQL Server to be used for a report. Here's an example of the data. Key Row - unique data row - unique data row - unique data row Key Row - unique data row - unique data row - unique data row - unique data row - unique data row Key Row - unique data row - unique data row - unique data row Basically, all the data between each key row is 1 report. How would I loop thru this and export a report for each Key Row.
ssisreportparsingflat-file
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Is the layout you posted how it looks in a flat file or is it in tables already (or do you even want it in tables)? What do you mean by "export a report for each Key Row"? Are you exporting to a flat file, displaying it in reporting services, or something else?
0 Likes 0 ·
trinityr avatar image trinityr commented ·
Sorry for the choppy submission. It's currently in a flat file format. I'd like to utilize Reporting Services. Which means I would need to put this into a table probably XML formatted. Here's a better example of the data...maybe.
P123400000432
Random Data
Yes this would be nicer
and cleaner if it was formatted better
Not sure
P123400000543
Wahoo!
Formatted...not really
Special characters?  You bet $#%
exact number of lines?
Now way
P123400000568
Now it's the end
no more records
no flagg that it's the end
it just ends like this
0 Likes 0 ·

1 Answer

·
tomgough79 avatar image
tomgough79 answered
You could perhaps import it with SSIS using a script task as the data source. Maybe cycle through it row by row, when you hit the first key, put it into a variable then start looping through the data after it, each time outputting a row comprising 2 columns, key and data. When you hit the next key, repeat the process and continue until you hit the end of the file Pour that into a table like: create table Destination ( key nvarchar(50), data nvarchar(255) ) Then just use this for your report - you can just put it in a table using "key" as the group
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.