Data Import Issues

• hb21l6

  SSCommitted

  Points: 1647

  More actions

  December 4, 2007 at 8:36 am

  #70608

  OS: w2k std

  SQL: SQL 2000 Std

  Hi there,

  I've tried importing a flat CSV file into an existing table using the DTS wizard.

  I get a load of conversion errors when it tries to impot... (see below)

  I tried altering the transformation VB script to see if I could alter the inbound fields using (convert(decimal(10,5), DTSSource("Col003")).. but it doesnt like using convert ( im guessing becuase its looking for VB script. and not SQL )

  Could anyone point me in the direction of a good URL that explains whats needed or give me some examples on how to convert a string type into decimal, guid, numberic types?

  any help would be appreciated.

  cheers

  Dave

  error during Transformation ' directCopyXform' from Row number 1. error encountered so far in this task:1

  TransformCopy 'directcopyxfom' conversion error: gerneral conversion failure on column 17 (source column 'Col017(DBTYPE_STR), destination column msrepl_tran_version'(DBTYPE_GUID)

  also these conversions

  source column 'Col014(DBTYPE_STR), destination column Field14 '(DBTYPE_NUMBERIC)

  source column 'Col013(DBTYPE_STR), destination column field13(DBTYPE_DECIMAL)

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  December 4, 2007 at 9:20 am

  #757025

  What I do in this situation is import the flat file into a staging table with matching data types without doing any conversions, then use an Execute SQL task to load the destination table from the staging table using cast/convert in the query.

  This requires creating a DTS package rather than using the Wizard only. You could start by saving what you have in the Wizard to a package that you can edit in DTS Designer.

  Greg

  Greg

• hb21l6

  SSCommitted

  Points: 1647

  More actions

  December 4, 2007 at 9:58 am

  #757043

  thats a great idea.. i'll give that a try in the morning..

  cheers

  Dave