Reza, Yes my problem is exactly what you just described but can you give me any example or some reference for the same. I have used OLE DB command many times for updation ,but how can we use it for a function which returns a value. Also this function will execute as many times as there the number of rows inserted in the table.So how can i use it. An example would really be appreciated. In Oracle sql developer this statement would be like - Insert into Table(Hexadecimal_column,Name,Marks) values (GetNextID(),'Neeraj',87) Here GetNextID is a function which will produce the new hexadecimal ID for new record inserted. but how/where can I use it in SSIS

There is a table in Oracle database which has one unique column(hexadecimal) which is filled by a fucntion 'GetNextID'(this function increments the hexadecimal number by one and return it back) everytime an insert happens in this table. I have data in excel sheet which needs to be inserted into the above table through SSIS. How do I fill that unique hexadecimal column by its next value when n number of records from the excel sheeet are inserting through SSIS. Please let me know ASAP.Its urgent...!!!

As I understood, you have a function on database side, named GetNextID, and now you want to run this function for each data row in excel sheet, and fetch the ID, and add it beside other columns in each row in destination table. Am I right? if yes, you can use OLE DB Command to run sql statements on database which you can connect with oledb connection there, and then you can use output and fetch it to a current column besides other columns. let me know if you need more informationhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Can anybody help me please. Its Urgent!

I would try as follows: Import the data into a staging table without creating a new ID, than run Insert into Table(Hexadecimal_column,Name,Marks) Select newID(), Name, Marks from stagingTable But this will work only if the function can be called in this way. If not, I assume you have to create an insert for every row.

OK, you probably have a source data which you read data from it and you want to insert them into destination database, Just with GetNextID. solution is: in the data flow task, right after your source, add an OLE DB Command Transformation, and connect green arrow from source to this transform. in the ole db command set an OLE DB Connection to oracle db, ( note that this should be OLEDB , and if you can not provide an ole db connection this solution isn't capable, just tell me to show you a work around for other types of connections ) then in the SQL Statement property of oledb command, write your function call sql statement, the syntax of sql statement here is exact same syntax which you write your sql commands in oracle. I am note sure about oracle syntax, but if you work with sql server syntax is : select GetNextID () you should replace this with your syntax, before this step, you should have a derived column transformation and create a dummy column with null value with data type which is same data type as result of GetNextID , then in the oledb command you can use this dummy column and in columns tab map this dummy column to output parameter of GetNextID function. this link show similar method but for calling an stored procedure from sql server with an output, the output fetched to a dummy column and then filled with oledb command, http://www.rad.pasfu.com/index.php?/archives/24-Output-Parameter-of-Stored-Procedure-In-OLE-DB-Command-SSIS.html just let me know if you have problem in implementationhttp://www.rad.pasfu.com

Actually how can I call a function in this way.this is my problem

I am not an oracle developer, maybe you call functions by CALL command, this should be exact same syntax you use in oraclehttp://www.rad.pasfu.com

with a googling, i found this syntax for oracle: create or replace function "ORACLE_FOO" (foo_arg1 in NUMBER) return NUMBER is rt NUMBER := 0; begin IF foo_arg1 > 1 THEN SELECT 1 INTO rt; ELSE SELECT 2 INTO rt; END IF; RETURN (rt); end; SELECT ORACLE_FOO(1) FROM dual; seems you just need to select ithttp://www.rad.pasfu.com

but how would I catch the return value of this function and bind it to a new derived column in the outgoing dataset.

you should create a dummy column with derived column before the oledb command, and then map output variable of oledb command to the dummy column, all steps described in the link I provided in previous posts from my blog posthttp://www.rad.pasfu.com