Error: The value could not be converted because of a potential loss of data.
Hello, I am importing data from 1 table to another table. The source table has a DOB field of type date and format YYYY-MM-DD. I have to import it into the destination table as VARCHAR(8) and the format should be YYYYMMDD. I am using the following SQL to do the import in the OLEDB Source: CASE WHEN DOB IS NULL OR DOB = '' OR LEFT(DOB,4) < 1800 THEN '18000101' ELSE CAST(REPLACE(DOB,'-','') AS VARCHAR(8)) END As dob But I am gettig the following error: The value could not be converted because of a potential loss of data. Can someone please suggest how to fix this error? PS: I had earlier posted this question in the wrong forum so I'm reposting it here. The link to my earlier post is: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/81c3fcb0-a832-421f-bdc8-c6d9c8afb2f1 Thanks, KK
November 30th, 2010 12:17pm

If the sourcefiled ist datatype date or datetime, you better should use convert instead of cast because you can append a conversion style: CASE WHEN DOB IS NULL OR DOB = '' OR LEFT(DOB,4) < 1800 THEN '18000101' ELSE CONVERT(VARCHAR(8),DOB,112) AS ) END As dob
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 1:13pm

Christa, I have tried using CONVERT too as in the following SQL : CASE WHEN DOB IS NULL OR DOB = '' OR LEFT(CONVERT(VARCHAR(8),DOB,112),4) < 1800 THEN '18000101' ELSE CONVERT(VARCHAR(8),DOB,112) END As dob But that didn't work either. I have tried increasing the length of the destination to varchar(15) but still got the same error. Thanks, KK
November 30th, 2010 1:38pm

Hi Kesar, The solution suggested by Christa should work fine. If you're still getting an error, please check if the error is indeed because of the DOB column. Regards, ShalinShalin Kapadia
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 1:51pm

You still have some potential ambiguity, the IS NULL return isn't typed. You need to ensure the source "column" is varchar(8), which in SSIS terms is DT_STR 8. Be sure to wrap the entire CASE in the cast - CAST(CASE WHEN DOB IS NULL OR DOB = '' OR LEFT(CONVERT(VARCHAR(8),DOB,112),4) < 1800 THEN '18000101' ELSE CONVERT(VARCHAR(8),DOB,112) END AS varchar(8)) As dob You may need to remove the column entirely and re-add it, or just re-create the destination. If you change a query, SSIS doesn't always recognise the change in a nice manner. Looking back at your previous thread this seems to be the problem, it is within the source, and nothing to do with the destination - There was an error with output column "dob" (212) on output "OLE DB Source Output" (11). The column status returned was: "The value could not be converted because of a potential loss of data.". This can happen when you change the query and the external column metadata doesn't match the output column metadata. You can see some of this if you use the Advanced Editor. Right-click the source to choose this editor. The simplest fix is usually to delete the problem component and re-add it. http://www.sqlis.com | http://www.konesans.com
November 30th, 2010 1:56pm

Thank you so much Darren. The step apparently only had to be deleted and recreated to fix the problem.
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 2:48pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics