Flat file source to destination table is getting only top 1 record into table

  • 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?

     

     

     

  • 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

  • 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

    • This reply was modified 4 years, 4 months ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

  • mcfarlandparkway wrote:

    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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • so i need to select row delimeter as vertical bar {|} ?

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • any one has sample script task to do this?

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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