SSIS package errors: The value could not be converted due to potential loss of data....
I've created an SSIS package where one OLE DB (SQL 2000) file is being converted and moved to a OLE DB (SQL 2005) file.. these are on two different (but trusted) domains. This is just a test package to see if the transfer will work...and I'm having problems. The package seems to be okay, but when executed I get multiple OLE DB errors: Invalid Character value for cast specification... The value could not be converted due to potential loss of data... Error: The input OLE DB destination Input (908) failed because error code 0xc0209029. The identified component returned an error from the ProcessInput method. The error specific to the component, but the error is fatal and will cause the Data Flow task to stop running. ============== I've setup the package with the OLE DB, then a data conversion, then the export column, and then the OLE DB .. the conversion is just two columns for now.. both sides are varchar(50), and they were converted to string(DTR_string) formats.. Can this be an issue of going from SQL 2000 to SQL 2005? I'm fairly new to this process, so, bear with me if I don't give you the info you need,, let me know if you need more. Thanks. Jill
February 15th, 2010 10:49am

Okay. I ended up setting the destination to a SQL Server destination instead of an OLE DB.. that got rid of the above error messages, but this brought up another issue.. it wants to keep writing to a column I don't have mapped. This column is the Primary key, and doesn't allow nulls, when I remove this column, the package will work..when the column is listed it won't write to the table throwing an error - cannot insert null value... if this column isn't mapped, why is it trying to insert a null to that column? Or is it that when the table is trying to build it won't build because nothing is mapped and it won't build due to the nulls? Hope that makes sense... any insight to this would be appreciated. thanks. jill
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2010 1:34pm

If you aren't providing a value for the primary key, then what is SSIS supposed to insert into that column? Is it an identity column? If it is not an identity column, you have to provide a value for it. If it is an identity column, you should be able to leave the column unmapped.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com
February 15th, 2010 2:43pm

Hi Jilltre800,You can transfer data from SQL 2000 to SQL 2005 and as your second reply you have passed this issue,Please note that you can use the error output to determine which rows having problems, regarding the primary key did u configure it as "is identity" column since you transfer records and you didn't match this column which needs not null value, please provide more details about the target table structure,Regards,
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2010 2:49pm

Guys, I had gone through the similar error yesterday, and the problem was, the file which I was trying to process had got the wrong pattern.I found this out when I added Dataview and looked clearly into it when it appeared during execution. So, there was one extra column value that caused to this. Since I have found solution for the same error, I would suggest you to add Dataview and obsrve where it breaks. Hope that helps.
June 8th, 2012 3:21pm

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

Other recent topics Other recent topics