sorting in SSIS (OLEDB Source with ORDER BY in a query) differs from sorting in a direct query in Oracle 9i - by Landry Salles

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


0
0
Sign in
to vote
ID 487614 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/2/2009 10:20:08 AM
Access Restriction Public

Description

Hi,

When I sorting in SSIS using a ORDER BY clause in a OLEDB Source in Oracle 9i, the result differs from sorting in a direct query in Oracle.

I have a OleDB Source with a SQL query in Oracle, with a ORDER BY. The output
IsSorted is set to true, and the column are sorted in the correct order. the order is:

A111
A222
-111
111
-222
222


When I execute the query directly in Oracle the order is:

-111
-222
111
222
A111
A222

Therefore, the merge join doesn't if I'm joing Oracle and SQL Server.  To workaround this problem I put a sort transformation before both dataflows.  This workaround was a problem when the datasource had a lot of rows.

Thanks,
Landry.
Sign in to post a comment.
Posted by Microsoft on 9/22/2009 at 10:30 AM
The desired sorting behavior can be achieved by specifying the "Sort punctuation as symbols" flag in the Comparison Flags column in the Sort Transform editor UI. This combined with LocaleID of "English (United States)", which can be set in the property panel when the transform is selected, provided the sort order you expected and saw from Oracle. More information can be found at http://msdn.microsoft.com/en-us/library/ms141038.aspx. Thanks

- Matt Carroll (SSIS Dev Lead)
Posted by Microsoft on 9/4/2009 at 3:07 PM
Thank you for your submission. We will be reviewing your information and providing you feedback on our findings and progress. Thanks for your interest and support of SSIS.
Posted by Landry Salles on 9/2/2009 at 10:25 AM
Hi again,

I had made another test... I execute the same query using Linked Server (MSDAORA) an the result was the same of direct query in Oracle:
-111
-222
111
222
A111
A222

Thanks,
Landry