I have just loaded sql 2008 on a dev server and started testing - I have a 
dts routing that loads a flat file into sql. So started with a integration 
service package to just load the data via a bulk insert - very simple then 
once file is loaded - very simple table column 0, column  1, etc all varchar 
- it has 5 numeric fields so created a new file to cast the numeric fields to 
- used the select insert to transfer the data to new table - works fine with 
all fields set to varchar - changed one field in the new table to numeric and 
used the following to insert varchar data into the numeric field cast(column 
23 as numeric (6,0)).  This comes back with a conversion error - I tested 
this method in SQL 2000 this afternoon and it works fine.  Lost no idea what 
I am doing wrong in sql 2008. Does ISP via bulk insert do something to the 
data different than a simple dts import in sql 2008? Any help would be 
appreciated.. thanks.

pat
Without seeing the data it is hard to suggest ... But , insert all the data 
into VARCHAR(n) column and try SELECT CONVERT (numeric....)

BTW, Since SQL Server 2005 and onwards many things have been changed and 
that behavious is a result of that changes

http://blogs.msdn.com/craigfr/archive/2010/01/20/more-on-implicit-conversions.aspx





"pat" <pat@discussions.microsoft.com> wrote in message 
news:CE78666D-2AD8-48DB-8631-23507FE39F5C@microsoft.com...
>I have just loaded sql 2008 on a dev server and started testing - I have a
> dts routing that loads a flat file into sql. So started with a integration
> service package to just load the data via a bulk insert - very simple then
> once file is loaded - very simple table column 0, column  1, etc all 
> varchar
> - it has 5 numeric fields so created a new file to cast the numeric fields 
> to
> - used the select insert to transfer the data to new table - works fine 
> with
> all fields set to varchar - changed one field in the new table to numeric 
> and
> used the following to insert varchar data into the numeric field 
> cast(column
> 23 as numeric (6,0)).  This comes back with a conversion error - I tested
> this method in SQL 2000 this afternoon and it works fine.  Lost no idea 
> what
> I am doing wrong in sql 2008. Does ISP via bulk insert do something to the
> data different than a simple dts import in sql 2008? Any help would be
> appreciated.. thanks. 

"Uri Dimant" wrote:

> pat
> Without seeing the data it is hard to suggest ... But , insert all the data 
> into VARCHAR(n) column and try SELECT CONVERT (numeric....)
> 
> BTW, Since SQL Server 2005 and onwards many things have been changed and 
> that behavious is a result of that changes
> 
> http://blogs.msdn.com/craigfr/archive/2010/01/20/more-on-implicit-conversions.aspx
> 
> 
> 
> 
> 
> "pat" <pat@discussions.microsoft.com> wrote in message 
> news:CE78666D-2AD8-48DB-8631-23507FE39F5C@microsoft.com...
> >I have just loaded sql 2008 on a dev server and started testing - I have a
> > dts routing that loads a flat file into sql. So started with a integration
> > service package to just load the data via a bulk insert - very simple then
> > once file is loaded - very simple table column 0, column  1, etc all 
> > varchar
> > - it has 5 numeric fields so created a new file to cast the numeric fields 
> > to
> > - used the select insert to transfer the data to new table - works fine 
> > with
> > all fields set to varchar - changed one field in the new table to numeric 
> > and
> > used the following to insert varchar data into the numeric field 
> > cast(column
> > 23 as numeric (6,0)).  This comes back with a conversion error - I tested
> > this method in SQL 2000 this afternoon and it works fine.  Lost no idea 
> > what
> > I am doing wrong in sql 2008. Does ISP via bulk insert do something to the
> > data different than a simple dts import in sql 2008? Any help would be
> > appreciated.. thanks. 
> 
> 
> .
> 

"Uri Dimant" wrote:

> pat
> Without seeing the data it is hard to suggest ... But , insert all the data 
> into VARCHAR(n) column and try SELECT CONVERT (numeric....)
> 
> BTW, Since SQL Server 2005 and onwards many things have been changed and 
> that behavious is a result of that changes
> 
> http://blogs.msdn.com/craigfr/archive/2010/01/20/more-on-implicit-conversions.aspx
> 
> 
> 
> 
> 
> "pat" <pat@discussions.microsoft.com> wrote in message 
> news:CE78666D-2AD8-48DB-8631-23507FE39F5C@microsoft.com...
> >I have just loaded sql 2008 on a dev server and started testing - I have a
> > dts routing that loads a flat file into sql. So started with a integration
> > service package to just load the data via a bulk insert - very simple then
> > once file is loaded - very simple table column 0, column  1, etc all 
> > varchar
> > - it has 5 numeric fields so created a new file to cast the numeric fields 
> > to
> > - used the select insert to transfer the data to new table - works fine 
> > with
> > all fields set to varchar - changed one field in the new table to numeric 
> > and
> > used the following to insert varchar data into the numeric field 
> > cast(column
> > 23 as numeric (6,0)).  This comes back with a conversion error - I tested
> > this method in SQL 2000 this afternoon and it works fine.  Lost no idea 
> > what
> > I am doing wrong in sql 2008. Does ISP via bulk insert do something to the
> > data different than a simple dts import in sql 2008? Any help would be
> > appreciated.. thanks. 
> 
> 
> .Well I cut the sample to 27 lines and am working on Column 23 - data in SSIS insert looks like this "1.00" - column is varchar 30 
> my best guess now is that the SSIS is not removing the "" on the text delimited fields like the old DTS and the reason why the standard convert or cast does not work - I just tested the DTS side and it has a qualifier for text "" so I am almost a 100% positive I am correct .
Next question - I am not sure on the easiest way to insert a text file into 
a database via SSIS - i used the bulk insert but don't see other options and 
how to control. Any one have samples of a simple text file load into a 
database?
Thanks.

Pat
As I mentioned  you can create a table with all columns defined as 
VARCHAR(n) and insert the data. I think SSIS is the best choice



"pat" <pat@discussions.microsoft.com> wrote in message 
news:BEBB9106-6386-4F18-ADD8-7FDAE4FE65DA@microsoft.com...
>
>
> "Uri Dimant" wrote:
>
>> pat
>> Without seeing the data it is hard to suggest ... But , insert all the 
>> data
>> into VARCHAR(n) column and try SELECT CONVERT (numeric....)
>>
>> BTW, Since SQL Server 2005 and onwards many things have been changed and
>> that behavious is a result of that changes
>>
>> http://blogs.msdn.com/craigfr/archive/2010/01/20/more-on-implicit-conversions.aspx
>>
>>
>>
>>
>>
>> "pat" <pat@discussions.microsoft.com> wrote in message
>> news:CE78666D-2AD8-48DB-8631-23507FE39F5C@microsoft.com...
>> >I have just loaded sql 2008 on a dev server and started testing - I have 
>> >a
>> > dts routing that loads a flat file into sql. So started with a 
>> > integration
>> > service package to just load the data via a bulk insert - very simple 
>> > then
>> > once file is loaded - very simple table column 0, column  1, etc all
>> > varchar
>> > - it has 5 numeric fields so created a new file to cast the numeric 
>> > fields
>> > to
>> > - used the select insert to transfer the data to new table - works fine
>> > with
>> > all fields set to varchar - changed one field in the new table to 
>> > numeric
>> > and
>> > used the following to insert varchar data into the numeric field
>> > cast(column
>> > 23 as numeric (6,0)).  This comes back with a conversion error - I 
>> > tested
>> > this method in SQL 2000 this afternoon and it works fine.  Lost no idea
>> > what
>> > I am doing wrong in sql 2008. Does ISP via bulk insert do something to 
>> > the
>> > data different than a simple dts import in sql 2008? Any help would be
>> > appreciated.. thanks.
>>
>>
>> .Well I cut the sample to 27 lines and am working on Column 23 - data in 
>> SSIS insert looks like this "1.00" - column is varchar 30
>> my best guess now is that the SSIS is not removing the "" on the text 
>> delimited fields like the old DTS and the reason why the standard convert 
>> or cast does not work - I just tested the DTS side and it has a qualifier 
>> for text "" so I am almost a 100% positive I am correct .
> Next question - I am not sure on the easiest way to insert a text file 
> into
> a database via SSIS - i used the bulk insert but don't see other options 
> and
> how to control. Any one have samples of a simple text file load into a
> database?
> Thanks.