Non significant '0' removed from alphanumeric code

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

Double check all of your metadata links and double check on the advanced editor screens that you don't inadvertently have that column cast as a numeric data type.|||Already done twice! ;-)|||

Sbastien Nunes wrote:

Already done twice! ;-)

Then I'm missing something. A DT_WSTR will not drop leading zeros.|||Are you using SQL in your lookup transformation?|||Yes I am.|||

Sebastion,

Did you get this resolved?

Did you try using data viewers across the Data flow to see at what point the leading zero gets removed...what type of destination are you using...may the probelm be there?

|||

Hi,

No it's not resolved. Yes I tried using Data Viewers, the column where the 0 is removed is an output column from a Lookup Transformation. The Lookup is based on a SQL query.

I have checked the MetaData and everything seems allright.

Thanks for your help.

|||

That is weird...

What type of OLE DB source you are using in the lookup transform?

Did you get same results when running the same query in a native query tool (e.g. SSMS if SQL Server)?

What happens if you try to explicitly cast that column to string type in the lookup query?

|||

Thanks for your answer Rafael, I didn't had the time to test that today, I'll try on monday and I'll let you know what happens.