Hi I am using the following query in the execute sql task. I have variable as variable1 (datatype int32). In the result set, it is single row and in the result window of execute sql task , i added a variable with result name as 'count' ( as i alias sql%rowcount as count) and variable name as variable1. I ran such queries in Management studio with @@rowcount to capture the no of updated rows.I was thinking if @@rowcount is replaced by 'sql%rowcount ', the query will in execute sql task if the database is oracle. But it is giving me error as : invalid datatype update table1 set job_name = 'Job1' WHERE job_name = 'job2'; select sql%rowcount as count; please advice Thanks

I am sorry The error is : invalid character

Please describe how you've set up the Execute SQL Task more clearly - refer to SSIS variables as SSIS variables. Depending on the type of connection manager you've used in the Execute SQL Task, using parameters/SSIS variables with it differs. Does that query function against Oracle? If you're getting an "invalid character" message, I think not... Talk to me now on

yes, the query in the execute sql task is pointing towards oracle database.

Does the query work in Oracle, outside of SSIS? Talk to me now on

did you find the solution? Thanks

I had a similar problem and fixed it by doing the following: In Oracle: Create a new stored procedure similar to the following (note datatype of output variable - I could only get this to work if it's a varchar): CREATE OR REPLACE PROCEDURE tangni.testproc3 ( new_job_name IN varchar, old_job_name IN varchar, update_count OUT varchar) IS BEGIN update test set name = new_job_name where name = old_job_name; update_count := sql%rowcount; commit; END; In SSIS Create a variable in SSIS called count with datatype of string. Create an Execute SQL Task as follows: General Tab ResultSet: None ConnectionType: OLE DB Connection: <your oledb connection using OraOLEDB.Oracle.1 provider> SQLSourceType: Direct input SQLStatement: {call tangni.testproc3('aaa','bbb', ?)} IsQueryStoredProcedure: False (greyed out) Parameter Mapping Tab Add new parameter with following details: Variable Name : <your variable name> Direction: Output Data Type: VARCHAR (nothing else would work for me) ParameterName: 0 ParameterSize: 1000000 (or however big the count might be) Note if you get this working youll probably want to replace the aaa and bbb with parameters. Hope this helps

If this works, it's likely due to a problem with OLEDB/ADO/ODBC and 64 bit INTs. Talk to me now on

I tried your suggestion like: CREATE OR REPLACE PROCEDURE testproc (update_count OUT varchar) IS BEGIN MERGE INTO tgt USING (select distinct ID, ADDRESS,CITY, STATE FROM table1)src ON (src.ID = tgt.ID) WHEN MATCHED THEN UPDATE SET tgt.address = src.address, tgt.city = src.city, tgt.state = src.state WHEN NOT MATCHED THEN INSERT (address,city,tmstp,ID,state) VALUES (src.address,src.city,Systimestamp,src.ID,src.state); update_count := sql%rowcount; commit; END; I dont have any input parameters. I am calling this proc in execute sql task as "Execute testproc (?)" and mapping the "?" to count variable created in ssis. I AM GETTING THE ERROR AS : [Execute SQL Task] Error: Executing the query "{call testproc(?)} " failed with the following error: "ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "TESTPROC", line 48 ORA-06512: at line 1". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I am not sure if i am doing it right. Please Advice.

Actuallt it is working now. All i did is that i made the variable count as int32 datatype (previously it was string). The task is is working and when i try to insert the value 'count variable' in the log table like insert into log table (total_count) values(?) I am getting a '0' (zero) as the value of count which is not the case as i know there are some updated records. Somehow the value of the varibale is not correct. Please Advice

Can you run your stored procedure outside of SSIS (i.e. using SQLPlus or SQLNavigator) to check it does return a value? By the way, I really struggled to get this to work as an int and instead had to make it a very large string - as suggested I think that this could be due to my Oracle Connectors but I'm loathe to change them after all the problems I've had!

Hi I used your suggestion and it is working fine for me. I want to know one thing, you said ParameterSize: 1000000 . Does that mean that it will do the count upto 1000000. If the update or insert is more than that, it will throw an error. Because i did the same (ParameterSize: 1000000), and my updates are more that 4 million, and i am able to log that in the table even though the ParameterSize: 1000000 . May be i misunderstood the meaning of ParameterSize: 1000000 . Plaese explain. Thanks