March 23, 2008 at 8:11 pm
i am importing a flat file into a db where i have the file in fixed width when i set that i need to make the columns separately as it shows as just one column but i couldnt do that hpw shpuld i do that plz let me know atleast give me an idea if poss...thkz in advance...
March 23, 2008 at 8:14 pm
This is definitely something that can work, please however explain what the issue is using better English and not just shorthand. It makes it hard to determine the issue.
What is the delimiter in the flat file, meaning how can you tell one column from another?
March 23, 2008 at 10:02 pm
I think you need to set the colulm delimiter correctly.Edit the file connection manager. In the columns tab in the file connection manager ---> edit, you beed to first "reset" columns.
note : by default it will show one column only.
After resting the columns you will be able to see the data from the flat file in the "preview".
After this the "column delimter" drop down will be active, you need to choose the appropriate delimeiter for yourself and hit the "refresh" button. This should give you the data separated in columns as you might want.
March 24, 2008 at 6:29 am
hi pritesh ans steve ...pritesh i tries the way you said me but it did not work for me in that way and i came to know that there is no delimited it is fixed width so in fixed width it shows me one column i cant set it into the number of columns i have when i tried to click on the column equally it includes other column values too so i couldnt make it i m not sure ..So please its a bit urgent let me know
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 24, 2008 at 6:37 am
can you attach some sample data in a file here so that i can understand your problem
March 24, 2008 at 6:55 am
i am assuming that you have a fixed file with no delimiters, and you want to separate the data in different columns, you can do this by following these steps:
1. Create a flat file connection ,with the format as "fixed width".
2. In the columns tab you need to manually set the columns , you will see a graph in the "source data columns:" box. you need to slide the red colour slider to mark the end of a row. you need to create number of black coloured sliders , these will mark the end of a column. You can create multiple black sliders on double clicking on the scale.
3. press OK , and use this connection manager in the flat file source , you wil be able to see the data separated in different columns.
Please refer to the screenshots that i have attached.
March 24, 2008 at 6:55 am
the sample data is something this way ...
A0233366240 2522966011G14454530000000 76668 LORABCDEM 1.0MG 5 -00000001-0000000100000000 000001 000001 00000001 0003210BRMBFI 00070 YHU06701 000 0000 01CHAIN 80U010000 TB11 01 000100 06199600781
A0011166230 2522967027D23200000037201 FDV0435 MASDEWRTYL TTHNMJYE 100MG -00000001-0000000100000000 000057 000057 00000001 0003210BRMBFI 00070 UGH06704 000 0000 01000000348380U010000 TB31 01 000400 03199600781
A0013466270 2522967028F4326023456701 FDV0520 MFDGHYTRGL TTGBNHYE 100MG -00000001-0000000100000000 001445 001445 00000001 0003210BRMBFI 00070 LKJ06700 000 0000 01000000348380U010000 TB31 01 000000 03199600781
A0011145270 2522967029X11234567137201 CFD0584 DFRGTROLOL TASDFGHE 100MG -00000001-0000000100000000 000794 000794 00000001 0003210BRMBFI 00070 EDR00055 000 0000 01000000348380U010000 TB31 01 005500 03199600781
A0011166270 2522967030C19960328137201 CGF0598 UJKLIULOL TAAAAAAE 100MG -00000001-0000000100000000 001445 001445 00000001 0004560DFGHFI 00070 EDT67800 000 0000 01000000348380U010000 TB31 01 000000 03199600781
THIS IS MY SAMPL DATA I THINK IT IS FIXED WIDTH BUT I AM NOT SURE I JUST DONT KNOW EXACTLY SO HOW COULD I DO THAT EVEN IF IT IS FIXED WIDTH IT SHOWS ME THIS WAY I HAVE ATTACHED A SCREEN SHOT I COULDNT SPLIT THE COLUMNS EXACLT PLZ LET ME KNOW HOW TO DO THAT THKZ IN ADVANCE
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 24, 2008 at 7:05 am
i think i got your problem. Your file format is "ragged right" . Use this format instead of "Fixed width" , you will have a better view of data in the COLUMNS Tab. Use the steps with the black sliders to mark the columns.
refer the screen shots here , with your data.
March 24, 2008 at 7:08 am
yes pritesh exactly thats what i need and i am trying too but the problem here is i couldnt set the columns correctly as in the pic when i just move the red line till the end of the row and trying to set columns i get an issue here...
like i couldnt set it as the first column in first row is diff from second row so when i tried to click as 1st column it includes second column values in 1st column in second row so i couldnt do that exactly it is not equally done one column has more characters but the above row has less so when i tried to do that it includes the next row second column in the first column values so i coludnt do this plz let meknow if i need to make any changes
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 24, 2008 at 7:13 am
thkz pritesh got it ...really it was helpful i have been tryying this from friday as my senior said it is fixed width so i havent tried it as ragged right so that made me think of fixed width and not even trying of other formats anyway thkz a lot pritesh and steve too....so pritesh if u dnt mind can i have ur mail id which u check regularly i shall not bother you but i just need it in worst case...anyway thkz ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 24, 2008 at 7:17 am
Okay Great ! , you can post your queries here as it will be benefitial for everyone who uses this community , you can mail me on priteshpatel2205@gmail.com as well.
March 24, 2008 at 7:24 am
Thkz Dude....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 24, 2008 at 7:46 am
dude i got a problem here again in my first row it has values as
asbabs 1221 dd323 00 3232 fdfd
dsadsd 3233 dfd32 00 3321 dsd n
dcfcdc 3232 fdv34 00 3233 eww n
dsvdvc 323 dffdff 00 2323 fdff
dfdfdfd 323 fdsfd 00 1233 dfd n
so i f my fiels are this way then the first takes it as error as it saya truncation occurs as i dnt have anything in my last column ...so i replaced it with n it worked out but i got other error as i dnt have n in my 4th row as it i smy last columnn is there anyway i could do that do i need to replace that with null or should i replace with same character or can i replace it with null
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 24, 2008 at 9:37 am
Double Click on the connection in connection mng and try the following:
Try with "Retain NULL values from the source as NULL in the data flow" in the connection mng.
Also go into "Advanced Tab" of the connection mng and try to set the OutputColumnWidth, if you know the exact length of the char that are coming in, key in those values.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
March 24, 2008 at 9:48 am
thkz look up i tried it the same way it worked for certain rows then i got this error
SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x800470C8 at Data Flow Task, Flat File Source [1]: The external metadata column collection is out of synchronization with the data source columns. The column "Column 23" needs to be updated in the external metadata column collection.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x800470C8 at Data Flow Task, Flat File Source [1]: The external metadata column collection is out of synchronization with the data source columns. The column "Column 23" needs to be updated in the external metadata column collection.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "\\Syndevsql\rawdata\Sandoz\Sandoz NYCC 4000000\ar110sel" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Information: 0x4004800C at Data Flow Task, DTS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 5 buffers were considered and 5 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Information: 0x4004800C at Data Flow Task, DTS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Warning: 0x8020200F at Data Flow Task, Flat File Source [1]: There is a partial row at the end of the file.
Information: 0x402090DE at Data Flow Task, Flat File Source [1]: The total number of data rows processed for file "\\Syndevsql\rawdata\Sandoz\Sandoz NYCC 4000000\ar110sel" is 135896.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "\\Syndevsql\rawdata\Sandoz\Sandoz NYCC 4000000\ar110sel" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (272)" wrote 135896 rows.
SSIS package "Package1.dtsx" finished: Success.
The program '[4344] Package1.dtsx: DTS' has exited with code 0 (0x0).
i have got more rows to be inserted but it says this error msg i dnt have any idea regd buffer so plz let me know where should i change this properties if i need to...thkz in advance
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply