I'm simple going nuts, trying to resolve this problem. So I have text file (flatfile) with some potential numeric data (Something like 0000059856) . Now this is part of a large string in the text file.
My goal is to retrive this data and convert into the following 0000059856 / 10000.0 = 5.9856. And then push the values to SQL Server table (So in SSIS data folow I have flatfile source --> Derived Column -->SQL Destination) .
The SQL Table column is defined as numeric(20,4). In SSIS I used "Derived Column flow task" to extract the column and then convert and apply the computation ( / 10000.0). So something like ((DT_NUMERIC, 16,4) (Column0))
/ 10000.0. But ultimately what I get in the SQL Server table is 0.50000. I've tried several options and couldn't really tell. I even used DT_DECIMAL,4. But that doesn't seem to work. What I need in the table
is 5.9856. I saw some similar issues on google about DT_NUMERIC in SSIS truncating stuff, but no clear solution. Any help would be great.
View Complete Post