data conversion

Hi!
I would be grateful for some advice, when getting error. I have 4 Lookups and one Data Conversion, getting the follwing error. Product.articlenr is a 13 number+letter productnumber.

[Lookup Demo [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[Data Conversion [9467]] Error: Data conversion failed while converting column "articlenr" (8559) to column "Copy of Lookup Product.articlenr" (10059). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion [9467]] Error: The "output column "Copy of Lookup Product.articlenr" (10059)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of Lookup Product.articlenr" (10059)" specifies failure on error. An error occurred on the specified object of the specified component.

curiousss wrote:

[Lookup Demo [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

Right. So adjust your keys in the lookup transformation such that you eliminate duplicates. If you are using SQL, then use a "SELECT DISTINCT" statement. The error message should be pretty clear in what the problem is.

curiousss wrote:

[Data Conversion [9467]] Error: Data conversion failed while converting column "articlenr" (8559) to column "Copy of Lookup Product.articlenr" (10059). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

You must be converting to something that is causing a truncation to occur on real data. In order for the conversion to occur, you would experience data loss. Double check your data types and lengths, if applicable.

|||Thank you!
I have following data types and lengths in conversion
Input column DT_STR lenght 30
Output column (DT_14)

so could this cause a problem?

|||

curiousss wrote:

Thank you!
I have following data types and lengths in conversion
Input column DT_STR lenght 30
Output column (DT_14)

so could this cause a problem?

Yes. 30 bytes overflows a DT_I4.

|||I am confused.. How can I change the Destination Input column DT_14 to length 30?

Conversion Input column DT_STR lenght 30, so probably Output column need to be (DT_14). But how can I change it to DT_14 to length 30?|||

curiousss wrote:

I am confused.. How can I change the Destination Input column DT_14 to length 30?

Conversion Input column DT_STR lenght 30, so probably Output column need to be (DT_14). But how can I change it to DT_14 to length 30?

30 bytes is too large for a DT_I4. See this page: http://msdn2.microsoft.com/en-us/library/ms187745.aspx

You would need to use a DT_DECIMAL(30,0) to be able to hold all of your 30 byte's worth of data. Or, you'll need to substring an appropriate amount of data from the originating character string.

|||Thank you.. how do I do this substring?

|||

curiousss wrote:

Thank you.. how do I do this substring?

substring([column],startlocation,length)|||

curiousss wrote:

Thank you.. how do I do this substring?

You also need to read the BOL when you have questions regarding functions and other things in SSIS. The particular section on functions can be found here: http://msdn2.microsoft.com/en-us/library/ms141671.aspx