Bulk Insert TSQL in DTS – Rows Affected Count

Question

Bulk Insert TSQL in DTS – Rows Affected Count

Seth Martel

Old Hand

Points: 392
More actions
September 12, 2005 at 7:39 am

#67044

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 have searched through materials over the past week and I have been unable to find a way to accomplish this goal. I would appreciate it if someone could confirm if this is possible with SQL Server 2000.

Otherwise I am going to have to count the rows on the table before and after the insert, but I would prefer to get the affected rowcount directly from the bulk insert tsql command.

I am interested in comments and/or suggestions.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You have an option with the bulk inserts. You can add the keyword "FIRETRIGGERS" so that the triggers still fire with the insert. You can do the same for the constraints if needed.

philcart SSC-Forever Points: 47797 More actions · Answer 2

So are you executing the BULK INSERT via an ExecuteSQL task, or are you using the in-built Bulk Insert task? I can't confirm it at the moment, but I believe there is a property on the Bulk Insert task which holds the number of rows inserted.

--------------------
Colt 45 - the original point and click interface

Seth Martel Old Hand Points: 392 More actions · Answer 3

I appreciate your response.

The bulk insert is done by an Execute SQL task. I need to fire triggers on the table during the insert and it is my understanding that this is not possible with the Bulk Insert Task so I am using the Execute SQL task.

The TSQL bulk insert returns the affected rows in QA, can I capture this in DTS?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

As I said it IS possible with the bulk insert, check the books online for the exact syntax.

Seth Martel Old Hand Points: 392 More actions · Answer 5

RGR'us:

I appreciate your assistance with this matter.

If it is possible to use the DTS Bulk Insert Task while firing triggers, please identify exactly how to do so. There is no option on the Bulk Insert Task Properties for FIRE_TRIGGERS and nowhere to put hints. It is my understanding that I am limited to the properties sheet in DTS. Please explain by what you mean by syntax for the DTS Bulk Insert Task.

I am presently using the Bulk Insert TSQL Command with the FIRE_TRIGGERS hint. This works fine.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

You seem to be right. You'll have to look up the exact syntax in bol and fire it that way. But it shouldn't be too hard.

philcart SSC-Forever Points: 47797 More actions · Answer 7

How about a different approach,

1) Bulk Insert into a staging table so you can easily get your rowcount.

2) Insert into production table so your triggers fire.

--------------------
Colt 45 - the original point and click interface

Seth Martel Old Hand Points: 392 More actions · Answer 8

phillcart:

I would really like to use staging tables and do the loads like that, but the client is very much against staging tables. They are using staging tables now and they want to stop.

I am beginning to get the impression that it is not possible to capture the number of rows inserted directly from the bulk insert tsql command. The alternative is to count the rows on the table before the insert and after the insert, subtract and use that result in the comparison logic. I was hoping that there was a way to capture the affected rowcount directly since I would have more confidence in that figure.

Thanks for your help.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

How about you create a #temp table as the staging table? That way they wouldn't see the staging table and you'd get your way for hte row count. Also staging tables and dts go hand in hand. It's really not uncommon to see their use in DTS and frankly it's often the only way to go.

homebrew01 SSC Guru Points: 55553 More actions · Answer 10

In BOL, under FIRE_TRIGGERS, ... "No result sets generated by the insert triggers are returned to the client performing the bulk copy operation."

Is that relevant to what you hoped to accomplish ?

Seth Martel Old Hand Points: 392 More actions · Answer 11

Since I was unable to get the rowcount value from the bulk insert TSQL command in a DTS Execute SQL Task into a global variable, 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