March 23, 2010 at 10:48 am
I'm relatively new to SSIS, only been using it intermittently for about 3-4 months now.
I'm working on a package right now that pulls a flat file from an FTP site, loads it into staging tables to do some data verification, then pushes the data into a live (currently just production) database. The push into the live system requires inserts into multiple tables, and if any one of the inserts fails, everything needs to be rolled back and some secondary actions need to be performed.
Due to time constraints and the fact that I haven't learned how to do the equivalent of T-SQL transactions within SSIS, the push to the live system is being done as a call to a stored procedure. The meat of the stored procedure is as follows:
BEGIN TRY
BEGIN TRANSACTION transaction1
{several INSERTs and UPDATEs}
COMMIT TRANSACTION transaction1
RETURN;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION transaction1
RETURN -1;
END CATCH
When I run the stored procedure from within SSMS, I get 0 if everything works, -1 if there's an error somewhere, just like I'd expect to.
When I run it as part of the SSIS package, the stored procedure runs as it's supposed to, only committing work when everything happens correctly, but the return value of -1 doesn't seem to trigger the "Failure" steps like I'd expect it to. Can anyone help me figure out what I'm missing?
Thanks in advance!
--Jennifer
Jennifer Levy (@iffermonster)
March 23, 2010 at 11:01 am
Try a RAISERROR statement, I'm thinking that the non-zero return doesn't matter to SSIS, but an error would..
CEWII
March 23, 2010 at 12:05 pm
Thanks, Elliott! Looking into it now.
(EDIT: And that got it. Thanks so much!!!)
Jennifer Levy (@iffermonster)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply