delete data from table in script vs. SSIS package

Question

question

sgrant asked Apr 24, '15

delete data from table in script vs. SSIS package

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 BEGIN ***DELETE _GEN_AR_HIGHER_ONE_IMPORT;*** SET NOCOUNT ON; DECLARE @ReturnCode INT; DECLARE @cmdline varchar(500); DECLARE @RTNMSG nvarchar(200); DECLARE @@CCCOUNT AS INT; 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 BEGIN TRANSACTION SELECT @ReturnCode = 0 EXEC @ReturnCode = msdb.dbo.sp_start_job @job_name=N'_GEN_AR_STUDENT_PAYMENT_IMPORT' IF (@@ERROR <> 0 OR @ReturnCode <> 0) BEGIN SET @RTNMSG = 'Error on import in SQL Job. Contact the Helpdesk.' IF (@@TRANCOUNT > 0) BEGIN ROLLBACK TRANSACTION END GOTO ENDOFPROC END COMMIT TRANSACTION INSERT INTO TRANS_HIST_IMPORT ( some columns )

import script-task sql-script-file

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

seanlange commented · Apr 24, 2015 at 06:25 PM

It runs but you have a begin transaction and no commit. Also, why do you have the set nocount after you delete your table? The nocount should be first.

0 ·

sgrant commented · Apr 24, 2015 at 06:28 PM

Sorry the rest got cut off. There is a commit transaction after the insert, but the insert never happens.

0 ·

seanlange commented · Apr 24, 2015 at 06:34 PM

You still haven't posted all of the code. You have **cough** goto **cough** statements in there but you didn't post a named block for it to go to. You also have a variable with two @@ at the beginning. Even thought it is legal is it mighty confusing.

0 ·

sgrant commented · Apr 24, 2015 at 06:47 PM

INSERT INTO TRANS_HIST_IMPORT ( some columns ) SELECT blah, blah, blah IF @@ERROR <> 0 BEGIN SET @RTNMSG = 'Error msg. Contact the Helpdesk.' ROLLBACK TRANSACTION GOTO ENDOFPROC END ELSE Begin COMMIT TRANSACTION End ENDOFPROC: SELECT @RTNMSG AS RESULTS; END

0 ·

Answer 1 · 2015-04-24T18:56:17Z

seanlange answered Apr 24, '15

OK. I tried to piece this together into a single script so it is legible. ALTER PROCEDURE [dbo].[_GEN_AR_HIGHER_ONE_STUD_PAY_IMPORT_SP] AS BEGIN DELETE _GEN_AR_HIGHER_ONE_IMPORT; SET NOCOUNT ON; DECLARE @ReturnCode INT; DECLARE @cmdline VARCHAR(500); DECLARE @RTNMSG NVARCHAR(200); DECLARE @@CCCOUNT AS INT; 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 BEGIN TRANSACTION SELECT @ReturnCode = 0 EXEC @ReturnCode = msdb.dbo.sp_start_job @job_name = N'_GEN_AR_STUDENT_PAYMENT_IMPORT' IF ( @@ERROR 0 OR @ReturnCode 0 ) BEGIN SET @RTNMSG = 'Error on import in SQL Job. Contact the Helpdesk.' IF (@@TRANCOUNT > 0) BEGIN ROLLBACK TRANSACTION END GOTO ENDOFPROC END COMMIT TRANSACTION INSERT INTO TRANS_HIST_IMPORT (SOME columns) SELECT blah ,blah ,blah IF @@ERROR 0 BEGIN SET @RTNMSG = 'Error msg. Contact the Helpdesk.' ROLLBACK TRANSACTION GOTO ENDOFPROC END ELSE BEGIN COMMIT TRANSACTION END ENDOFPROC: SELECT @RTNMSG AS RESULTS; END 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?

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sgrant commented · Apr 24, 2015 at 07:15 PM

the commit is int the complete code, sorry, there are too many characters to load it into a comment. The important question is that if I remove the delete statement altogether the procedure works perfectly and I don't understand why. Perhaps you are correct and we should put it into a try/catch block and see what happens. thanks for the suggestions.

0 ·

question