Hi,

I'm trying to load a flat file into SQL 2005 table.
The file has about 250 columns(fields).
When I try to use the Import wizard, I'm getting truncation errors,
indicating that the length is smaller.
If I correct that and proceed, I'm getting the error again but for a
different column.
And this continues....I noticed one thing, even if you change the length of
the columns in the table, you cannot avoid the error. When you look at the
Source file properties window in SSIS Import Wizard, and under 'Advanced'
tab/window, when you select any column, you'll see one 'OutputColumnWidth'
field to the right side. So, coming to the point.....if you edit the length
here, then you're not getting the error. When you update here for each
column, you won't get the error and the import goes smoothly.
But, the big thing is: if I have to update in this way, for say about 250
columns, clicking on each column, you can imagine !!!!!!! Is there any
automated way for this? or Am I doing wrong?
Really, it's surprising to me that why editing in the destination table is
not effective.
This is the same case whether you allow SSIS to create a table for you or
you create a table for yourself and point the Import wizard to it.

On the other hand, is there a way I can update the 'OutputColumnWidth' value
for all the columns?
Can somebody tell what I'm missing? or any hints for my questions?

Thanks for your help.
Siva.

Just went thru the same process and I didn't find a convenient way to solve
the problem.



As far as I can tell the Flat File Source option "Suggest Types" only scans
the 1st 1000 or so of the flat file records to make its prediction. In my
case I had 300,000 + records to import so I ended up having to define all
the columns with the max possible value. Half of the column values were
NULL in the 'early' (< 90,000) records so the suggest type had to punt to a
default guess on those columns.



It would have helped alot if the "Suggest Types" had the ability to scan all
300,000 records. It would have been worth the wait considering the time and
pain involved with defining types for all 200 incoming columns. SSIS
loaded all 300,000+ records in about 1/4 the time it took for me to define
all the incoming columns!



Of course, if you don't care about truncation issues you could simply tell
the Flat File Source to "ignore" truncation errors in the error handling
dialog. Wasn't an option in my case.



We're migrating from an Infomrix database and I think the problem may have
solved itself if I could figure out how to set-up an ODBC or OLE DB
connection from SSIS to Informix. Using an Informix table as a source
should have trumped the unknown column type issue. Unfortunately, haven't
yet "cracked that nut".



I've felt your pain.



Barry

in Oregon


"SivaRam" <SivaRam (AT) discussions (DOT) microsoft.com> wrote

Hi Barry,

Thanks for your detailed reply.
Where do you get this "ignore' truncation errors on 'error handling dialog'?

I appreciate your time..
Thanks,
Siva.

"frostbb" wrote:

In the Flat File Source Editor, select the right hand pane presents the
options "Connection Manager", "Columns" & "Error Output"

Selecting the "Error Output" option should display another window with
"Input or Output", "Column", "Error", "Truncation" & "Description" columns.
You should be able to select the "Truncation" column and use the "Set this
value to selected cells" dialog at the bottom of the form to "Ignore"
Truncation errors.

I haven't tried this option myself.

Another alternative is to set all the columns to varchar and then import
into an intermediate or 'staging' sql table that also has all varchar
columns but this takes more setup time than simply defining the flatfile
columns in the Flat File Source Editor "Columns" option.

Your call.

Hope this helps.

Barry
in Oregon

"SivaRam" <SivaRam (AT) discussions (DOT) microsoft.com> wrote

Instead of ignoring those truncation errors and loosing the data integrity, I
would "redirect" them into another table and that way it would be easier to
go through handful of those rows and determine why truncation is occuring and
you can then fix your table structure.

"frostbb" wrote:

Did you ever figure out how to connect SSIS to Informix?
I've managed to create a connection manager to it and a data source, but
cannot get the OLEDB source to pick up either. When I try to create a
connection to it I get a class factory can not supply requested class warning.

JM

"frostbb" wrote: