Audit logging usign SSIS using Execute sql task... Need help

Posted on 2011-10-11
Last Modified: 2012-06-22
I have to log the result in the audit table in ORacle as I am not aware much of oracle I think I am doing something wrong here. Below is the table that I need to populate into and I have this values. How do i need to populate using SSIS execute sql task. I did mapped the variables in the parameter mapping in executesql task I

insert into audit_logs
(file_type,importing_Host,source,Directory, filename,LOAD_DATE,LOAD_STATUS,LOADED,ERRORS,DISCARDED)


I put them in Execute sql task and ran along with teh dataflow task while dataflow task is successful when it comes to this query. I faced the following error...
Do i need to pass in the expressions tab as a sqlstatementsource.

[Execute SQL Task] Error: Executing the query "
insert into audit_logs
Imp..." failed with the following error: "ORA-01036: illegal variable name/number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Am i doing anything wrong? Please guide me through this

Question by:srionline2k6
    LVL 3

    Accepted Solution

    If I remember correctly, you need to replace the variables in your query string with a '?' , then map the parameter numbers to the variables.


    VALUES(?, ?,"Incognito_server",?,"Incognito",SYSDATE,"SUCCESS",?,NULL,NULL)

    Then map parameters:
    0 --> [User::FileType]
    1 -->  [System::MachineName]
    LVL 3

    Expert Comment

    The above is correct for OLEDB and Excel connections. For you specific connection, check following link:

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now