Post reply

Differences between the preview of a Flat File Source and its output

  • September 4, 2008 at 2:04 pm

    #126159

    Well i have a flat file source, reading from a flat file connection...

    This file is a fixed width file, actually a Ragged Right File...

    I configured the file based on the specifications i have.

    I previewed the connection and also the flat file and everything looks great this is the preview:

    But when i import, i have the following output:

    It is copying to a Table with varchars as types:

    This is the table:

    CREATE TABLE [dbo].[E1_P1_M3_Step1](

    [ent_num] [varchar](10) NULL,

    [SDN_Name] [varchar](350) NULL,

    [SDN_Type] [varchar](12) NULL,

    [Program] [varchar](50) NULL,

    [Title] [varchar](200) NULL,

    [Call_Sign] [varchar](8) NULL,

    [Vess_Type] [varchar](19) NULL,

    [Tonnage] [varchar](14) NULL,

    [GRT] [varchar](9) NULL,

    [Vess_flag] [varchar](40) NULL,

    [Vess_owner] [varchar](150) NULL,

    [Remarks] [varchar](1000) NULL

    ) ON [PRIMARY]

    But when i go to see what was copied i see this:

    I'm really lost here... I checked the mappings and they're ok.

    Any suggestions??? How can the preview be different from the output???

    This is the main design:

    This is the Dataflow design:

    At first i had the last column with a specified width, but now i corrected and left the last column with no width (has an output width) and the column delimiter ({CR}{LF}).

    This is the file:

    http://www.treas.gov/offices/enforcement/ofac/sdn/delimit/sdn.ff

    As the specifications says for this:

    http://www.treas.gov/offices/enforcement/ofac/sdn/dat_spec.txt

    Main table, text file name SDN.FF

    Column Posi-

    sequence Column name Type Size tion Description

    -------- ----------- ------- ---- ---- ---------------------

    1 ent_num number 10 10 unique record

    identifier/unique

    listing identifier

    2 SDN_Name text 350 11 name of sdn

    3 SDN_Type text 12 361 type of SDN

    4 Program text 50 373 sanctions program name

    5 Title text 200 423 title of an individual

    6 Call_Sign text 8 623 vessel call sign

    7 Vess_type text 25 631 vessel type

    8 Tonnage text 14 656 vessel tonnage

    9 GRT text 8 670 gross registered

    tonnage

    10 Vess_flag text 40 678 vessel flag

    11 Vess_owner text 150 718 vessel owner

    12 Remarks text 1000 868 remarks on SDN

    END OF ROW 1868

    Record separator: carriage return

    null: -0-

    Any idea?

  • September 4, 2008 at 2:19 pm

    #867421

    create the data flow task using import\export wizard.

    goto start --- > run ---> type dtswizard ---> select source as flat file ---> and go on ---> in the end save the package on file sytem and then see the result ---> if the result are as per your expectation then add this package in your package and see.....

    thanks

  • September 4, 2008 at 2:31 pm

    #867430

    keep in ragged right mode and take the entire row as one column specifying the maximum length of row as the column column. Next add a derived column transformation and use the substring function and derive all the columns.

    I believe this happens when the end of each row is of not the same length

    in ragged right you cannot specify the end length

    Regards
    Venkat
    http://sqlblogging.blogspot.com[/url]

  • September 4, 2008 at 3:16 pm

    #867467

    create the data flow task using import\export wizard.

    goto start --- > run ---> type dtswizard ---> select source as flat file ---> and go on ---> in the end save the package on file sytem and then see the result ---> if the result are as per your expectation then add this package in your package and see.....

    thanks

    I did it, and the package that it created worked perfect!...

    I believe this happens when the end of each row is of not the same length

    in ragged right you cannot specify the end length

    I guess that this is the problem...

    I'm creating the tasks dinamically trought a C# code... and i have realized that's the problem.

    I'm going to check that, will post the results in a minute

  • September 5, 2008 at 1:26 am

    #867594

    I suspect that your row width needs to be adjusted by 3 characters

  • September 5, 2008 at 9:01 am

    #867837

    Yeap, it the columns in the SS had a bad width, but it wasn't the problem.

    The problem was the one that SrikanthSv mentioned:

    in ragged right you cannot specify the end length

    I was specifying the width of the last column!.

    Fixed that and worked like a charm! (i love this expression ^^)

    Thanks to All! =)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply