How To Configure Database Activities ?

News Knowledge Base
1

How to configure Database Activities?

Step By Step Guide

  1. Excel

    1. Setup Environment
    2. Activities
      1. Connect

        ConnectionString: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\WORK\TestDB.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;ReadOnly=0;"""

        ProviderName: "System.Data.OleDb"

      2. Insert

        Query with parameters example:

        "INSERT INTO [CNDN_BY_AWB_HDR$] ([COMPANY_CODE],[CASE_ID],[REASON_CODE],[CREDIT_NOTE_TYPE],[ADJUSTMENT_TYPE],[INVOICE_NUMBER],[AWB_COUNT],[INVOICE_DATE],[SAP_SYSTEM],[PAPERFEE_ON_INVOICE],[EXCEPTION_FROM_REBOOKING_FEE]) VALUES (@CompanyCode,@CaseId,@ReasonCode,@CreditNoteType,@AdjustmentType,@InvoiceNumber,@AwbCount,@invoiceDate,@SapSystem,@PaperfeeOnInvoice,@ExceptionFee)"

  2. Access Database

    1. Setup Environment: Install DB2OLEDBV5_x64.msi
    2. Activities
      1. Connect

        ConnectionString: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Users\ramona.cirstoiu\Documents\DatabaseTEST1.accdb;Persist Security Info=False;"

        ProviderName: "System.Data.OleDb"

  3. SQL Server

    1. Setup Environment
    2. Activities
      1. Connect

        ConnectionString: "Data Source=ROQAWSQL05;Initial Catalog=TESTDB;Persist Security Info=True;User ID=sa;Password=1qazXSW@"

        ProviderName: "System.Data.SqlClient"

      2. Insert

        Query with parameters example:

        "INSERT INTO CNDN_BY_AWB_HDR (COMPANY_CODE,CASE_ID,REASON_CODE,CREDIT_NOTE_TYPE,ADJUSTMENT_TYPE,INVOICE_NUMBER,AWB_COUNT,INVOICE_DATE,SAP_SYSTEM,PAPERFEE_ON_INVOICE,EXCEPTION_FROM_REBOOKING_FEE) VALUES (@CompanyCode,@CaseId,@ReasonCode,@CreditNoteType,@AdjustmentType,@InvoiceNumber,@AwbCount,@invoiceDate,@SapSystem,@PaperfeeOnInvoice,@ExceptionFee)"

  4. Oracle

    1. Setup Environment

      1. Install Oracle Instant Client and ODBC Package (both of them 32 bit - http://www.oracle.com/technetwork/database/windows/index-098976.html). Run the odbc_install.exe file.

      2. Locate the tnsnames.ora file (you can also copy it in the instant client folder).

      3. Add to System Variables:
        -> Add to PATH the folder where the Instant Client is (for example, c:\Apps\instantclient_12_2)

        -> Create new System variable TNS_ADMIN which points to the folder where tnsnames.ora is located (for example, c:\Apps\instantclient_12_2)

      4. In ODBC Data Source Administrator (32 bit), System DSN tab, add a new System Data Source and use the "Oracle in instantclient" driver. Test the connection. (see attached screenshot, OracleODBC).

    2. Activities
      1. Connect

        ConnectionString: "Dsn=ORACLE_DSN;Uid=testuser;Pwd=testpass;"

        ProviderName: "System.Data.Odbc"

      2. Insert

        Query without parameters example:

        "INSERT INTO CNDN_BY_AWB_HDR (COMPANY_CODE,CASE_ID,REASON_CODE,CREDIT_NOTE_TYPE,ADJUSTMENT_TYPE,INVOICE_NUMBER,AWB_COUNT,INVOICE_DATE,SAP_SYSTEM,PAPERFEE_ON_INVOICE,EXCEPTION_FROM_REBOOKING_FEE) VALUES (‘CompanyCode’,’CaseId’,’ReasonCode’,’CreditNoteType’,’AdjustmentType’,’InvoiceNumber’,’AwbCount’,’invoiceDate’,’SapSystem’,’PaperfeeOnInvoice’,’ExceptionFee’)"

        Query with parameters example:

        "INSERT INTO CNDN_BY_AWB_HDR (COMPANY_CODE,CASE_ID,REASON_CODE,CREDIT_NOTE_TYPE,ADJUSTMENT_TYPE,INVOICE_NUMBER,AWB_COUNT,INVOICE_DATE,SAP_SYSTEM,PAPERFEE_ON_INVOICE,EXCEPTION_FROM_REBOOKING_FEE) VALUES (:CompanyCode,:CaseId,:ReasonCode,:CreditNoteType,:AdjustmentType,:InvoiceNumber,:AwbCount,:invoiceDate,:SapSystem,:PaperfeeOnInvoice,:ExceptionFee)"