Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pwcphoto
Yak Posting Veteran
69 Posts |
 Posted - 2005-12-11 : 22:30:20
|
| I am trying to import a database from PosgresSQL to MSSQL 2000, I can use DTS no problem on the smaller tables, but I have some very large tables with several million rows and it is crashing DTS and the Enterprise Manager. Is there any way to get DTS not to buffer all of the info in memory which it appears to be doing, and just transfer small chuncks at a time? Watching the system memory, it just keeps climbing until it is all gone then enterprise manager dies.I have loaded the latest pgsql odbc drivers and have been able to import smaller tables of up to 100,000 rows, but on tables of several million rows it dies.It actually dies even before running the actual import. when I click the button to manually set the DTS parameters it is the same thing. It must try to load the source data, not only column names into memory prior to doing anything.There any registry keys to keep it from pre-loading the entire source database?Other options for importing the data?Thanks,Phil-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-12 : 00:13:16
|
| Why don't you just export the data out of Posgres, then use BCP to import the data into the system. Make sure your database is set to bulk recovery or simple mode though, or you are going to blow up the transaction log file. Run a backup before and after. You should be able to import millions of rows easily and quickly this way.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
pwcphoto
Yak Posting Veteran
69 Posts |
Posted - 2005-12-12 : 17:03:49
|
| So there is no way to get DTS to process a smaller number of rows at a time and step through it is what you are saying? It must do them all at once? Heck of a design for an "enterprise" piece of software.Thanks,Phil-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-12-12 : 17:40:24
|
| What can I say, if you're building enterprise software, don't use DTS.Also if you are doing this from EM on a client, DTS will pass all the data through your client computer as well.rockmoose |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-12-12 : 21:35:16
|
quote:
Originally posted by pwcphoto
So there is no way to get DTS to process a smaller number of rows at a time and step through it is what you are saying? It must do them all at once? Heck of a design for an "enterprise" piece of software.Thanks,Phil-----------------------I used to be a rocket scientist. Now I'm just a space cadet...
yes, you can get DTS to process a smaller chunk of data. Are you using the transform data task? it has an option for "insert batch size" that you can set.-ec |
|
|
|
|
|
|
|
|
Importing data from PGSQL