0

This is my script where I grab SCOPE_IDENTITY() and assign to SSIS Package variable.

--START:  SCRIPT TO CHECK IF THERE IS ENTRY FOR 'UNKOWNN'  IN AUDIT DIMESNION - IF NOT INSERT

IF NOT EXISTS (SELECT AUDIT_SK FROM SHARED.AUDIT_DIM WHERE AUDIT_SK = -1)
BEGIN
SET IDENTITY_INSERT SHARED.AUDIT_DIM ON 
INSERT INTO [SHARED].[AUDIT_DIM]
       (AUDIT_SK
       ,[JOB_ID]
       ,[OUTPUT_STREAM_ID]
       ,[OUTPUT_STREAM_DESC]
       ,[SELECTION_START_DATE]
       ,[SELECTION_END_DATE]
       ,[JOB_RUN_DATE])
 VALUES
       (-1
       ,-1
       ,-1
       ,'UNKNOWN'
       ,'1000-01-01'
       ,'1000-01-01'
       ,'1000-01-01')
SET IDENTITY_INSERT SHARED.AUDIT_DIM OFF    
END

--- END 'UNKOWNN'  IN AUDIT DIMESNION 

INSERT  INTO [SHARED].[AUDIT_DIM]
    (
      [JOB_ID],
      [OUTPUT_STREAM_ID],
      [OUTPUT_STREAM_DESC],
      [SELECTION_START_DATE],
      [SELECTION_END_DATE],
      [JOB_RUN_DATE]
    )
VALUES  (
      ?,  --parameters passed from package variables
      ?,  --parameters passed from package variables
      ?,  --parameters passed from package variables
      ?,  --parameters passed from package variables
      ?,  --parameters passed from package variables
      ?   --parameters passed from package variables
     )

SELECT CONVERT(INT,SCOPE_IDENTITY()) -- This is assigned back to Package variable

This was all working fine for last couple of months and its is constantly failing with following error.

Message

Executed as user: XXXXXXX\xxxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:53:54 PM Error: 2012-04-30 15:54:07.15 Code: 0xC002F210 Source: Generate AUDIT_SK Execute SQL Task Description: Executing the query "--START: SCRIPT TO CHECK IF THERE IS ENTRY FOR 'U..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I2)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:53:54 PM Finished: 3:54:07 PM Elapsed: 13.151 seconds. The package execution failed. The step failed.

If I copy the package to different enviroment or run the script alone in SSMS all works fine.

Can someone guide me where should i start looking into?

Thanks

Sreedhar
  • 29,307
  • 34
  • 118
  • 188
  • So, what changed? Something in the package, something with the table? – Aaron Bertrand May 04 '12 at 03:57
  • Sure package was not changed, but there was an upgrade on production server. Though package worked fine after the upgrade for couple of weeks and suddenly started to throw this error. – Sreedhar May 04 '12 at 03:59
  • Sorry it was my fault didnt check the variable defined for parameter JOB_ID; it was defined as data_type 'SHORT' and the value being passed was too bigger to fit in the variable. This been now changed to long and all worked fine. SQL Agent job error message was not quite clear to understand. – Sreedhar May 10 '12 at 02:34

0 Answers0