March 14, 2012 at 3:45 pm
Maybe someone can help me here.
I pull data from UniVerse database on a scheduled basis and it works fine until someone screws up an input on one of the fields. The expected value on the field (Lets call it Stop) should be numeric. But some moron decides to input either alphabets or a simple blank space (good cases of fat fingering).
Now, I cant change that database or I dont have authority to tell anyone to stop fat fingering.
I thought of changing the data source properties on SSIS ADO.NET source, but ADO.NET does not allow changing of input data types (except if it were NTEXT or Unicode strings). UniVerse column Stop somehow comes across as Integer value and SSIS interprets it as such.
I tested out the OLEDB sources as well and was able to change the datatype for the Output Column to unicode string. But my source still fails with the following error.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (949)
returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
There may be error messages posted before this with more information about the failure.
I tried setting the DelayExternalValidation property, but even that does not help. Can anyone guide me in the right direction?
March 15, 2012 at 3:45 am
This was removed by the editor as SPAM
March 15, 2012 at 8:39 am
This doesnt help either. The ODBC driver for UniVerse does not allow specifying any other SQL functions except SELECT. So in effect, all I can write is SELECT ColumnA, ColumnB, ColumnC from TableXYZ.
When I tried your suggestion with the OLEDB driver, it didnt work either. I guess UniVerse doesnt like anyone telling it what to do or doesnt have a way of interpreting basic SQL statements. If thats the case, that really sucks.
March 15, 2012 at 8:42 am
could you not use a data conversion task in the middle of the source and destination to convert in SSIS rather than in the actual query at the source
March 15, 2012 at 8:48 am
I would agree to the fact that we can always use the derived column to change the data. But the problem is, the package fails at the ADO.NET/OLEDB source itself rather than failing on any transformation.
I cant seem to override the datatype of the source column(within SSIS environment. Let alone UniVerse because it accepts any garbage thrown at it and data type really doesn't matter to it).
This has got me thinking if I can use a script task to create a custom source and then modify the data types in there (???!??) Is that even possible?
March 15, 2012 at 8:49 am
Do you need the value from this field if it's non-numeric? If you go to the error output of your source, you should be able to set it to ignore failure, so you'll just end up with a NULL value for this row if it can't be converted into the expected data type.
March 15, 2012 at 8:58 am
Howard,
I already tried doing that. But this ODBC driver doesn't seem to handle that too.
What I am thinking is that when ODBC establishes the connection, it expects to see numeric value, but when it sees a string, it bombs out. There's no way to control this behavior and thats the sad part. I tried redirecting the error, ignoring it and what not. I even tried manipulating the properties in the advanced editor, but that doesn't help either. This is frustrating.
March 16, 2012 at 2:43 am
This was removed by the editor as SPAM
March 21, 2012 at 7:46 am
Can I use OPENROWSET (and ROWSET functions in general) to over come this issue?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply