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.
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
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
15 People are following this question.
