Your original Mac Pro's SATA ports are only SATA II (3.0 Gb/s) but you have four bays so you can support RAID by leveraging all of the bays.
As for using a PCI slot to host a M.2 PCIe SSD unit. While it can be done, it won't necessarily get you better performance than using your drive bays depending on the SSD you use as well as the slot you use within the system. Lastly, your given system may not be able to even leverage the throughput here. Your CPU, memory as well as its graphics card could be a bigger bottleneck of the system.
Frankly, I would put in SSD's into the HD bays as being the better direction. Being cheaper and offer more bang if you RAID them.
The problem here is the SSD you bought is a mSATA version of M.2 SSD which only works within a mSATA interface. It is not compatible with PCIe NVMe hardware.
Sometimes a slower interface is faster! A dual drive RAID 0 drive set running SATA II (3.0 Gb/s) is faster than a single drive running SATA III (6.0 Gb/s)! Almost twice as fast!
Here you have the empty bays and have a dual channel SATA II (3.0 Gb/s) controller in the system already. All you will need is two 1 TB drives (as you wanted 2 TB of storage) and the frames to hold the drives.
There is different levels of RAID, yes some are to setup to protect the integrity of your data. While thats still important, SSD's don't have the same reliability issues as HDD's which is why using RAID 0 is quite safe.
As to what is auto sense: Sadly, many drive makers are scaling back their drive offerings. Fixed SATA I (1.5 Gb/s) & SATA II (3.0 Gb/s) drives are gone, for a long time we had auto sense drives which offered compatibility across two or more of the SATA speeds. Today, many drive makers are in the process of reducing their offerings again. Many HDD makers now only offer dual SATA II / SATA III auto sense drives. In the case of SSD's many are dropping dual and focusing on SATA III only! So you do need to be careful what you buy for older systems.