The only mention I can find of an enemy ship being sunk by a spar torpedo during the Civil War is by the submarine Hunley but spar torpedo were used before the Hunley in semi-submersible "torpedo boats".
Were there any successful (sinking) attacks by these early non-submarine torpedo boats before the Hunley?
Edit: The CSS Albemarle was sunk by a spar after the Hunley
Spar torpedoes (or at least the idea of spar torpedoes) pre-date the American Civil War by several years. The concept of the spar torpedo was probably first invented by the Russian general von Tsienhausen, who proposed using offensive "torpedoes" against the Allied fleet during the Crimean War in 1856. By 1862, von Tsienhausen had built a working prototype of the spar torpedo and successfully demonstrated them at the Russian naval base in Cronstadt during the fall of 1862.
The Confederate Navy embraced spar torpedoes quite readily. Spar torpedoes could be cheaply built and fitted to small craft, allowing the out numbered Confederates to wage asymmetrical warfare against Union blockade warships. The Confederate Navy equipped several vessels with spar torpedoes. The ironclad ram CSS Atlanta carried a very large torpedoes during its June 1863 engagement between the USS Weehawken and the USS Nahant, but was grounded before it could use it. Union troops also found that the CSS Charleston carried a similar weapon when it was captured in February 1865.
Confederates launched at least five spar torpedo attacks on Union warships between 1862 and 1865, although only one (the Hunley attack actually sunk its targets) In one of the first attacks, the David attacked the USS New Ironsides off Charleston in October 1863. The explosion badly damaged Ironsides and nearly sunk David, but neither vessel sunk. Another confederate torpedo boat attempted an attack on the USS Memphis in March 1864, although the alert crew of the Memphis were able to avoid the Confederate vessel. In April 1864, the improbably-named Squib damaged the USS Minnesota (then the flagship of the North Atlantic Blockading Squadron) off Newport News. Another attack of Charleston in April 1864 failed after the alert crew of the USS Wabash spotted an incoming Confederate torpedo boat and escaped. And in early 1865, a Confederate boat attempted to sink the US gunboat Octaroa but its torpedo failed to explode.
Most of these attacks were conducted but the Confederate Torpedo Service, a unit dedicated to this type of warfare. However, citizen-sponsored milita groups also played in a role in several attacks. Most of the Hunley's crew, for example, were actually civilians and civilian volunteers played a major role in the first attack by David off Charleston.
We should also mention the successful US Navy's successful spar torpedo attack on the CSS Albemarle on the night of October 27, 1864. The resulting explosion sunk the ironclad...and the small US Navy boat. The action won Lorenzo Denning a Medal of Honor and earned William B. Cushing the Thanks of Congress.
After the American Civil War, torpedoes were widely used during the Russo-Turkish War (1877-1878) and Russian sailors made several towed and spar torpedoes attacks on Turkish vessels.
Sources:
*Royal Bird Bradford, Notes on the Spar Torpedo, (1882)
*Norman Polmar, Submarines of the Russian and Soviet Navies, 1718-1990, (1991)
*http://www.navyandmarine.org/ondeck/1862ConfTorpedoService.htm
It depends on how you define successful. The David class of Confederate semi-submersibles also used spar torpedos. On October 5, 1863 (several months before the Hunley attack on February 17, 1864), the David attacked the USS New Ironsides using a spar torpedo. It detonated under the New Ironside's startboard quarter and extinguished her boiler fires. The crew believed that the ship was sinking and began abandoning it; they soon found that it was relatively undamaged and restarted the fires before chasing off the David. There were two injuries associated with the attack (and a third death due to a gunshot, rather than the blast), so one might consider that to be a successful use of the torpedo.
The Hunely, however, was the first (and the last) use of a spar torpedo to sink a ship.