January 23, 2019 at 9:24 am
Hi there
I have been tasked with importing a file using a generic framework and not specific table/column mappings as you would normally have in SSIS
I have been told that these will be fixed width files but also that the columns in each file
How can I use SSIS to dynamically import this file. I have been told that I cannot use SQL for this.
Attached are the sample files.
January 23, 2019 at 11:00 am
Weegee2017 - Wednesday, January 23, 2019 9:24 AMHi thereI have been tasked with importing a file using a generic framework and not specific table/column mappings as you would normally have in SSIS
I have been told that these will be fixed width files but also that the columns in each file
How can I use SSIS to dynamically import this file. I have been told that I cannot use SQL for this.
Attached are the sample files.
Excel does not really do fixed-width files (and most people here will not open Excel attachments, for security reasons). Moreover, unless you somehow provide a record spec for your fixed-width file, it will be impossible for SSIS to parse out the columns.
For example
ABCD
Is that
A,B,C,D
or
AB,C,D
or
A,BC,D
you get the drift.
If the file is dynamic, I guess the target (a SQL table, presumably) will also be dynamic? Created at runtime, perhaps?
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
January 24, 2019 at 2:21 am
Phil Parkin - Wednesday, January 23, 2019 11:00 AMWeegee2017 - Wednesday, January 23, 2019 9:24 AMHi thereI have been tasked with importing a file using a generic framework and not specific table/column mappings as you would normally have in SSIS
I have been told that these will be fixed width files but also that the columns in each file
How can I use SSIS to dynamically import this file. I have been told that I cannot use SQL for this.
Attached are the sample files.
Excel does not really do fixed-width files (and most people here will not open Excel attachments, for security reasons). Moreover, unless you somehow provide a record spec for your fixed-width file, it will be impossible for SSIS to parse out the columns.
For example
ABCD
Is that
A,B,C,D
or
AB,C,D
or
A,BC,D
you get the drift.If the file is dynamic, I guess the target (a SQL table, presumably) will also be dynamic? Created at runtime, perhaps?
Hi Phil
Yes thats correct, the file is dynamic. So the file could have 10 input columns now but could next time have 25 or 30
Therefore the routine would have to be smart enough to read the columns in from the first row and create the destination table at runtime
January 24, 2019 at 5:28 am
Weegee2017 - Thursday, January 24, 2019 2:21 AMHi PhilYes thats correct, the file is dynamic. So the file could have 10 input columns now but could next time have 25 or 30
Therefore the routine would have to be smart enough to read the columns in from the first row and create the destination table at runtime
OK, but how will it know where the columns start and end? Fixed-width files do not have column delimiters.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply