x

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 )

more ▼

asked Apr 24, 2015 at 04:53 PM in Default

avatar image

sgrant
1 2

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.

Apr 24, 2015 at 06:25 PM seanlange

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

Apr 24, 2015 at 06:28 PM sgrant

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.

Apr 24, 2015 at 06:34 PM seanlange

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

Apr 24, 2015 at 06:47 PM sgrant
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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?

more ▼

answered Apr 24, 2015 at 06:56 PM

avatar image

seanlange
997 1 4 6

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.

Apr 24, 2015 at 07:15 PM sgrant
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x22
x16
x10

asked: Apr 24, 2015 at 04:53 PM

Seen: 126 times

Last Updated: Apr 24, 2015 at 07:15 PM

Copyright 2017 Redgate Software. Privacy Policy