Cannot load script for execution to new SQL Server version

To fix above error, make sure the SSIS project is referenced to right SQL Server version.

To do so:

1. Right click on Integration Project
2. Click Properties
3. Pick your sql version (see image below):

Merge join unpredictable behaviors

Today while on SSIS package, found very strange behavior where I will keep seeing rows coming for update but nothing would update because there was nothing to update.

Also would see rows that exist in destination but Merge join will bring them for insert. Strange!

After hours of research, found if you have "Sort" defined in one source in SSIS and order by defined in other source inside SQL Server then you will see all kind of unexpected behaviors.

Sort operation in SSIS is caSE Sensitive and it will NOT work right if there is data source that already is sorted in sql server.

So in short:
1. Dont mix sources with "Sort" in SSIS and with "order by" defined in sql server.
2. Sort operation inside ssis is caSE sensitive.
3. Keep one of the order by kinds. Either all from sql server or just defined inside SSIS.


HTH

Testing SSIS connectivity hangs...

Hi,

If you are experiencing "Testing Connectivity..." hang issue where the VS is just waiting then it is OK to kill the instance and review the packages using text editor.

In my case I had Connect Timeout = 0 and if server names were not present, the VS will never stop searching for it.

I had to open SSIS package in Textpad, search for Connect Timeout setting and change it 30(seconds) from 0.

OR

Other option would be to Work Offline.
But this option will only be set once the package open fully after Testing connectivity is done.

HTH.