March 31, 2020 at 4:23 pm
Hello. I hope everyone is safe.
I have to import a tab delimited text file into SQL Server and I'm using SSIS. This is something I have done many times, but this text file is different. When you look at it, it really looks for like several text files appended together. Even though I can open the file without any problems in Excel, the flat file connection manager will not read it correctly. When you open the file in Notepad++ you can see the tabs and carriage returns, so I don't really understand how it get around this.
Here is an excerpt from the text file. It looks like FHD is the file header, and each RHD is a row header setting off a new segment of data( BWR, then ABL rows). SSIS does not recognize the carriage return after the 12 is the second row, and puts "12BWR" into the same cell. Then everything is messed up.
FHD 01 01 PLX 000075 001913 03/19/2020 12.13.31
RHD 01 BWR 03 12
BWR 6780xxxxx 293xxxxx 01 3585xxxxx
BWR 6786xxxxx 6032xxxxxxxxxxxx 01 5
BWR 6787xxxxx 5424xxxxxxxxxxxx 01 4
RHD 01 ABL 02 12
ABL 6780xxxxx 293xxxxxx 03/19/2020 1
ABL 6786xxxxx 6032xxxxxxxxxxxx 03/19/2020 1
This is a unique text file, but I'm hoping other people have run into something like this. I did find a method to suing a script transformation editor for use with odd delimiters, but since my delimiter is TAB and not something like "~" it didn't work. Anyone have ideas?
Thank you,
Amy
March 31, 2020 at 5:16 pm
Is each row type going to a different target table?
Are the rows linked to one another (ie, are there any parent/child relationships in the data, or does the row order need to be maintained for any reason)?
If there are relationships between the rows, please describe them.
From what I am seeing, I suspect that an asynchronous script component may be needed, but there is also a small chance that you will be able to get by with a conditional split and lots of derived columns. Or possibly by pre-processing the input file to make it more SSIS-friendly.
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
March 31, 2020 at 6:06 pm
Thank you for the reply. The rows are linked, but I wouldn't necessarily say there is a child / parent relationship. As in there are children, but I'm not sure what the parent would be. Information about one person is spread out into the different sections, marked by the RHD row. So one person has information in the BWR section, then more in the ADL section. Once in SQL the data gets put in separate tables as indicated by the 3 letter heading.
Does this help?
March 31, 2020 at 6:48 pm
It would also help if you have a file specification from whoever is sending this file. They should be able to tell you what each field represents and the relationship for each record in the file.
In Notepad++ you want to show all characters. This will help determine what the record terminator characters actually are - which could be CR or LF or CR/LF.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 31, 2020 at 10:01 pm
Thank you for the reply. The rows are linked, but I wouldn't necessarily say there is a child / parent relationship. As in there are children, but I'm not sure what the parent would be. Information about one person is spread out into the different sections, marked by the RHD row. So one person has information in the BWR section, then more in the ADL section. Once in SQL the data gets put in separate tables as indicated by the 3 letter heading.
Does this help?
Are you saying that there are multiple tables containing person info, but no constraints enforcing referential integrity between those tables? Not good, but makes loading the tables easier.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply