Bulk Insert Failed

Question

Bulk Insert Failed

Viewing 6 posts - 16 through 20 (of 20 total)

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

frederico_fonseca SSCoach Points: 16038 More actions · Answer 1

Jeff Moden wrote:

water490 wrote:
I used powershell to create a script that prepares the files for bulk insert. it ensures that the columns match with the destination table. works beautifully.
You mean a poweshell script that writes to another file? If so, that seems like a waste. I can see having a PoSh that creates the proper BCP format file, which requires no duplication of data on disk.

or better yet powershell that reads file and loads directly to the database (although if this powershell is part of the SSIS package doing it in c# script would even be better)

water490 SSC Eights! Points: 918 More actions · Answer 2

Jeff Moden wrote:

water490 wrote:
I used powershell to create a script that prepares the files for bulk insert. it ensures that the columns match with the destination table. works beautifully.
You mean a poweshell script that writes to another file? If so, that seems like a waste. I can see having a PoSh that creates the proper BCP format file, which requires no duplication of data on disk.

SSIS task is setup to run the script. The powershell script overwrites the existing ones.

water490 SSC Eights! Points: 918 More actions · Answer 3

frederico_fonseca wrote:

Jeff Moden wrote:
water490 wrote:
I used powershell to create a script that prepares the files for bulk insert. it ensures that the columns match with the destination table. works beautifully.
You mean a poweshell script that writes to another file? If so, that seems like a waste. I can see having a PoSh that creates the proper BCP format file, which requires no duplication of data on disk.
or better yet powershell that reads file and loads directly to the database (although if this powershell is part of the SSIS package doing it in c# script would even be better)

I prefer to using C# to handle the loading and moving files etc. I am more familiar with C# than I am with powershell.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

water490 wrote:

frederico_fonseca wrote:
Jeff Moden wrote:
water490 wrote:
I used powershell to create a script that prepares the files for bulk insert. it ensures that the columns match with the destination table. works beautifully.
You mean a poweshell script that writes to another file? If so, that seems like a waste. I can see having a PoSh that creates the proper BCP format file, which requires no duplication of data on disk.
or better yet powershell that reads file and loads directly to the database (although if this powershell is part of the SSIS package doing it in c# script would even be better)
I prefer to using C# to handle the loading and moving files etc. I am more familiar with C# than I am with powershell.

If your any good at all with good ol' DOS (Cmd Line), you can do it all from T-SQL.

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

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

frederico_fonseca wrote:

Jeff Moden wrote:
water490 wrote:
I used powershell to create a script that prepares the files for bulk insert. it ensures that the columns match with the destination table. works beautifully.
You mean a poweshell script that writes to another file? If so, that seems like a waste. I can see having a PoSh that creates the proper BCP format file, which requires no duplication of data on disk.
or better yet powershell that reads file and loads directly to the database (although if this powershell is part of the SSIS package doing it in c# script would even be better)

I've been meaning to ask someone and it would seem that you have the experience to answer... have you done a performance comparison between such a PoSh push to the database compared to using BULK INSERT? I have not but I'm curious to know, if you have the time.

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

water490 SSC Eights! Points: 918 More actions · Answer 6

Jeff Moden wrote:

frederico_fonseca wrote:
Jeff Moden wrote:
water490 wrote:
I used powershell to create a script that prepares the files for bulk insert. it ensures that the columns match with the destination table. works beautifully.
You mean a poweshell script that writes to another file? If so, that seems like a waste. I can see having a PoSh that creates the proper BCP format file, which requires no duplication of data on disk.
or better yet powershell that reads file and loads directly to the database (although if this powershell is part of the SSIS package doing it in c# script would even be better)
I've been meaning to ask someone and it would seem that you have the experience to answer... have you done a performance comparison between such a PoSh push to the database compared to using BULK INSERT? I have not but I'm curious to know, if you have the time.

No I have not. The BULK INSERT did the job so I stopped there. It is part of SSIS so I will set up the job to run it unattended. So if it takes 5 minutes or 20 minutes it won't matter. I am not sitting there waiting for it.