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 September 9th, 2005, 11:42 AM
sjm sjm is offline
Registered User
 
Join Date: Aug 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Bulk Insert TSQL in DTS – Rows Affected Count



I have a TSQL bulk insert command in a DTS package as an Execute SQL Task because I need to have triggers fired on the table as the data is inserted. I wish to know if there is a way to have the total number of rows inserted to be returned so I can put it in a variable? I was hoping this would be stored in msdb.sysdtssteplog as progresscount but it is not.

I am interested in comments and/or suggestions.


 
Old October 11th, 2005, 12:51 PM
sjm sjm is offline
Registered User
 
Join Date: Aug 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Since I was unable to get the rowcount value from the bulk insert TSQL command in a DTS Execute SQL Task, the alternative I used was to dynamically configure the SQL Statement at runtime with a VBscript and the global variable value of the number of rows that should have been inserted. So the task ends up as:

BULK INSERT (table)
               FROM '(file)'
               WITH (FIRE_TRIGGERS,
               FORMATFILE = '(formatfile)')
               IF (34693609=@@ROWCOUNT)
                    BEGIN
(misc)
                    END
               ELSE
                    BEGIN
                         (misc)
                    END




 
Old May 30th, 2008, 11:40 AM
Registered User
 
Join Date: May 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You wouldn't still happen to have a sample of the script you wrote for this would you? If so I would love to get a hold of a copy as I am in the depths of this right now and could use a hand.

Thanks,
Rich

Quote:
quote:Originally posted by sjm
 Since I was unable to get the rowcount value from the bulk insert TSQL command in a DTS Execute SQL Task, the alternative I used was to dynamically configure the SQL Statement at runtime with a VBscript and the global variable value of the number of rows that should have been inserted. So the task ends up as:

BULK INSERT (table)
             FROM '(file)'
             WITH (FIRE_TRIGGERS,
             FORMATFILE = '(formatfile)')
             IF (34693609=@@ROWCOUNT)
                    BEGIN
(misc)
                    END
             ELSE
                    BEGIN
                         (misc)
                    END









Similar Threads
Thread Thread Starter Forum Replies Last Post
Bulk Insert snufse SQL Server 2000 9 December 17th, 2007 07:53 AM
insert rows in database in a bulk from grid harrysingh26 ASP.NET 2.0 Professional 6 March 6th, 2006 11:46 AM
Bulk Insert luma SQL Server DTS 1 July 13th, 2005 01:48 AM
Bulk insert deyakhatib SQL Server 2000 6 March 4th, 2004 04:09 AM





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