Parsing Unstructured Text file in SSIS and read the each line to get the required data

Question

Parsing Unstructured Text file in SSIS and read the each line to get the required data

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

OK, that looks like a cross join.
It would be easiest to put all of the DeviceIds into a staging table and all of the unit/value pairs into another and then doing the cross join in T-SQL.
Have a read here for some examples of reading non-standard formats using a script component.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

stakhilmadivada Old Hand Points: 372 More actions · Answer 2

Phil Parkin - Tuesday, June 13, 2017 12:03 PM
OK, that looks like a cross join.
It would be easiest to put all of the DeviceIds into a staging table and all of the unit/value pairs into another and then doing the cross join in T-SQL.
Have a read here for some examples of reading non-standard formats using a script component.

I need to take the TEXT file in SSIS and create the package to populate the data in the database,
To populate the data in the table how can I create the package in SSIS?
Image shows what data should populate in the database table from the file.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

stakhilmadivada - Tuesday, June 13, 2017 12:07 PM
Phil Parkin - Tuesday, June 13, 2017 12:03 PM
OK, that looks like a cross join.
It would be easiest to put all of the DeviceIds into a staging table and all of the unit/value pairs into another and then doing the cross join in T-SQL.
Have a read here for some examples of reading non-standard formats using a script component.
I need to take the TEXT file in SSIS and create the package to populate the data in the database,
To populate the data in the table how can I create the package in SSIS?
Image shows what data should populate in the database table from the file.

I understand what you want to achieve. Now it's your turn to learn how to write script components. I gave you a link and if you Google "SSIS script component unstructured file import" you will get a lot more. No one here is going to write the code for you unless you pay them.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

stakhilmadivada Old Hand Points: 372 More actions · Answer 4

Phil Parkin - Tuesday, June 13, 2017 12:16 PM
stakhilmadivada - Tuesday, June 13, 2017 12:07 PM
Phil Parkin - Tuesday, June 13, 2017 12:03 PM
OK, that looks like a cross join.
It would be easiest to put all of the DeviceIds into a staging table and all of the unit/value pairs into another and then doing the cross join in T-SQL.
Have a read here for some examples of reading non-standard formats using a script component.
I need to take the TEXT file in SSIS and create the package to populate the data in the database,
To populate the data in the table how can I create the package in SSIS?
Image shows what data should populate in the database table from the file.
I understand what you want to achieve. Now it's your turn to learn how to write script components. I gave you a link and if you Google "SSIS script component unstructured file import" you will get a lot more. No one here is going to write the code for you unless you pay them.

Thanks

stakhilmadivada Old Hand Points: 372 More actions · Answer 5

Lowell - Tuesday, June 13, 2017 8:37 AM
this was kind of interesting, so I imported the file into a varchar max column, and used two CrLf as the delimiter, and the pipe character as the column delimiter(since it does notexist int he data)
That gave me 99763 rows(with data containing CrLf), instead of 587,475 rows.
there are only 5 Device Id's in the whole file, and 27,704 'Units=' and 'Value=' pairs.
i was assuming that Units/Values would be related to the previous appearing DeviceId, but all Units/Value Pairs appear after the last DeviceId in the file
I'm not sure that data in in an order related to the previous row(s) of data.

Hi Lowell, can you explain me in detail how you did it?