Our programming team is more comfortable scripting in sql than working with SSIS packages. We have a stored procedure that will initiate a job for an SSIS package - running as a proxy user with the correct windows credentials to import from a network file share. All of this runs fine. The issue arises when we want to make sure the holding table for this data is clear before importing. If we include a 'delete' clause at the beginning of the sql script, the procedure only executes the sql job and the remaining part of the stored procedure is ignored. However, if we remove that line and include that as part of the SSIS package all works fine....why? Included is a sample of the script.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
ALTER procedure [dbo].[_GEN_AR_HIGHER_ONE_STUD_PAY_IMPORT_SP] AS
SET NOCOUNT ON;
SELECT @@CCCOUNT = (SELECT COUNT(*) FROM TRANS_HIST_IMPORT WHERE SOURCE_CDE = 'CC') IF @@CCCOUNT > 0 BEGIN SET @RTNMSG = 'CC TRANSACTIONS ALREADY EXIST. PLEASE REVIEW GL IMPORT FUNCTION.' GOTO ENDOFPROC; END
INSERT INTO TRANS_HIST_IMPORT ( some columns )
asked Apr 24, 2015 at 04:53 PM in Default
OK. I tried to piece this together into a single script so it is legible.
The problem I see is that once you do the INSERT to TRANS_HIST_IMPORT you don't have a transaction. It either jumped to the goto OR it was already committed. Then your script checks @@ERROR and attempts a rollback. This would throw an error because there is no transaction. Another possibility is that the DELETE is failing. Maybe you have the call to this procedure wrapped in a try/catch block and the error is getting squelched?
answered Apr 24, 2015 at 06:56 PM