SQLquery - can this execute on each new insert


I am having a problem with the function called SQLquery. Then again, it might be that I just don' t understand 100% how this function works, specifically, when this function gets executed.

What I need is the ability to generate the next ID number based on the MAX that exist in table within our CRM system. This only needs to happen when there is an Insert of a new record.

I have a DTS file that has several steps. The last step Inserts a new record into an existing table. It is in this step that I need to be able to find the MAX id number in the table and add one (1) to it and then assign it to the new Inserted record. Here is the function that I am using that is assign to the appropriate field in the new record:

Sqlquery(" " , " ScheduleMapping" , " Select max(cast(new_id as int)) from [CSFP_MSCRM].[dbo].[FilteredNew_Mapping]" ) + 1

This function appears to work fine for the first new insert. But if there is a second or third new insert, it appears to assign (return) the same number as when it first ran.


- does this function only execute ONCE within a DTS file?

- Could it be possible that the new inserted record with the incremented New_id isn' t posted to the system before the function runs the second time?

Any guidance on this would be greatly appreciated.

Thank you


Posted by Thomas K. on Sep 20 2011 12:50PM { U2710 , F8 , T2214 , C7061 }

(8) Answers