10 Replies Latest reply on Apr 16, 2021 4:08 AM by Krishna Kumar

    Insert data into target database issue

    Vijay Bhasker New Member

      Hi,

       

      Kindly Help us to resolve following issue with high priority ASAP

      WRT_8023 Error truncating target table [dbo."FSL Product Item"]. [Error executing truncate target table query: DELETE FROM dbo."FSL Product Item"]

      we are getting above issue while inserting records to target in Dev environment.

       

        • 1. Re: Insert data into target database issue
          Vijay Bhasker New Member

          Please help on this following Error

          IICS task is not able to insert data into Navision target. and showing error as above

          Target Details
          Connection:*PROD_SQL_Server_Nav_West_Prod_Data
          Target Object:*FSL Product Item
          Use alphabetical order:  No 
          Truncate Target:True
          Enable Target Bulk LoadTrue

           

          target Connection as follows

           

          Please help me with high precarity

          • 2. Re: Insert data into target database issue
            Krishna Kumar Support Moderators

            Could you please share below logs for our analysis?

             

            1. Session log

            2. Tomcat.log : (Secure Agent Installation Directory)\apps\Data_Integration_Server\logs\tomcat\tomcat<app_spec>.log

            • 3. Re: Insert data into target database issue
              Krishna Kumar Support Moderators

              Also, please check if the table is available in the target database, and the user used in the connection has necessary permissions to truncate the table. thanks

              • 4. Re: Insert data into target database issue
                Vijay Bhasker New Member

                Thank you Krishna Kumar, Concern table is available at target

                and session log as follows

                Task Name: Populate_Product_Item_From_FSL_To_Navision_All
                Agent Group Id: 000VNF25000000000002
                Agent Group Name: PIDWDLSVDEV282
                Agent Id: 000VNF08000000000002
                Agent Name: PIDWDLSVDEV282
                04/15/2021 06:10:27 **** Importing Connection: SrcConn_000VNF0B000000000059 ...
                04/15/2021 06:10:27 **** Importing Connection: TargConn_000VNF0B000000000058 ...
                04/15/2021 06:10:27 **** Importing Source Definition: ProductItem ...
                04/15/2021 06:10:27 **** Importing Target Definition: FSL_Product_Item ...
                04/15/2021 06:10:27 **** Importing SessionConfig: cfg_s_dss_000VNF0I0000000001LE ...
                    <Warning> :  The Error Log DB Connection value should have Relational: as the prefix.
                    <Warning> :  Invalid value  for attribute Error Log DB Connection. Will use the default value
                    Validating Source Definition  ProductItem...
                    Validating Target Definition  FSL_Product_Item...
                04/15/2021 06:10:27 **** Importing Mapping: m_dss_000VNF0I0000000001LE ...
                Validating transformations of mapping m_dss_000VNF0I0000000001LE...
                Validating mapping variable(s).
                    <Warning> :  Invalid table attribute: Exec SQL
                04/15/2021 06:10:27 **** Importing Workflow: wf_dss_000VNF0I0000000001LE ...
                    <Warning> :  The value entered is not a valid integer.
                    <Warning> :  Invalid value NO for attribute Fail task after wait time. Will use the default value
                [Summary for Application Source Qualifier SQ_ProductItem]
                     Is a new repartition point.
                     Partition type changed from None to Pass Through.
                Successfully extracted session instance [s_dss_000VNF0I0000000001LE].  Starting repository sequence id is [467462248]
                ======================================================================
                DIRECTOR> VAR_27085 [2021-04-15 06:10:28.061] Parameter file [E:\Informatica Secure Agent\apps\Data_Integration_Server\data\parameters\s_dss_000VNF0I0000000001LE.param] is opened for [session [wf_dss_000VNF0I0000000001LE.s_dss_000VNF0I0000000001LE]].
                DIRECTOR> VAR_27062 [2021-04-15 06:10:28.061] Warning! Cannot find section for worklet [wf_dss_000VNF0I0000000001LE] and folder [] in parameter file [E:\Informatica Secure Agent\apps\Data_Integration_Server\data\parameters\s_dss_000VNF0I0000000001LE.param].
                DIRECTOR> VAR_27028 [2021-04-15 06:10:28.062] Use override value [s_dss_000VNF0I0000000001LE_6_error.csv] for user-defined workflow/worklet variable:[$ErrorFileName].
                DIRECTOR> VAR_27028 [2021-04-15 06:10:28.062] Use override value [1970-01-01] for user-defined workflow/worklet variable:[$LastRunDate].
                DIRECTOR> VAR_27028 [2021-04-15 06:10:28.062] Use override value [1970-01-01T00:00:00Z] for user-defined workflow/worklet variable:[$LastRunTime].
                DIRECTOR> VAR_27028 [2021-04-15 06:10:28.062] Use override value [s_dss_000VNF0I0000000001LE_4_15_2021_11_10_success.csv] for user-defined workflow/worklet variable:[$SuccessFileName].
                DIRECTOR> TM_6014 [2021-04-15 06:10:28.062] Initializing session [s_dss_000VNF0I0000000001LE] at [Thu Apr 15 06:10:28 2021].
                DIRECTOR> TM_6683 [2021-04-15 06:10:28.062] Repository Name: [XMLRepository]
                DIRECTOR> TM_6684 [2021-04-15 06:10:28.062] Server Name: [rDTM]
                DIRECTOR> TM_6686 [2021-04-15 06:10:28.062] Folder: []
                DIRECTOR> TM_6685 [2021-04-15 06:10:28.062] Workflow: [wf_dss_000VNF0I0000000001LE] Run Instance Name: [] Run Id: [0]
                DIRECTOR> TM_6101 [2021-04-15 06:10:28.062] Mapping name: m_dss_000VNF0I0000000001LE.
                DIRECTOR> TM_6964 [2021-04-15 06:10:28.062] Date format for the Session is [MM/DD/YYYY HH24:MI:SS]
                DIRECTOR> TM_6703 [2021-04-15 06:10:28.066] Session [s_dss_000VNF0I0000000001LE] is run by 64-bit Integration Service  [], version [9.1.0 HotFix1], build [0627].
                MANAGER> PETL_24058 [2021-04-15 06:10:30.119] Running Partition Group [1].
                MANAGER> PETL_24000 [2021-04-15 06:10:30.119] Parallel Pipeline Engine initializing.
                MANAGER> PETL_24001 [2021-04-15 06:10:30.120] Parallel Pipeline Engine running.
                MANAGER> PETL_24003 [2021-04-15 06:10:30.120] Initializing session run.
                MAPPING> CMN_1569 [2021-04-15 06:10:30.122] Server Mode: [UNICODE]
                MAPPING> CMN_1570 [2021-04-15 06:10:30.122] Server Code page: [UTF-8 encoding of Unicode]
                MAPPING> TM_6151 [2021-04-15 06:10:30.122] The session sort order is [Binary].
                MAPPING> TM_6185 [2021-04-15 06:10:30.122] Warning. Code page validation is disabled in this session.
                MAPPING> CMN_65084 [2021-04-15 06:10:30.122] Current Timezone:[UTC -6:0]
                MAPPING> CMN_65085 [2021-04-15 06:10:30.123] Current Process ID:[9752]
                MAPPING> TM_6155 [2021-04-15 06:10:30.123] Using HIGH precision processing.
                MAPPING> TM_6180 [2021-04-15 06:10:30.123] Deadlock retry logic will not be implemented.
                MAPPING> TM_6181 [2021-04-15 06:10:30.123] Session source-based commit interval is [5000].
                MAPPING> PMJVM_42020 [2021-04-15 06:10:30.131] [INFO] Loaded library : E:\Informatica Secure Agent\apps\jdk\1.8.0_275_SA\jre\bin\server\jvm.dll.
                MAPPING> PMJVM_42014 [2021-04-15 06:10:30.132] [DEBUG] The value specified for option JVMOption2 is not valid and will be ignored.
                MAPPING> PMJVM_42014 [2021-04-15 06:10:30.132] [DEBUG] The value specified for option JVMOption3 is not valid and will be ignored.
                MAPPING> PMJVM_42014 [2021-04-15 06:10:30.132] [DEBUG] The value specified for option JVMOption4 is not valid and will be ignored.
                MAPPING> PMJVM_42014 [2021-04-15 06:10:30.132] [DEBUG] The value specified for option JVMOption5 is not valid and will be ignored.
                MAPPING> PMJVM_42009 [2021-04-15 06:10:30.638] [INFO] Created Java VM successfully.
                MAPPING> SDKS_38029 [2021-04-15 06:10:31.141] Loaded plug-in 500100: [plugin description].
                MAPPING> SDKS_38024 [2021-04-15 06:10:31.172] Plug-in 500100 initialization complete.
                MAPPING> SDKS_38016 [2021-04-15 06:10:31.172] Reader SDK plug-in intialization complete.
                MAPPING> TM_6308 [2021-04-15 06:10:31.172] DTM error log using code page UTF-8.
                MAPPING> TM_6310 [2021-04-15 06:10:31.172] DTM error log enabled at file [E:\Informatica Secure Agent\apps\Data_Integration_Server\data\error\s_dss_000VNF0I0000000001LE_6_error.csv].
                MAPPING> TM_6682 [2021-04-15 06:10:31.172] Warning: Since DTM Error Logging is enabled, row level errors will not be written to the session log. Please check the DTM Error Log for any row level errors.
                MAPPING> TM_6701 [2021-04-15 06:10:31.173] Warning: The data column delimiter for DTM Error logging and the flat file delimiter are the same [','].
                MAPPING> TE_7022 [2021-04-15 06:10:31.174] TShmWriter: Initialized
                MAPPING> DBG_21491 [2021-04-15 06:10:31.241] Source-based commit interval based on rows from Source Qualifier [SQ_ProductItem]
                MAPPING> DBG_21244 [2021-04-15 06:10:31.241] Initializing Load Order Group [1]. Source Qualifier is [SQ_ProductItem]
                MAPPING> TM_6007 [2021-04-15 06:10:31.241] DTM initialized successfully for session [s_dss_000VNF0I0000000001LE]
                DIRECTOR> PETL_24033 [2021-04-15 06:10:31.263] All DTM Connection Info: [<NONE>].
                MANAGER> PETL_24004 [2021-04-15 06:10:31.263] Starting pre-session tasks. : (Thu Apr 15 06:10:31 2021)
                PRE-SESS> DBG_21349 [2021-04-15 06:10:31.268] No load constraints for Target [FSL_Product_Item]. It does not reference any primary table from the same active source or transaction generator.
                PRE-SESS> DBG_21104 [2021-04-15 06:10:31.268] Determining truncate order for [FSL Product Item] [instance id:2]
                PRE-SESS> DBG_21531 [2021-04-15 06:10:31.268]     Truncate order for [FSL Product Item] [instance id:2] determined successfully
                PRE-SESS> CMN_1021 [2021-04-15 06:10:31.288] Database driver event...
                CMN_1021 [The Windows authentication method for accessing Microsoft SQL Server is deprecated. Informatica intends to drop support for the Windows Authentication in a future release. Use the Windows Authentication v2 option as the authentication type to access Microsoft SQL Server from Data Integration using the Secure Agent hosted on a Linux or Windows machine.

                ODBC Event Using single row fetch.

                ODBC Event Using Single Row Inserts. connect string = [Driver=DataDirect CLOSED 8.0 SQL Server Wire Protocol for Informatica - R36;DB=NAV_WEST_DATA;HOST=PIDWDLSVSQL650;PORT=1433;UID=;WorkArounds2=2;EQI=1;AM=4;APP=Informatica;TimestampTruncationBehavior=1]. userid = []

                DBMS Name: Microsoft SQL Server

                DBMS Version: 12.00.6164

                Driver Name: dwsqls28.dll

                Driver Version: 08.02.0381 (B0713, U0507)]

                PRE-SESS> WRT_8365 [2021-04-15 06:10:31.289] Starting truncate for target table [dbo."FSL Product Item"]. : (Thu Apr 15 06:10:31 2021)
                PRE-SESS> WRT_8445 [2021-04-15 06:10:31.289] SQL statement for truncate is: [TRUNCATE TABLE dbo."FSL Product Item"].
                PRE-SESS> WRT_8023 [2021-04-15 06:10:31.290] Error truncating target table [dbo."FSL Product Item"].  [Error executing truncate target table query: TRUNCATE TABLE dbo."FSL Product Item"]
                PRE-SESS> CMN_1022 [2021-04-15 06:10:31.291] Database driver error...
                CMN_1022 [
                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot find the object "FSL Product Item" because it does not exist or you do not have permissions.]

                PRE-SESS> WRT_8208 [2021-04-15 06:10:31.291] Error truncating target table [dbo."FSL Product Item"].  The Integration Service is trying the DELETE FROM query.
                PRE-SESS> WRT_8445 [2021-04-15 06:10:31.291] SQL statement for truncate is: [DELETE FROM dbo."FSL Product Item"].
                PRE-SESS> CMN_1761 [2021-04-15 06:10:31.291] Timestamp Event: [Thu Apr 15 06:10:31 2021]
                PRE-SESS> WRT_8023 [2021-04-15 06:10:31.291] Error truncating target table [dbo."FSL Product Item"].  [Error executing truncate target table query: DELETE FROM dbo."FSL Product Item"]
                PRE-SESS> CMN_1761 [2021-04-15 06:10:31.291] Timestamp Event: [Thu Apr 15 06:10:31 2021]
                PRE-SESS> CMN_1022 [2021-04-15 06:10:31.291] Database driver error...
                CMN_1022 [
                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The DELETE permission was denied on the object 'FSL Product Item', database 'NAV_WEST_DATA', schema 'dbo'.]

                PRE-SESS> CMN_1761 [2021-04-15 06:10:31.291] Timestamp Event: [Thu Apr 15 06:10:31 2021]
                PRE-SESS> WRT_8023 [2021-04-15 06:10:31.291] Error truncating target table [dbo."FSL Product Item"].  [DELETE FROM dbo."FSL Product Item"]
                MANAGER> PETL_24026 [2021-04-15 06:10:31.354] Pre-session task completed with failure. : (Thu Apr 15 06:10:31 2021)
                MANAGER> PETL_24005 [2021-04-15 06:10:31.354] Starting post-session tasks. : (Thu Apr 15 06:10:31 2021)
                MANAGER> PETL_24029 [2021-04-15 06:10:31.417] Post-session task completed successfully. : (Thu Apr 15 06:10:31 2021)
                MAPPING> SDKS_38025 [2021-04-15 06:10:31.425] Plug-in 500100 deinitialized and unloaded with status [-1].
                MAPPING> SDKS_38018 [2021-04-15 06:10:31.426] Reader SDK plug-ins deinitialized with status [-1].
                MAPPING> TM_6315 [2021-04-15 06:10:31.426] DTM error log: file log [E:\Informatica Secure Agent\apps\Data_Integration_Server\data\error\s_dss_000VNF0I0000000001LE_6_error.csv], rows inserted [0].
                MAPPING> TM_6018 [2021-04-15 06:10:31.426] The session completed with [0] row transformation errors.
                MANAGER> PETL_24002 [2021-04-15 06:10:32.185] Parallel Pipeline Engine finished.
                DIRECTOR> PETL_24013 [2021-04-15 06:10:32.246] Session run completed with failure.
                DIRECTOR> TM_6022 [2021-04-15 06:10:32.246]

                SESSION LOAD SUMMARY
                ================================================

                DIRECTOR> TM_6252 [2021-04-15 06:10:32.246] Source Load Summary.
                DIRECTOR> CMN_1740 [2021-04-15 06:10:32.246] Table: [SQ_ProductItem] (Instance Name: [SQ_ProductItem])
                  Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
                DIRECTOR> TM_6253 [2021-04-15 06:10:32.246] Target Load Summary.
                DIRECTOR> CMN_1740 [2021-04-15 06:10:32.246] Table: [FSL_Product_Item] (Instance Name: [FSL_Product_Item])
                  Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
                DIRECTOR> TM_6023 [2021-04-15 06:10:32.246]
                ===================================================

                DIRECTOR> TM_6020 [2021-04-15 06:10:32.246] Session [s_dss_000VNF0I0000000001LE] completed at [Thu Apr 15 06:10:32 2021].

                • 5. Re: Insert data into target database issue
                  Nico Heinze Guru

                  Also please check on the DBA side whether this DELETE FROM... has been aborted due to the transaction logs overflowing. This happens to us all the time with larger tables in SQL Server (but then we're using ODBC to access SQL Server, and PowerCenter cannot issue a TRUNCATE TABLE over ODBC).

                   

                  Regards,

                  Nico

                  • 6. Re: Insert data into target database issue
                    Vijay Bhasker New Member

                    I will show you the issue, if you come online, may resolve it ASAP.

                    Please help me

                    • 7. Re: Insert data into target database issue
                      Krishna Kumar Support Moderators

                      We see below error in the session log.

                       

                      1. FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The DELETE permission was denied on the object 'FSL Product Item', database 'NAV_WEST_DATA', schema 'dbo'.]

                       

                      2. FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot find the object "FSL Product Item" because it does not exist or you do not have permissions.]

                       

                      -> Could you please share the screenshot from the database side that the table exists?  Also, the delete permission was denied on that object. It would be great if you could check with your DBA on the same. thanks

                      FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot find the object "FSL Product Item" because it does not exist or you do not have permissions.]

                      • 8. Re: Insert data into target database issue
                        Krishna Kumar Support Moderators

                        Please raise a technical ticket if this is urgent. Someone from Global Customer Support team should help you in isolating the issue.

                        • 9. Re: Insert data into target database issue
                          Vijay Bhasker New Member

                          facing a issue to raise a technical ticket, Please help me with steps to raise technical ticket