February 1, 2006 at 9:41 am
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, try this SQL:
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
February 1, 2006 at 3:43 pm
Try naming the column in your resultset:
SELECT @@IDENTITY As RowID
In the Output Parameters dialog, set the "Output Parameter Type" to "Row Value" and complete the mapping of your column named "RowID" to the name of your variable.
February 2, 2006 at 8:19 am
One would think that would work, wouldn't one?
Unfortunately, DTS doesn't see things that way. I tried this:
INSERT INTO tblDataload_Price_Load_Log (Division_Code, Price_Level )
VALUES (?, ?)
SELECT (@@IDENTITY) As LoadID
I can see and set the output parameter 'LoadID' to my global variable and the step compiles and executes, but the LoadID value does not get updated. Why not? Beats me. I then tried this:
INSERT INTO tblDataload_Price_Load_Log (Division_Code, Price_Level )
VALUES (?, ?)
SELECT MAX(Load_ID) FROM tblDataload_Price_Load_Log AS LoadID
Now DTS does not even display LoadID, or anything else, in the Parameters list for output parameters. Maddening, isn't it? I certainly hope SSIS makes things like this possible.
Any other ideas?
February 2, 2006 at 11:25 am
Maybe this is a scope or session problem.
Since the execute sql task basically puts a "GO" after each sql statement, try to put a semicolon after your insert command:
INSERT INTO tblDataload_Price_Load_Log (Division_Code, Price_Level )
VALUES (?, ?);
SELECT (@@IDENTITY) As LoadID
If that doesn't work, you will have to go to a stored procedure:
CREATE PROCEDURE INSERT_PRICE_LOAD_LOG @div VARCHAR(2), @PRICELEVEL INT, INTLOADID INT OUTPUT
AS
INSERT INTO tblDataload_Price_Load_Log (Division_Code, Price_Level )
VALUES (@DIV, @PRICELEVEL)
RETURN @@IDENTITY
GO
February 2, 2006 at 11:27 am
Correction:
Maybe this is a scope or session problem.
Since the execute sql task basically puts a "GO" after each sql statement, try to put a semicolon after your insert command:
INSERT INTO tblDataload_Price_Load_Log (Division_Code, Price_Level )
VALUES (?, ?);
SELECT (@@IDENTITY) As LoadID
If that doesn't work, you will have to go to a stored procedure:
CREATE PROCEDURE INSERT_PRICE_LOAD_LOG @div VARCHAR(2), @PRICELEVEL INT, @INTLOADID INT OUTPUT
AS
INSERT INTO tblDataload_Price_Load_Log (Division_Code, Price_Level )
VALUES (@DIV, @PRICELEVEL)
RETURN @@IDENTITY
GO
February 3, 2006 at 9:33 am
DTS doesn't seem able to use parameters ('?') and SQL local variables in the same batch. I tried something very much like your suggestion; when I tried using parameters to get the input values for the SQL/sproc, DTS refused to compile the task. I then reverted to local variables to hold the Globals then using the locals in the SQL/sproc with the same result.
I even tried the Rube Goldberg-esque workaround suggested here: http://www.sqldts.com/default.aspx?234
at which point I decided there must be better (read 'simpler') way to accomplish this simple task.
February 3, 2006 at 2:29 pm
Nothing comes to mind except an ActiveX task that uses ADO.
January 5, 2008 at 5:34 am
You can make it a two step process:
1st sql task: do the insert
2nd sql task: pull identity into global var
link 1st and 2nd task with a "on success" workflow
/Kaare
April 5, 2008 at 3:23 am
I have the same problem too. Is it resolved yet?
April 5, 2008 at 3:23 am
I have the same problem too. Is it resolved yet?
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply