2 Replies Latest reply on Jun 5, 2013 11:33 AM by 998158

    FDM import issue

    998158
      Hi,

      when we execute import integration script, temporary table (strworktable) gets created in FDM schema which holds data that is imported.

      Now, we have a scenario, where in we need to insert data directly to strworktable using 'Insert into ...' statement.

      Is it possible to insert data to strworktable directly..if so can someone let me know how can we do that

      Thanks..
        • 1. Re: FDM import issue
          SH_INT
          There are examples in the FDM admin guide scripting section which detail how to do this.
          • 2. Re: FDM import issue
            998158
            Hi SH,

            Thanks for your reply.

            I referred admin guide, but could not get much info. about how to insert into strworktablename.

            However, I've tried code myself, but getting data access error, when exceuting script. As a first step, I am trying to insert values from tdataseg4 to strworktablename


            Below is the code:

            Function Load_test(strLoc, lngCatKey, dblPerKey, strWorkTableName)*
            *+'------------------------------------------------------------------+*
            +'Oracle Hyperion FDM Integration Import Script:+

            +'+
            *+'Created By:     +*
            *+'Date Created:     +*
            +'Purpose:+

            +'+

            *+'------------------------------------------------------------------+*

            Dim objSS 'ADODB.Connection*
            Dim strSQL 'SQL String*
            Dim rs 'Recordset*
            Dim rsAppend 'tTB table append rs Object*


            +'Initialize objects+
            Set cnSS = CreateObject("ADODB.Connection")*

            +'Connect To SQL Server database+

            cnss.open "Provider=ORAOLEDB.ORACLE;Data Source=devdb;User ID=FDM;Password=xxxx"*



            +'Initialize common SQL statement+
            strSQL = "INSERT INTO " & strWorkTableName*

            strSQL = strSQL & "(PartitionKey, CatKey, PeriodKey, DataView, CalcAcctType, Amount, Account, Entity, ICP,UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8, UD9, UD10) "*

            strSQL = strSQL & "SELECT PartitionKey, CatKey, PeriodKey, DataView, CalcAcctType, Amount,Account, Entity, ICP,UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8, UD9, UD10"*

            strSQL = strSQL & "FROM tdataseg4 "*


            DW.DBTools.mLogError 1, CStr(strSQL), CStr(strWorkTableName), Nothing*
            DW.DataManipulation.fExecuteDML(strSQL)*
            +'cnss.Execute strSQL+


            +'Records loaded+
            RES.PlngActionType = 6*
            RES.PstrActionValue = "SQL Import successful!"*
            +'Assign Return value+
            Load_test = True*
            cnss.Close*
            Set cnss = Nothing*

            End Function*


            Below is the error I'm recieving

            ERROR:+
            Code............................................. -2147217865+
            Description...................................... Data access error.+
            At line: 59+
            Procedure........................................ clsImpProcessMgr.fLoadAndProcessFile+
            Component........................................ upsWObjectsDM+
            Version.......................................... 1112+
            Thread........................................... 17616+

            Can you let me know if I'm missing something here..quick help on this is appreciated..

            Thanks in advance

            Edited by: 995155 on Jun 5, 2013 4:32 AM