TransformCopy Conversion Error

• Jonathan Hall

  Valued Member

  Points: 51

  More actions

  August 13, 2004 at 8:12 am

  #86051

  I have created a fixed-length text source and a SQL OLE DB destination in a DTS package.  When I try to run it I have a field 5 digits that is suppose to be an integer but the 5 digits  have leading zero's.  It is a quantity field

  I get a string to integer conversion error due to the leading zero's.

  any ideas on how to fix this?  I will be creating a ActiveX script to dynamicaly pass the file name to the text source part of the DTS.

   

  Thanks,

  Jon

• Peter Tillotson

  Hall of Fame

  Points: 3325

  More actions

  August 16, 2004 at 6:32 am

  #519076

  One straight forward approach is to load your data into a temporary table in SQL server, where the column in question is set up as varchar. Then move the data into your final destination table and do any transformations needed, such as convert(int, TroubleSomeField).

  eg: 

  INSERT INTO final_table (key_field, TroublesomeField)

  SELECT key_Field, convert(int, TroublesomeField) as TroublesomeField

  FROM staging_table st

  where not exists

  (SELECT 'dummy value' from final_table ft where ft.key_field = st.key_field)

  This approach lets you do validation of the data against your existing tables before adding the new records.

  Regards

  Peter

   

• Jonathan Hall

  Valued Member

  Points: 51

  More actions

  August 23, 2004 at 6:44 am

  #520151

  That is exactly what I ended up doing.  I made a temp table as a holding tank for the data, then do all my transforming to the necessary tables.

  Thanks for the response!

  Jon