Home Dashboard Directory Help
Search

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: 

Closed
 as Not Reproducible Help for as Not Reproducible


0
0
Sign in
to vote
Type: Bug
ID: 487614
Opened: 9/2/2009 10:20:08 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

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.
Details
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
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
SortProblem5 - SQL Server direct query.JPG (restricted) 9/2/2009 -
SortProblem4 - Oracle direct query.JPG (restricted) 9/2/2009 -
SortProblem7 - SQL Server Linkedserver to Oracle.JPG (restricted) 9/2/2009 -
SortProblem6 - SQL Server direct query NVARCHAR.JPG (restricted) 9/2/2009 -
SortProblem1 - SSIS Package.JPG (restricted) 9/2/2009 -
script.sql (restricted) 9/2/2009 -
SortProblem3 - Oracle connection properties.JPG (restricted) 9/2/2009 -
SortProblem2 - OleDB Source Oracle.JPG (restricted) 9/2/2009 -