none
Multiple update statements in SP not executing in SSIS

    Question

  • Hi,

    I have created an SP which has multiple updates on one table. When i call the SP from SQL, all update staements are run successfully. Now when I am calling the same SP from ETL the last update does not run. Not able to understand why. I have given the sample code for my SP creation. The Column1 from Table1 should get updates with Column1New value. But it doesnt.

    Also the parameter datatype in SP is INT. the variable definition in SSIS is also INT.

    I have used Exceute SQL Task to execute the SP.In parameter Mapping data type is LONG.One of the value that may get passed is '-99'.

    Can anyone help me out with this?

    Thanks in Advance!!     

    BEGIN 
     
         BEGIN TRAN  
     
           DECLARE @Temptable TABLE (Key1 INT,Key2 INT,Column1New bit) 
          
           INSERT INTO @Temptable 
            ( 
             Key1, 
             Key2, 
             Column1New
            )         
            SELECT  
               T1.Key1 
              ,T2.Key2  
              ,(Some calculations)  AS Column1New
            FROM  
              Table1 T1 
            INNER JOIN Table2 T2 ON  
             T2.Key1 = T1.Key1       
            INNER JOIN Table3 T3 ON 
             T2.Key2 = T3.Key2 
            WHERE 
             T1.Key3 > @Parameter1
            
    --1st Update which works                

          UPDATE        
           Table1 
          SET        
           Column3=NewFunction(Column3)
          FROM  
           Table1  T1 
           INNER JOIN  @Temptable RD ON 
           (T1.Key1 = RD.Key1) 
           INNER JOIN Table3  T3 ON      
           (T3.Key2 = RD.Key2) 
          WHERE 
           --Some condition
    --2nd Update which works                
         
          UPDATE  
           Table1 
          SET        
           Column2    = dbo.udfConvertDateToInt(Column3)
          WHERE 
           --Somecondition

    --Last Update which doesnt not work     
          UPDATE  
           Table1 
          SET        
            Column1=RD.Column1New--BSQV-35
           FROM Table1 T1 
           INNER JOIN @Temptable RD ON 
           (T1.Key1 = RD.Key1)
          WHERE 
           Key3 > @Parameter1
     
          SELECT @@ROWCOUNT AS WorkerRowsUpdated      
     
         COMMIT TRAN    
         
        END 


    Wednesday, March 12, 2014 4:15 AM

Answers

  • The job is run on another environment using batch file with a different credential.

    Hi Inquisitive_26,

    Based on your description, it seems that the issue occurs because the user account under which the SQL Server Agent job runs doesn’t have sufficient permission to perform the UPDATE operations. To check if it is the root cause, create a proxy account using the credentials that is used to execute the batch file on another server, and then set the current job to run under the new proxy.

    Reference:
    http://technet.microsoft.com/en-us/library/ms190698(v=sql.105).aspx 

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Wednesday, March 19, 2014 7:09 AM
    Moderator

All replies

  • is the execute sql task getting completed successfully? Are you getting any warnings etc in progress tab when package runs? Also is package run from a job or under a different login?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 12, 2014 5:27 AM
  • Hi,

    Execute SQL task is getting completed successfully. The job is run on another environment using batch file with a different credential.

    Thanks

    Wednesday, March 12, 2014 6:18 AM
  • The job is run on another environment using batch file with a different credential.

    Hi Inquisitive_26,

    Based on your description, it seems that the issue occurs because the user account under which the SQL Server Agent job runs doesn’t have sufficient permission to perform the UPDATE operations. To check if it is the root cause, create a proxy account using the credentials that is used to execute the batch file on another server, and then set the current job to run under the new proxy.

    Reference:
    http://technet.microsoft.com/en-us/library/ms190698(v=sql.105).aspx 

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Wednesday, March 19, 2014 7:09 AM
    Moderator