none
DBTYPE_I4 RRS feed

  • Question

  • I am getting the below error when I try to insert the system variable values ErrorCode and ErrorDescription into Audit table in Event handling part of SSIS

    [Execute SQL Task] Error: Executing the query "INSERT INTO Audit_Backuplog( DatabaseId, Databas..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    The table has these columns defined as below  

    ErrorCode  NVARCHAR(1000),
    ErrorDesc NVARCHAR(2000)

    I have the below expression in the exe sql task editor on Onerror event in Event handler 

    "INSERT INTO Audit_Backuplog
    ( DatabaseId,
    DatabaseName,
    DatabaseBackUpPath, 
    BkUpSuccessfull, 
    BkUpDtTime, 
    BkUpBy, 
    PkgComponent,
    ErrorCode,
    ErrorDesc
    )
    VALUES
    ('"+(DT_WSTR,3)(@[User::DatabaseId])+"','"+
    @[User::DatabaseName]+"','"+
    @[User::DbBkUpPath]+"','No','"+(DT_WSTR,50)@[System::CreationDate]+"','"+
    @[System::UserName]+"','"+
    @[System::TaskName]+"','"+(DT_WSTR,50)@[System::ErrorCode]+"',RTRIM(LEFT('"+@[System::ErrorDescription]+"',1000)))"




    Neil



    • Edited by Neilcse Tuesday, February 21, 2017 8:33 AM
    Tuesday, February 21, 2017 7:53 AM

All replies

  • Hi Neil,

    Is the result set property mapped to "None" and if possible post the screen shots of parameter mapping/ Result set Tabs.

    Tuesday, February 21, 2017 10:41 AM
  • Hi,

    The result set properties is mapped to none and there are no parameter as I am using package & system variables.

    Regards,


    Neil

    Tuesday, February 21, 2017 11:22 AM
  • Try the below Query

    INSERT INTO Audit_Backuplog
     ( DatabaseId,
    DatabaseName,
    DatabaseBackUpPath, 
    BkUpSuccessfull, 
    BkUpDtTime, 
    BkUpBy, 
    PkgComponent,
    ErrorCode,
    ErrorDesc
     )
     VALUES
     (?,?,?,'No',?,?,?,?,RTRIM(LEFT(?,1000)))

    and for each '?', map a parameter in the parameter Tab

    There is an example here http://www.sqlis.com/post/The-Execute-SQL-Task.aspx

    Refer "Returning a Single Value, Passing in Two Input Parameters"

    • Proposed as answer by Albert_ Zhang Wednesday, February 22, 2017 4:57 AM
    Tuesday, February 21, 2017 1:21 PM
  • Evaluate the expression and try run the result of the expression in SSMS

    If that actually works fine, you may need to put a breakpoint on the pre-execute of the sql task and check what the values of ErrorCode and ErrorDescription are. You say it is on the OnError event handler so I would expect a value to exist for them both

    Tuesday, February 21, 2017 7:36 PM
  • This is working copy which I have used. This is working perfectly for me by scheduling it using sql job

    Might help others!

    USE [FRAMEWORK] GO CREATE TABLE [dbo].[fw_backupDbStatus]( [Id] [int] IDENTITY(1,1) NOT NULL, [DbServer] [nvarchar](150) NULL, [DbName] [nvarchar](150) NULL, [Flag] [char](1) NOT NULL, [DbDesc] [nvarchar](200) NULL, [Status] [nvarchar](50) NULL, [BackUpPath] [nvarchar](500) NULL, [LastBackupOn] [datetime2](7) NULL, CONSTRAINT [UK_UniqueDbName] UNIQUE NONCLUSTERED ( [DbName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[fw_backupDbStatus] ADD DEFAULT ('') FOR [DbServer] GO ALTER TABLE [dbo].[fw_backupDbStatus] ADD DEFAULT ('n') FOR [Flag] GO use FrameWork truncate table [fw_backupDbStatus] insert into [fw_backupDbStatus] (DBName,Flag,DbDesc,BackUpPath) values ('DataMart' ,'y' ,'Datamart' ,'C:\DailyDbBackup\'), ('DataMart_Med' ,'y' ,'Datamart' ,'C:\DailyDbBackup\'), ('framework' ,'y' ,'ETLFramework' ,'C:\DailyDbBackup\') use FrameWork select * from backupDBs USE [FRAMEWORK] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_backupDbStatus_job] AS BEGIN DECLARE @v_cur CURSOR, @v_dbname NVARCHAR(150), @v_fullpath NVARCHAR(500), @v_backuppath NVARCHAR(500), @v_filename NVARCHAR(150), @v_str NVARCHAR(500) BEGIN SET @v_cur = CURSOR FOR SELECT DbName,BackUpPath FROM dbo.fw_backupDbStatus WHERE flag = N'y' OPEN @v_cur FETCH NEXT FROM @v_cur INTO @v_dbname,@v_backuppath WHILE @@FETCH_STATUS = 0 BEGIN update fw_backupDbStatus set status = N'Executing' where DBName = @v_dbname and flag = N'y' SET @v_filename = @v_dbname+N'.bak' SET @v_fullpath = 'N'''+@v_backuppath+@v_filename+'''' BEGIN SET @v_str = N' BACKUP DATABASE '+@v_dbname+ N' TO DISK = '+@v_fullpath+N' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 20' END exec (@v_str) update fw_backupDbStatus set status = N'Complete', LastBackupOn = getdate() where DBName = @v_dbname and flag = N'y'; FETCH NEXT FROM @v_cur INTO @v_dbname,@v_fullpath END END END



    Neil


    • Edited by Neilcse Friday, June 14, 2019 1:56 PM minor edits
    Friday, June 14, 2019 1:55 PM