It seems so simple; within a single Execute SQL Task, insert a new row into a
log table and store the new Identity column value in a global variable. Yet
I can't get it to work.

I started with this:

DTS pkg global variable: intLoadID (int) which I set to zero.

Execute SQL Task:

INSERT INTO tblDataload_Price_Load_Log (Division_Code, Price_Level )
VALUES('71', 900) SELECT @@IDENTITY
-- end of SQL task.

On the Output Parameters tab, I selected intLoadID for the Output Global
Variables. The Parameters column had no value. This was my first clue
things were not right. The SQL parsed ok and the package executes without
error. A new row is definately added to the table, but the global variable
stays at zero.

Ok, maybe this will work:

SELECT Load_ID FROM tblDataload_Price_Load_Log WHERE Load_ID = (SELECT
@@IDENTITY)

Now I have the value 'Load_ID' available in the Parameters column of the
Output Parameters tab, but still no change; the global variable intLoadID
stays at zero.

What am I missing here?

Thanks.

dj

Hello dj,

Have you thought about using a proc to do this?

http://www.sqldts.com/default.aspx?234

Whilst not as simple it will work.

Allan

Thanks for the quick reply.

I saw that on the superb sqldts site, but it strikes me as an insanely
arcane method to accomplish such a simple task. Even if I did have time to
screw around with smoke-and-mirrors code like that, I sure as hell don't have
time to try documenting it for the next poor chump that has to try to
maintain it.

There must be a better way.

dj

"Allan Mitchell" wrote: