September 6, 2018 at 3:58 am
In my SSIS package I have 2 data sources, both OLE DB to SQL servers with the same table design same Primary Keys and Collation.
In both Data Sources I set the IsSorted property to True and set the SortPosition to 1 on the Primary Key field in each case. I then used a Left Merge Join to determine which records from the Left side of the join need to be added to the Right side. This has been working well for a number of months. Yesterday I started getting Primary Key violation errors and initially I could not fathom why. It wasn't until I did a Preview of the data in the data source in the Right side that I noticed that the data was not sorted by the Primary Key. I checked the settings to make sure IsSorted was set correctly and that the correct field was specified for the sort. All checked out correctly. I also checked the Left side, this was being sorted correctly.
The only way I could get this to work was to delete both sources and the Merge Join. (I tried to add a Sort Task and remove the IsSorted property and SortPosition but the Merge Join would throw its rattle out of the Pram), create the sources again, as Sort Tasks after each source and then set up the Merge Join again.
Is this a bug? I checked the Primary Keys on both tables. The only other clue is that on the Left side is on the same LAN whereas the Right side is a remote server.
September 6, 2018 at 5:52 am
tim.ffitch 25252 - Thursday, September 6, 2018 3:58 AMIn my SSIS package I have 2 data sources, both OLE DB to SQL servers with the same table design same Primary Keys and Collation.In both Data Sources I set the IsSorted property to True and set the SortPosition to 1 on the Primary Key field in each case. I then used a Left Merge Join to determine which records from the Left side of the join need to be added to the Right side. This has been working well for a number of months. Yesterday I started getting Primary Key violation errors and initially I could not fathom why. It wasn't until I did a Preview of the data in the data source in the Right side that I noticed that the data was not sorted by the Primary Key. I checked the settings to make sure IsSorted was set correctly and that the correct field was specified for the sort. All checked out correctly. I also checked the Left side, this was being sorted correctly.
The only way I could get this to work was to delete both sources and the Merge Join. (I tried to add a Sort Task and remove the IsSorted property and SortPosition but the Merge Join would throw its rattle out of the Pram), create the sources again, as Sort Tasks after each source and then set up the Merge Join again.
Is this a bug? I checked the Primary Keys on both tables. The only other clue is that on the Left side is on the same LAN whereas the Right side is a remote server.
Setting the IsSorted property does not affect whether or not the incoming data gets sorted. Instead, you are telling the component that the sort has already been done.
You should change the data sources & add an ORDER BY to your source-data queries, to ensure that incoming data is sorted in the order you desire.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2018 at 8:15 am
Phil Parkin - Thursday, September 6, 2018 5:52 AMtim.ffitch 25252 - Thursday, September 6, 2018 3:58 AMIn my SSIS package I have 2 data sources, both OLE DB to SQL servers with the same table design same Primary Keys and Collation.In both Data Sources I set the IsSorted property to True and set the SortPosition to 1 on the Primary Key field in each case. I then used a Left Merge Join to determine which records from the Left side of the join need to be added to the Right side. This has been working well for a number of months. Yesterday I started getting Primary Key violation errors and initially I could not fathom why. It wasn't until I did a Preview of the data in the data source in the Right side that I noticed that the data was not sorted by the Primary Key. I checked the settings to make sure IsSorted was set correctly and that the correct field was specified for the sort. All checked out correctly. I also checked the Left side, this was being sorted correctly.
The only way I could get this to work was to delete both sources and the Merge Join. (I tried to add a Sort Task and remove the IsSorted property and SortPosition but the Merge Join would throw its rattle out of the Pram), create the sources again, as Sort Tasks after each source and then set up the Merge Join again.
Is this a bug? I checked the Primary Keys on both tables. The only other clue is that on the Left side is on the same LAN whereas the Right side is a remote server.
Setting the IsSorted property does not affect whether or not the incoming data gets sorted. Instead, you are telling the component that the sort has already been done.
You should change the data sources & add an ORDER BY to your source-data queries, to ensure that incoming data is sorted in the order you desire.
Yes I was aware of the behaviour of the IsSorted property. It seems I wrongfully assumed that data would be read in Primary Key order. Lesson learned.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply