Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
|
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 8th, 2004, 07:22 AM
Authorized User
 
Join Date: Jun 2003
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default Assigning a @@ROWCOUNT to a variable

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

 
Old January 8th, 2004, 12:20 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

What kind of package actions are you using? "Execute SQL Task"?

Peter
------------------------------------------------------
Work smarter, not harder.
 
Old January 9th, 2004, 04:16 AM
Authorized User
 
Join Date: Jun 2003
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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, ...)










Similar Threads
Thread Thread Starter Forum Replies Last Post
ASSIGNING A JAVA SCRIPT VARIABLE TO A XSL VARIABLE SOMANATHAN10 XSLT 1 February 21st, 2007 04:26 AM
Assigning variable value from a radio button cybersnac VB How-To 0 February 14th, 2007 03:57 PM
Assigning value from variable cyberddindia Classic ASP Basics 3 November 7th, 2006 11:57 AM
Help with assigning a variable from a binding dfranczak ADO.NET 0 July 17th, 2003 01:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.