Flagging BCP Operation

Question

Flagging BCP Operation

murthykalyani

SSCarpal Tunnel

Points: 4500
More actions
December 31, 2009 at 9:12 am

#96788

Here is the scenario.
Have to bcp data from a flat file to a sql server table.
Upon completion of this operation, this needs to be flagged as success or failure.
Based on the flag result, I have to insert a record into a table.
This can be done through SSIS, but I have an issue, I have to do this same task i.e copying of data to various sql servers on demand. This can be done for one sql server or needs to be done all sql servers. For this very reason I want to do this with Power Shell.
Does any body have any idea how to flag a bcp operation?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

If you use BULK INSERT, you could check the table afterwards. I guess with bcp you could do the same. run a query and determine if the data was inserted.

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 2

Steve Jones - Editor (12/31/2009)
If you use BULK INSERT, you could check the table afterwards. I guess with bcp you could do the same. run a query and determine if the data was inserted.

I may insert data in batches, if there is an issue with the data with a batch at that pointbulk insert or bcp will fail and will roll back only that batch. But there will be data from previous batches that has got inserted to the table.

To me this may not work.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

murthykalyani (12/31/2009)
Steve Jones - Editor (12/31/2009)
If you use BULK INSERT, you could check the table afterwards. I guess with bcp you could do the same. run a query and determine if the data was inserted.
I may insert data in batches, if there is an issue with the data with a batch at that pointbulk insert or bcp will fail and will roll back only that batch. But there will be data from previous batches that has got inserted to the table.
To me this may not work.

That's why you should always BCP or BULK INSERT into a staging table and validate all of the data in the staging table before moving it to the final table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

I agree with Jeff. The issue is that you could issue this:

bcp...

if errorlevel = 0 then notify

However there are many places things could fail. At any point before the IF check, in between any batches, things could fail, including right before the IF check. If you tightly couple these processes like this, with the same computer process handle, you could easily miss a success or failure.

This is why we have a transaction log in SQL Server, so that two independent processes (write log, flush memory and write data files) exist to ensure ACID properties.

You want to design things so that your load process can start and stop, and restart, with batches. It should then update a table at the completion of the load. You have a completely separate process that checks for, or responds to that final update, and notifies you. It also should let you know if the time elapsed for the load has been too long.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

This is more what I'd like to see...

BULK INSERT into staging

validate w/error processing/logging

BULK INSERT into staging

validate w/error processing/logging

BULK INSERT into staging

validate w/error processing/logging

IF no validation failures, merge with final table (should never get an error at this point)

In 2k, BCP will log and isolate errors for repair and allow import to continue if that's what's desired.

In 2k5, same thing with both BCP and Bulk Insert.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 6

Hi Jeff and Steve,

Happy New Year.

Coming back to this topic. My task is to just to port the data from flat file most likely. So once I port the data if bcp is success then I have to insert a record into a log table flagging success, if not it will send an email.

I have another way of checking that has come to my mind i.e activating -e option in bcp command i.e redirecting any errors to an error log file.

Once bcp process is done, will check for size of log file, if it is 0 bytes then bcp process is success if not there is an failure and from there the other the other process that to check why failure is happening will follow.

I am plannning to do all this with power shell, let me know if there are any cons for this processes.

Murthy