DTS Error in load from one table to other

• kumbhardare

  SSC Veteran

  Points: 297

  More actions

  October 21, 2008 at 12:59 pm

  #73065

  Hi,

  I have DST script

  ' Copy each source column to the destination column

  Function Main()

  DTSDestination("Failure_Error_Type_ID") = DTSSource("Temp_Error_Type_ID")

  DTSDestination("Failure_Transaction_ID") = DTSSource("Temp_Transaction_ID")

  DTSDestination("Failure_Purchasing_System_ID") = DTSSource("Temp_Purchasing_System_ID")

  DTSDestination("Failure_Electronic_Issue_ID") = DTSSource("Temp_Electronic_Issue_ID")

  DTSDestination("Failure_Buyer_Assignment_ID") = DTSSource("Temp_Buyer_Assignment_ID")

  DTSDestination("Failure_Duns_ID") = DTSSource("Temp_Duns_ID")

  DTSDestination("Failure_Duns_Company_Assignment_ID") = DTSSource("Temp_Duns_Company_Assignment_ID")

  DTSDestination("Failure_PO_Create_Date") = Replace( convert(varchar(11),DTSSource("po_create_date") ) )

  DTSDestination("Failure_Item_Id") = DTSSource("item_id")

  DTSDestination("Failure_PO_Line") = DTSSource("po_line")

  DTSDestination("Failure_PO_Number") = DTSSource("po_number")

  DTSDestination("Failure_Duns_Code") = DTSSource("duns_code")

  DTSDestination("Failure_Duns_Company_Name") = DTSSource("vendor_name")

  DTSDestination("Failure_Transaction_Date") = Replace( convert(varchar(11),DTSSource("transaction_date") ) )

  DTSDestination("Failure_Date_Received") = Replace(convert(varchar(25),DTSSource("date_received") ) )

  DTSDestination("Failure_Original_Value") = DTSSource("original_value")

  DTSDestination("Failure_Error_Field_Value") = DTSSource("error_field_value")

  Main = DTSTransformStat_OK

  End Function

  Get a error

  Error during Transformation ' ' for Row number 1. Errors encountered so far in this task: 1.

  Error Source: Microsoft Data Transformation Services (DTS) Data Pump

  Error Description:Error Code: 0

  Error Source= Microsoft VBScript runtime error

  Error Description: Type mismatch: 'varchar'

  Error on Line 13

  Can Some one Help?

  Thanks

• Erik Kutzler

  SSCarpal Tunnel

  Points: 4287

  More actions

  October 21, 2008 at 4:22 pm

  #887876

  So, which of those lines is line 13? Find it and make sure the data type in both the source and destination table is the same.

• kumbhardare

  SSC Veteran

  Points: 297

  More actions

  October 22, 2008 at 8:37 am

  #888151

  hi,

  This is line 13

  DTSDestination("Failure_PO_Create_Date") = Replace( convert(varchar(11),DTSSource("po_create_date") ) )

  My main problem is that datatype is diffrent in the 2 tables.

  po_create_date is varchar(11) and Failure_PO_Create_date is smalldattime.

  Thanks

  Raju

• Erik Kutzler

  SSCarpal Tunnel

  Points: 4287

  More actions

  October 22, 2008 at 12:03 pm

  #888264

  You're using SQL syntax in an Active X Script task. That obviously won't work.

  Simply change the line to

  DTSDestination("Failure_PO_Create_Date") = DTSSource("po_create_date").

  If the data in the varchar field is actually a date, the transformation will automatically convert it to smalldatetime for you.

  Alternately, you could use this code, which will insert data into the Failure_PO_Create_Date field only if data in po_create_date is a date:

  If IsDate(DTSSource("po_create_date")) Then

  DTSDestination("Failure_PO_Create_Date") = CDate(DTSSource("po_create_date") )

  End If

• kumbhardare

  SSC Veteran

  Points: 297

  More actions

  October 22, 2008 at 2:02 pm

  #888336

  Hi,

  Thanks the alternate method worked

  Thanks

  Raju