August 5, 2019 at 10:13 pm
Thanks in advance for any help! I have a csv file that has a header row in which the header column titles have some special characters in them. For example, the column for a diagnosis code (we are a healthcare organization) might have the heading "Diagnosis: Primary". For reasons I won't get into here, I need the flat file to import as a data source but SSIS keeps importing the header without the colon. It just removes it. I have to have the column headings exactly as they come to me, so this is a major issue. If I don't tell it that my first row is column headings, it imports the first row as if it were data, with the colon where it is supposed to be. I would hate to have to programmatically interrogate the first row and treat it as column headings. So does anyone know what I can do to ensure that all characters in the column headings are imported exactly as defined? (I have tried different code sets). I can't find any reference to special character replacement for data source column headings.
August 5, 2019 at 10:36 pm
Have you considered just skipping the first row?
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
August 5, 2019 at 11:18 pm
So I chuckled when i read that. The problem is that I need the column headings as part of a pivot task. Sorry I wasn't clear.
August 6, 2019 at 5:53 pm
I presume you've considered pushing the data into a staging table first and then pivoting from that?
I would expect it to be faster than the SSIS component, and your column-naming issues would be resolved as part of the change.
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
August 6, 2019 at 6:12 pm
When you define the file connection - you have the opportunity to define the column headers and rename them. The file connection then maintains the column header information as meta data in the SSIS package.
If something is removing a portion of the header field - then it has to be related to how the file is structured and delimited. Is the delimiter a colon or some other character?
Or - are you attempting to import a file where the header column names change and you need to capture the header column names? If that is the case, then it is going to be much harder - since you don't know how many columns or what columns will be included in the file.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply