SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR

5
votes

I got this strange error message I couldn't get rid of no matter what i tried. It is an SSIS package developed using Visual Studio Prof. 2012 and database is SQL Server 2012.

Message reads this:

[OLE DB Source [85]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Source.Outputs[OLE DB Source Output].Columns[ProductionMultp]" failed because error code 0xC0209075 occurred, and the error row disposition on "OLE DB Source.Outputs[OLE DB Source Output].Columns[ProductionMultp]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

This happens at the time of creating source data in OLE DB Source Editor. I tried to convert source data to the right data type needed in the destination within the source sql command, I tried to the conversion using Data Conversion tool in SSIS, I tried to use both with no avail...

Anybody familiar with that sort of message???

By the way, there is no NULLs in the data nor any value is greater than allowed number of digits in given data type...

2
sql-serverssis
Could you give us some example data for the column that's causing you problems, and let us know what data type it is in the source, what data type you're trying to convert it to, and what you're doing to carry out the conversion? We'll probably need specific details to figure it out, as the error messages are pretty generic. – Jo Douglass
Thanks Jo and you are right about those messages being so generic. I don't have much experience in here to make my points crossed and sorry about that. Yes, the data in the source is varhchar(10) and I even CAST it in sql command to make sure that gets crossed as varchar(10) and it is varchar(10) in destination table also... This was making the whole thing really difficult to understand because I thought there is no need for any conversion but since I start getting the message I decided to use Data conversion transformation editor to convert it to DT_STR with length 10 with no avail... – inandoutofSQL
But at the end, I think I know what happened although I have no idea why... After all those attempt to convert the data to get it right, I checked on Advanced Editor for OLE DB Source and that field showed as Numeric there... And my error message has gone after I changed it to DT_STR. Go figure!.. – inandoutofSQL

2 Answers

13
votes

Go to Advanced Editor for OLE DB Source by right clicking on 'OLE DB Source' / 'Show Advanced Editor...'. Go to the tab 'Input and Output Properties' and expand the option 'OLE DB Source Output' then expand 'Output Columns'. Find your field that is mentioned in the error message and click on it. On the right hand side 'Common Properties' show, find Data Type Properties / Data Type and Length there. Change the data type to what needs to be for that field. That worked fine in my case, I hope it helps you too.

0
votes

I change my Data Source to ADO.Net Source and it work fine.