• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 937
  • Last Modified:

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

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)

VALUES([User::FileType],
 [System::MachineName],"Incognito_server",[User::SourceDirectory],"Incognito",SYSDATE,"SUCCESS",[User::InsertRowCount],NULL,NULL)
 


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
(file_type,
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




 
0
srionline2k6
Asked:
srionline2k6
  • 2
1 Solution
 
BartVxCommented:
If I remember correctly, you need to replace the variables in your query string with a '?' , then map the parameter numbers to the variables.

So:

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

Then map parameters:
0 --> [User::FileType]
1 -->  [System::MachineName]
etc
0
 
BartVxCommented:
The above is correct for OLEDB and Excel connections. For you specific connection, check following link:

http://technet.microsoft.com/en-us/library/ms140355.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now