BULK INSERT to SQL server

[sonia jacob]

I have 43 gig file to be loaded to a table in SQL server 2005. There are about 2 billion records in there. Its a one-one mapping with no transformation. There are no keys on the table.
Using DSR and ODBC Stages the performace on it was very poor. The maximum that DS could do was around 2000 - 3000 rows per sec. And I am talking about 2 billion records to be loaded to a single table in less than 24 hours. This is just one load, but the biggest one. Hence I started using BULK INSERT of SQL server to bulk load data to the tables. The Query would be as below

BULK INSERT STG_FINANCIAL_COLLECTION_DATA
FROM #source file name#
WITH (FORMATFILE=#format file name#, BATCHSIZE=200000,ROWS_PER_BATCH=2000000000)

BATCHSIZE=200000 should commit the records in batches of 200000 and also clear the log. I ran this at the backend and found that after 200000 teh records get commited and log get cleared. But when the same query is run through DS using Dynamic RDBMS stage or the ODBC stage, we do not see the commits happening. The log gets filled up in teh first half hour, the load fails the "Rollback" kicks in. The same happens when the query is wrapped in a Stored Proc.

I was hoping to figure out why DS / DataDirect 5.2 SQL Server Wire Protocol is unable to process the commits in batches? The same query works through SQL Query Analyser, which is the client that we use for SQL server.

[rajeev_prabhuat]

Hi,

I am trying to load the data into Windows SQL Server using Bulk insert, but I am getting an error "APT_CombinedOperatorController,0: Fatal Error: Fatal: Stage supports bulk load to SQL Server only on Windows".
So please let me know what options should I set to carry out the activity in DRS Stage?

Regards
Rajeev Prabhu