"Isak Dinesen" <no5pam@hotmail.com> wrote in message
news:19d90d5f.0404271426.69ba211b@posting.google.com...
> I am getting the following error when attempting to CopyColumn between
> a fixed-width flat file and a nullable decimal column:
>
> "Error Source : Microsoft Data Transformation Services (DTS) Data Pump
>
> Error Description : The number of failing rows exceeds the maximum
> specified.
> TransformCopy 'DTSTransformation__13' conversion error: General
> conversion failure on column pair 1 (source column 'Q_LVS_MLE'
> (DBTYPE_STR), destination column 'Q_LVS_MLE' (DBTYPE_NUMERIC))."
>
> Generally speaking, is it possible to load nullable data from a
> fixed-width flat file in which a "null" column value is populated with
> spaces?
>
> I've tried using all of the TransformFlag settings that might apply
> (DTSTransformFlag_AllowDemotion,
> DTSTransformFlag_AllowLosslessConversion,
> DTSTransformFlag_AllowNullChange,
> DTSTransformFlag_AllowNumericTruncation,
> DTSTransformFlag_Default), but to no avail.

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:#QtGlvNLEHA.3852@TK2MSFTNGP10.phx.gbl...
> Spaces are not nulls they are empty strings
>
> i.e.
>
> declare @i numeric(10,2)
> declare @s varchar(2)
>
> set @s = ' '
>
> --set @i = @s
>
> /*
> Server: Msg 8114, Level 16, State 5, Line 6
> Error converting data type varchar to numeric.
> */
>
> --set @i = CAST(@s as numeric(10,2))
>
> /*
> Server: Msg 8114, Level 16, State 5, Line 13
> Error converting data type varchar to numeric.
> */
>
> --
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "Isak Dinesen" <no5pam@hotmail.com> wrote in message
> news:19d90d5f.0404271426.69ba211b@posting.google.com...
> > I am getting the following error when attempting to CopyColumn between
> > a fixed-width flat file and a nullable decimal column:
> >
> > "Error Source : Microsoft Data Transformation Services (DTS) Data Pump
> >
> > Error Description : The number of failing rows exceeds the maximum
> > specified.
> > TransformCopy 'DTSTransformation__13' conversion error: General
> > conversion failure on column pair 1 (source column 'Q_LVS_MLE'
> > (DBTYPE_STR), destination column 'Q_LVS_MLE' (DBTYPE_NUMERIC))."
> >
> > Generally speaking, is it possible to load nullable data from a
> > fixed-width flat file in which a "null" column value is populated with
> > spaces?
> >
> > I've tried using all of the TransformFlag settings that might apply
> > (DTSTransformFlag_AllowDemotion,
> > DTSTransformFlag_AllowLosslessConversion,
> > DTSTransformFlag_AllowNullChange,
> > DTSTransformFlag_AllowNumericTruncation,
> > DTSTransformFlag_Default), but to no avail.
>
>

"Isak Dinesen" <no5pam@hotmail.com> wrote in message
news:muLjc.13478$gH6.1681@newsread3.news.atl.earthlink.net...
> So it is simply not possible to load a table containing nullable columns
> from a fixed-width text file without setting up a custom ActiveX transform
> on every nullable column? I was hoping it would be possible to configure
the
> task with a specific null escape sequence (\0, for example), that would be
> interpreted as null by the CopyColumn transform.
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:#QtGlvNLEHA.3852@TK2MSFTNGP10.phx.gbl...
> > Spaces are not nulls they are empty strings
> >
> > i.e.
> >
> > declare @i numeric(10,2)
> > declare @s varchar(2)
> >
> > set @s = ' '
> >
> > --set @i = @s
> >
> > /*
> > Server: Msg 8114, Level 16, State 5, Line 6
> > Error converting data type varchar to numeric.
> > */
> >
> > --set @i = CAST(@s as numeric(10,2))
> >
> > /*
> > Server: Msg 8114, Level 16, State 5, Line 13
> > Error converting data type varchar to numeric.
> > */
> >
> > --
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "Isak Dinesen" <no5pam@hotmail.com> wrote in message
> > news:19d90d5f.0404271426.69ba211b@posting.google.com...
> > > I am getting the following error when attempting to CopyColumn between
> > > a fixed-width flat file and a nullable decimal column:
> > >
> > > "Error Source : Microsoft Data Transformation Services (DTS) Data Pump
> > >
> > > Error Description : The number of failing rows exceeds the maximum
> > > specified.
> > > TransformCopy 'DTSTransformation__13' conversion error: General
> > > conversion failure on column pair 1 (source column 'Q_LVS_MLE'
> > > (DBTYPE_STR), destination column 'Q_LVS_MLE' (DBTYPE_NUMERIC))."
> > >
> > > Generally speaking, is it possible to load nullable data from a
> > > fixed-width flat file in which a "null" column value is populated with
> > > spaces?
> > >
> > > I've tried using all of the TransformFlag settings that might apply
> > > (DTSTransformFlag_AllowDemotion,
> > > DTSTransformFlag_AllowLosslessConversion,
> > > DTSTransformFlag_AllowNullChange,
> > > DTSTransformFlag_AllowNumericTruncation,
> > > DTSTransformFlag_Default), but to no avail.
> >
> >
>
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OOe7BQbLEHA.1644@TK2MSFTNGP09.phx.gbl...
> According to the text file driver docs
>
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjettext_file_format.asp
>
> NULL in fixed width is represented by blanks.
>
> I just tested this and that does not seem to hold true. So in the absence
> of knowing how to do this any other way I would do this
>
> We know that the amount of blanks has to be equal to the length of the
field
> in the text file right?
>
> Insert the whole text file into a staging table and use ISNULL() from
there
> to decide whether the value should be null.
>
> --
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "Isak Dinesen" <no5pam@hotmail.com> wrote in message
> news:muLjc.13478$gH6.1681@newsread3.news.atl.earthlink.net...
> > So it is simply not possible to load a table containing nullable columns
> > from a fixed-width text file without setting up a custom ActiveX
transform
> > on every nullable column? I was hoping it would be possible to configure
> the
> > task with a specific null escape sequence (\0, for example), that would
be
> > interpreted as null by the CopyColumn transform.
> >
> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > news:#QtGlvNLEHA.3852@TK2MSFTNGP10.phx.gbl...
> > > Spaces are not nulls they are empty strings
> > >
> > > i.e.
> > >
> > > declare @i numeric(10,2)
> > > declare @s varchar(2)
> > >
> > > set @s = ' '
> > >
> > > --set @i = @s
> > >
> > > /*
> > > Server: Msg 8114, Level 16, State 5, Line 6
> > > Error converting data type varchar to numeric.
> > > */
> > >
> > > --set @i = CAST(@s as numeric(10,2))
> > >
> > > /*
> > > Server: Msg 8114, Level 16, State 5, Line 13
> > > Error converting data type varchar to numeric.
> > > */
> > >
> > > --
> > > --
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > > "Isak Dinesen" <no5pam@hotmail.com> wrote in message
> > > news:19d90d5f.0404271426.69ba211b@posting.google.com...
> > > > I am getting the following error when attempting to CopyColumn
between
> > > > a fixed-width flat file and a nullable decimal column:
> > > >
> > > > "Error Source : Microsoft Data Transformation Services (DTS) Data
Pump
> > > >
> > > > Error Description : The number of failing rows exceeds the maximum
> > > > specified.
> > > > TransformCopy 'DTSTransformation__13' conversion error: General
> > > > conversion failure on column pair 1 (source column 'Q_LVS_MLE'
> > > > (DBTYPE_STR), destination column 'Q_LVS_MLE' (DBTYPE_NUMERIC))."
> > > >
> > > > Generally speaking, is it possible to load nullable data from a
> > > > fixed-width flat file in which a "null" column value is populated
with
> > > > spaces?
> > > >
> > > > I've tried using all of the TransformFlag settings that might apply
> > > > (DTSTransformFlag_AllowDemotion,
> > > > DTSTransformFlag_AllowLosslessConversion,
> > > > DTSTransformFlag_AllowNullChange,
> > > > DTSTransformFlag_AllowNumericTruncation,
> > > > DTSTransformFlag_Default), but to no avail.
> > >
> > >
> >
> >
>
>