data conversion in ssis

Question

data conversion in ssis

gugan_ta

SSCommitted

Points: 1649
More actions
July 3, 2009 at 11:19 am

#135868

hi,
am using data conversion in ssis to convert data.
source column contains both character & numeric data... heres i want
non numeric records should be made as either zero/null in destination column and numeric values as it is. Help...
Eg:
Source: Destination:
s_column(varchar) d_column(numeric)
a null/0
12 12
13 13
b null/0

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Input column should be defined as character in the data source. Using a derived column component in the data flow you will build an expression to test for numeric, if yes then pass it on, if not set to null. You will define the output column as some kind of numeric coming out of the derived column. At this point you will actually have both the source and the new derived column in the dataflow, be sure to use the new column..

CEWII

gugan_ta SSCommitted Points: 1649 More actions · Answer 2

Hi,

Thanks..can u give the expression to be used in derived column..

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

It will have to wait until monday.. I won't have time this weekend..

CEWII

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Well, it seems there are not some functions is the derived column component that I thought were there.. Given that I have an alternate solution.. As long as you specify the query for the source and don't choose a table from the list this will work, and since I think this is the best method anyway..

SELECT DerivedColumn = CASE WHEN ISNUMERIC(SourceField) = 1 THEN CONVERT(int, SourceField) ELSE NULL END

Basically add the derived column to your select statement and SQL will do the work before the field is even passed to SSIS at that point you shouldn't need to do a data conversion.. I made a bit of an assumption about the end datatype, in this case I chose int, but it could be any numeric datatype.. This code will get some false positives if the SourceField ONLY has comman, decimal point, and about 3 other characters, but they have to be there all alone.. But with this code you should get the idea..

CEWII