[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-11
2
Medium Priority
?
896 Views
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)

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
Comment
Question by:srionline2k6
  • 2
2 Comments
 
LVL 3

Accepted Solution

by:
BartVx earned 2000 total points
ID: 36956264
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
 
LVL 3

Expert Comment

by:BartVx
ID: 36956373
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 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