Dataload using Powershell - 15+GB /day

Question

Dataload using Powershell - 15+GB /day

gk-411903

Hall of Fame

Points: 3801
More actions
May 23, 2009 at 11:46 am

#200790

Hello All
I have to load approximately 17+GB of data per day for a DataWarehouse db. The files come on an hourly basis. So i am looking at a GB/hr. I am not allowed load PERL on the server for whatever reasons. So i have decided to go the Powershell way and now learning Powershell. I am googling for some URL's and want to know if any of you out there has already done it. If so , can the following be answered.
I did try out Data Insert / Select using Powershell for few rows so i got the taste of the syntax.
1. In the Get-Content , how do i get one line at a time?
2. How do i get the individual columns assuming they are delimited by comma (for each object?)
3. How do i trap exceptions / errors in Powershell when i insert a row?
4. How reliable is BEGIN / END TRANSACTION for batch loading / rolling back?
Apologies if my SQL Server knowledge is limited and you see errors above.
TIA

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

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

I'm no Powershell pro. But if you try to load CSV files into your database you should have a look at the SQL Server "bcp" command line utility or BULK INSERT. It's quiet fast.

Greets

Flo

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

I agree with Flo. Bulk Insert will load 5.1 million rows of data 20 columns wide in less than 60 seconds.

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

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 3

Powershell is not going to be very good at loading data - since it really is not designed for that. But, you can use powershell to build the bcp/bulk insert commands.

I'd probably go with bcp so you can keep everything at the command line level - however, you could easily use SQLCMD and BULK INSERT called from powershell.

The other option is to build an SSIS package to manage your data loads, since that is exactly what that tool is designed for. But, if you don't have any transformations to be performed - it might be a little overkill for your project.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs