January 16, 2009 at 8:48 am
Alvin Ramard (1/16/2009)
If I'm not mistaken, a friend of mine ran into the same problem.After search the internet, I believe he found out that this is caused by a BUG.
Data types can cause some issue with SSIS as it is very strict and does not like to implicitly convert some types of data, I don't think that this is a bug, every time i see this message (which is a lot) it is always fixable by using explicit conversion.
if it is a bug then i would like to see a link to the KB article if available?
January 16, 2009 at 9:37 am
Can't really send screen dump for legal reasons.
June 15, 2009 at 10:22 am
Running into this myself, and I'd like to see the KB on this 'bug', too!
These errors don't occur importing same tables/datatypes to same tables/datatypes on previous sql server versions. Only shows up in attempts to recreate old import jobs on 2008 server.
Did SQL2000 and sql2005 allow these implicit conversions without error, and so it is logical that they would show up now that they are just not allowed implicitly?
June 15, 2009 at 1:24 pm
This is not something new with 2008. I'm using 2005 and I do have to use a Data Conversion transform between the OLE DB Source and the Excel Destination to handle those types even in a simple copy to Excel.
We may be a bit spoiled by SQL Server doing so much with implicit conversions, so SSIS does tend to blindside us with its insistence on the superiority of Unicode.
June 15, 2009 at 4:00 pm
I'm not using excel at all, just copying data from a 4D database to a sqlserver database, and on previous versions of server, the conversion from a unicode format into a varchar field was handled without any conversion interventions on my part. I'd never even noticed that the incoming data was unicode before this.
June 17, 2009 at 2:26 am
I did a little test in SSIS 2005 (don't have a SQL 2008 around to test that as well), and as I expected it fails if you map a nvarchar source column on a varchar destination column.
The error is: Validation error. Data Flow Task: OLE DB Destination [...]: Column "..." cannot convert between unicode and non-unicode string data types.
In SQL 2000 dts this indeed did not invoke an error, as it allowed implicit conversions (which could cause other problems, that might get unnoticed until someone used the data from the destination for some report or other operation).
So, if your source fields have different datatypes than your destination fields, you should do explicit conversion in SSIS. There are at least 2 ways to do this:
Peter Rijs
BI Consultant, The Netherlands
June 17, 2009 at 8:27 pm
Thanks for the input, Peter. I almost added yesterday, but the whole thing is kind of off topic, that yes, these are DTS2000 imports I'm rebuilding, and the conversion thing is easily beaten. The more difficult problem is dealing with the horrible old 4D odbc(not)driver for a 4D 03 database behind a third party app, and the fact that with the rebuilt SSIS package, I cannot get more than 255 characters of an ntext equivalent field out of it. Get them all fine in the old packages. It's been a real headache, with no joy whatsoever so far.
October 28, 2010 at 9:52 am
Got the same error, then I created a Data Conversion task and it worked. But, now when I export it into MS Excel, I get another error "Error at Data flow task [DTS.Pipeline]: The "runtime connection "OleDbConnection" (515)" in the connection manager collection, Connections, of "component" Ecel Destination" (509) does not have a value for the ID property...
Does anyone know what that is?
I am running MS Excel 2003.
October 29, 2010 at 5:39 am
please refrain from hijacking old posts, start a new one instead.
Oh and BTW: please be more specific, see for instance
Forum Etiquette: How to post data/code on a forum to get the best help
Peter Rijs
BI Consultant, The Netherlands
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply