I am trying to import one table from postgreSQL to SQL Server 2005 using sql server import and export wizard. When i test the connection after providing data source, location, username, password in the Data Link Properties section I get the message "Test Connection Succeeded". As soon as i press next to go onto next step i get the following error. TITLE: SQL Server Import and Export Wizard------------------------------ Cannot get string literals from the database connection "Provider=PostgreSQL.1;User ID=sa;Data Source=localhost;Location=TestMasterMap;Extended Properties=". ------------------------------ADDITIONAL INFORMATION: Object reference not set to an instance of an object. (DTSWizard) ------------------------------BUTTONS: OK------------------------------ I have tried all sorts of different combinations for these properties but it always fails on this step. Can anybody help me with this?

To help someone who might be trying to achieve similar goal as mine. Instead of selecting the PostgreSQL OLE DB Provider in the data source drop down menu of SQL Server Import and Export Wizard, select .Net Framework Data Provider for Odbc Then you have to make a DSN and provide a ConnectionString. Following ConnectionString worked for me Driver={PostgreSQL};Server=localhost;Port=5432;Database=TestMasterMap;Uid=postgres;Pwd=; To make a DSN you have to go into Administrative Tools Data Sources (ODBC) and create a user DSN. Once this is done you can supply the DSN name in the DSN text box of SQL Server Import and Export Wizard.

The settings above worked for a small table with 3 rows. Now when I try to import the bigger table which I wanted to import in the first instance I get following out of memory error. Anybody knows how to work around this. I have checked my task manager and more than half of my memory was still not used when I get this error. TITLE: SQL Server Import and Export Wizard ------------------------------ Column information for the source and destination data could not be retrieved. "Query" -> [temporary].[dbo].[exporttable]: - ERROR [HY000] Out of memory while reading tuples.; No query has been executed with that handle ------------------------------ ADDITIONAL INFORMATION: ERROR [HY000] Out of memory while reading tuples.; No query has been executed with that handle (PSQLODBC.DLL) ------------------------------ BUTTONS: OK ------------------------------

I tried the same way and got a table with 5 columns and about 3,000,000 rows imported. Bigger tables will result the out of memory error, yes, but it still works to importfrom the first row to the 3,000,000th row, then from 3,000,000 to 6,000,000 etc. It is pretty troublesome, so I hope to know if there is any easier way. Also this method only imports the pure data for me and I have to set the PK afterwards, is there any import setting to keep the primary key info? thanks

Moving to integration services, where they might have more information about how to accomplish your goals.

This sounds more like a driver issue to me than an SSIS issue. Have you tested the ODBC driver from another client to see if it has the same row count limitation? On the primary key issue, I don't believe the Import/Export wizard will replicate primary keys (which are schema, not data) unless you are moving between SQL Servers. If you open the packages in BIDS, you can add logic to set the primary keys using Execute SQL Tasks.

I could not find my wayto get rid of thisOUT OF MEMORY issue and had to write a small application which picked up million rows in one go from postgre and write them into sql server. I am not sure if the problem lies in Postgre or Sql server.

Hi, I had the same problem with my postgreSQL database. Have you tried enabling the "use declare/fetch" option for your psqlODBC driver? You can do this by opening your data sources, press "Configure", then in the opened data source details, in the "Options" section select "Datasource" and in the opened window check the "use declare/fetch". I had a 1.7 mio table with the same problem, afterwards it imported all the rows at once without "out of memory while reading tuples".

If you are interested here is a native .NET provider for PostgreSQL, I have seen many .NET developers use PostgreSQL as the database without issues. http://pgfoundry.org/projects/npgsql

i would like to use this but i cant find any instructions on installing it. 1) wha directory do we put the .dll in? 2) from where do you run the install command from? Thanks

There is a forum for the project in that link if you read a few postings you will see installation info and everything you need to use it. You put it in your bin directory but do some reading before you install.

We can use PostgresDirect to connect to SSIS.

SSIS I have installed Greenplum ODBC driver to connect to postgresql. While I was trying to retrieve count (*) from test_schema following error is thrown Test_schema does not exist. And on retrieving data from test_schema2 following error is thrown ERROR: permission denied for schema test_schema2. How to go about it? ADO.Net connection manager--ODBC provider is used in SSIS Connection String :-- Driver={PostgreSQL UNICODE};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword; Can i specify the schema name in the connection string? Or is it fine if i mention schemaname.TableName in the query? Thanks

First you have to find Postgres docs to see if schema and Count(*) is implemented and if yes how and your connection string is not the correct one for the provider I posted. I think you need to start a need thread because I don't think ODBC should be used for .NET development.

My guess is that the "out of memory" problem is mostly the problem at PostgreSQL side. I've also met with some memory problem with PostgreSQL when running queries with large result set. It looks like PostgreSQL buffer results in memory. When it runs out of memory before it finishes processing the whole query, it will generate the out of memory error. Although there are several things you can try to configure PostgreSQL, the easiest way is to separate your query into smaller ones or to stream the result set (e.g. using a cursor) although this will be much slower.

Can you please provide me the script which pick from Postgres to SQl server 2005 you can mail me at anil.mopada@gmail.com

I am Facing Same Problem. if u have any solution so please share with me,which pick from Postgres to SQl server 2005.ESF tool is best but there are some issues in it. qaiser.shabbir@gmail.com

How can the request for an email be proposed as an "answer" ?!