The Happy Pig Motel is located in the Three Horns - Valley area, not far from the Bloodshot Ramparts. You'll find it along the road to the Dust on the north end of the valley. When you first arrive, the motel will have no power and be a bloody mess.
Your first mission will be to restore power to the motel during the "No Vacancy" mission. After power has been restored, you'll be able to access the "Neither Rain Nor Sleet Nor Skags" through the bounty board and later you can partake in the mission "The Ice Man Cometh" after you've complete Bright Lights, Flying City.