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

Question

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

ironic

SSC Enthusiast

Points: 129
More actions
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?

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

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

bang.prashant Ten Centuries Points: 1100 More actions · Answer 1

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

SrikanthSv Ten Centuries Points: 1197 More actions · Answer 2

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]

ironic SSC Enthusiast Points: 129 More actions · Answer 3

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

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 4

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

ironic SSC Enthusiast Points: 129 More actions · Answer 5

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! =)