I get the purpose of including Picard in the DS9 premiere as a way to pass the torch, but what exactly would you say was gained by the angst Sisko had towards Picard? For story purposes, why did Jennifer need to be killed by the Borg? Could she not simply have died another way that didn't require shoehorning Locutus and Wolf 359 into the story?

It was kind of neat in that episode, but in retrospect it seems very odd considering neither Picard nor the Borg ever make another appearance on the show.

My take on it was the tension was there to help set Picard and Sisko, and DS9 by extension, apart from Next Generation. DS9 was a going to be a darker show and its genesis was in one of the darkest times we had seen in Federation history. I liked that Sisko didn't like Picard at first, but by the end of the episode had come around. It was a lot of character growth for a pilot episode, perhaps more than we had seen with any other Trek captain in their pilots.

I also thought going back to Wolf 359 was a great move. BOBW was arguably TNG's finest outing and I don't think it hurt DS9 to bask in some of that glow. Plus the DS9 pilot finally gave us a taste of what the Battle of Wolf 359 had been like. And I think it showed how much Sisko had been forged in battle, with war playing such a major role on DS9 in later years, it was one of the best Trek historical moments to introduce us to his character, and it also tied him to Picard in an nontraditional way and made his hostility feel more legitimate than petty, if if perhaps unfair.

I do get your point about the Borg not showing back up on DS9. I would've loved a follow-up. Personally I thought Sisko could fit just as easily into First Contact as Picard did as the main character. And it would've been great to see Sisko take on the Borg at least one more time, early in the series before the Dominion became so prominent.