|
Subject:
|
Assigning a @@ROWCOUNT to a variable
|
|
Posted By:
|
ioates
|
Post Date:
|
1/8/2004 6:22:14 AM
|
I have a DTS package that creates 7 textfiles from a table (on a weekly basis).
What I want to do, as each textfile is created is to assign the recordcount to a variable and then use the 7 variables (at the end of the package) to log the changes historically, by a date in another table.
This is the code I have
DECLARE @Total1 int
EXEC exportTextfile1
SET @Total1 = @@ROWCOUNT
--and then at the last step of the package
INSERT INTO logTable (Total1 , Total2 , Total3, ...)
VALUES (@Total1, @Total2, @Total3, ...)
--The error message I get is this
Error 'Must declare the variable Total1, Total2, ...'
How and where do I assign a variable within a DTS package, I have looked at the 'Set Global Variable' Object, but I am unsure
|
|
Reply By:
|
planoie
|
Reply Date:
|
1/8/2004 11:20:06 AM
|
What kind of package actions are you using? "Execute SQL Task"?
Peter
------------------------------------------------------
Work smarter, not harder.
|
|
Reply By:
|
ioates
|
Reply Date:
|
1/9/2004 3:16:27 AM
|
Peter
Hope this helps
- 1 Dynamic Properties Task that is set first (see 1)
- 1 SQL Server Connection, that has
- 7 Transform Data Tasks, that link to (see 2)
- 7 seven texfile (destination)
- Upon completion of all 7 texfiles an Execute SQL task (see 3)
1. Destination Property = Value
Source Type = Global Variable
Source Value = @Total1
2. DECLARE @Total1 int
EXEC exportTextfile1 -- An SP to get resultset
SET @Total1 = @@ROWCOUNT
3. INSERT INTO logTable (Total1 , Total2 , Total3, ...)
VALUES (@Total1, @Total2, @Total3, ...)
|
