asked
SSIS Derived column expression
In my SSIS 2008 package, I am trying to insert data in sql server table from flat file(Test1.txt) which is '|' delimited. Last column in this file sometime has value and sometime it is blank. In sql server table i used column name 'C_S_A' with data type money. In Flat file connection manager editor, 'Advanced' -> 'CSA ' is column name with data type = currency [DT_CY] In 'Derived column', i am using following formula for 'Expression' which works when i have values in last column of Test1.txt file: ISNULL([CSA ]) ? NULL(DT_CY) : (DT_CY)[CSA ] When i have no value is last column of Test1.txt file then it is not working and gives error: [Flat File Source [339]] Error: Data conversion failed. The data conversion for column "C S A " returned status value 2 and status text "The value could not be converted because of a potential loss of data.". [Flat File Source [339]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "C S A " (1239)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "C S A " (1239)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (339) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. May i know what will be the fix in Expression ?
I tried expression two different ways. Either expression is working fine but still it is not inserting data into table. Derived1 == "" ? 0 : (DT_NUMERIC,18,2)(Derived1) LEN(TRIM(Derived1)) > 0 ? ((DT_NUMERIC,18,2)TRIM(Derived1)) : 0
As the flat file source column is currency, and currency should map to 'money' in SQL Server (see [mapping table][1]), I do not see why you would need to do any conversion whatsoever. What error do you see if you map CSA to C_S_A directly, with no transformations? What value did you use for the 'Retain null values from the source as null values in the data flow' property in the data flow? [1]:
http://bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations
Hi PhilParkin, Thank you so much. I greatly appreciate your comment. Thanks
18 People are following this question.
