November 19, 2019 at 3:49 pm
I have a package where i have flat file source with 4 columns and the source file will be in this format
123456|20130701|AWD|WS1035575|20170201|VCDF|MA1064844|20110001|NHS|AS19474754|20160401|EFV7|LK
First column holds -123456
Second column holds -20130701
Third column holds -AWD
Fourth column holds -WS
it repeats …...
before for each loop i have a script task where the files with *.txt will sort if we have multiple files they will sort based on date in a particular path . it will pick all txt files.
I have a for each loop container where i used Enumerator as DataSet and in configuration i selected variable as DataSet and set Enumeration Mode to Rows in the first table.
when i ran the job i only getting first record into table. not all the records.
when i change the source file to separate lines then my job works well and i see all 4 records into table.
123456|20130701|AWD|WS
1035575|20170201|VCDF|MA
1064844|20110001|NHS|AS
19474754|20160401|EFV7|LK
DO we need to set any thing to bring all records into the table if we use first approach?
November 19, 2019 at 3:54 pm
123456|20130701|AWD|WS1035575|20170201|VCDF|MA1064844|20110001|NHS|AS19474754|20160401|EFV7|LK
Is more than 4 columns. What is your row delimiter here, the 4th | character? If so, can you change that? Using a row delimiter that's the same as the column delimiter is a really bad idea.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 19, 2019 at 4:49 pm
That's more than four columns. What is your row delimiter?
--Edit: I seem to have regurgitated Thom's first few words almost verbatim, apologies! But these are critical questions
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
November 19, 2019 at 5:38 pm
My Row Delimeter is {CR}{LF}
Format - Delimited
Text Qualifier as None
Header RowDelimeter - {CR}{LF}
Header rows to skip 0
columns names in the first data row box is unchecked.
November 19, 2019 at 6:05 pm
My Row Delimeter is {CR}{LF}
Format - Delimited
Text Qualifier as None
Header RowDelimeter - {CR}{LF}
Header rows to skip 0
columns names in the first data row box is unchecked.
So now you see the problem?
When you add a row delimiter to the source file, it is picked up properly. If your source file contains no row delimiter, how do you expect SSIS to determine where one row ends and the next begins?
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
November 19, 2019 at 6:14 pm
so i need to select row delimeter as vertical bar {|} ?
November 19, 2019 at 6:19 pm
As Thom suggested, having | as both row and column delimiter is going to confuse the hell out of SSIS and I'd not expect you to have any success doing that.
The format of the source file needs to change. Or you'll have to write some code to unscramble that mess.
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
November 19, 2019 at 6:57 pm
any one has sample script task to do this?
November 19, 2019 at 7:13 pm
A Script Component source would be the way to go, in my opinion, not a Script Task.
It needs to read the file (just one row, based on your example) and then split it on |.
Then it needs to start outputting rows in groups of 4 columns. As this is a weird file format, I doubt that anyone has one ready to go, but it should not take long to roll your own.
Are you sure that you can't get that source file format changed? It would make life so much 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
November 20, 2019 at 9:11 am
Agreed, a Script Component is what you want, not a Script Task; the 2 are distinctly different (1 for use in the Control Flow the other the Data Flow). As you want to import the data, it's a Script Component you want, which will act as a Data Source.
I can't imagine either, that it'll be too hard (probably easier in VB than C#, due to the string handling capabilities), and it's going to effectively just be a loop on the string, that resets every 4th iteration.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply