I had them done on and off for about 12 years. Same issue as you...herniated discs and severe sciatica.
The shot itself is just a teensy bit painful, sorta like a slight bee sting at the lower part of the back. The pain wears off quickly, but the doctor encourages you to take it easy for the remainder of the day. You must have someone with you who will drive you home, too.
The shots are administered in an allotment of three shots. Oftentimes, the first shot is enough to get rid of your pain. Sometimes, though, if the pain continues, they'll have you come back a few weeks later for the second shot. And if the pain is still evident after the second shot, you'll go back in another couple of weeks for the third shot. Most often, I only used the two shots, but someimes I did use all three.
Because of side effects of steroids, they don't like you to get them too frequently, so you'll be limited to how many doses you can get in a year period.
After awhile, my shots ended up not providing any relief, and I had to find another means to get relief. Ultimately I ended up with spinal surgery - absolutely the best thing ever to finally get rid of the excruitating sciatica pain.
Good luck!