When using data conversion component in dataflow becareful that the data you trying to convert to is valid, otherwise you may get an error like this:
1. Error 1 Validation error. Data Flow Task: No update required [22340]: input column "gms_valueConverted" (23080) has a datatype which cannot be joined on
The following datatype is not supported by lookup:
o DT_R4
o DT_R8
o DT_TEXT
o DT_NTEXT
o DT_IMAGE
o DT_R8
o DT_TEXT
o DT_NTEXT
o DT_IMAGE
To convert a float datatype in the database to DT_R8 you will have to do the following. You will have to convert the field in the database to numeric and you will have to convert the input in SSIS to numeric.
Use the data Data Conversion Transformer to convert the data to type numeric [DT_NUMERIC].
Then in the lookup component cast the value to numeric in the SQL statement. eg (SELECT customerId, CAST(customerAsset_Value AS numeric(18,3)) AS customerAssset_Value FROM tbl_Customer)
Mapping Integration Services Data Types to Database Data Types
You can find more about the type conversion in SSIS here
Data type mapping
http://msdn2.microsoft.com/en-us/library/ms141036.aspx
Sequel casting
http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx
No comments:
Post a Comment