none
Import data from postgreSQL into SQL server 2005

    Question

  • 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?

     

    Wednesday, October 17, 2007 3:59 PM

All replies

  • 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.

     

    Thursday, October 18, 2007 9:38 AM
  •  

    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

    ------------------------------

     

    Thursday, October 18, 2007 9:41 AM
  • 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 import from 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

     

    Monday, November 19, 2007 10:18 AM
  • Moving to integration services, where they might have more information about how to accomplish your goals.

     

    Tuesday, November 20, 2007 7:42 PM
  • 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.

     

    Wednesday, November 21, 2007 5:22 AM
  • I could not find my way to get rid of this OUT 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.

     

    Wednesday, November 21, 2007 8:06 AM
  • 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".
    • Proposed as answer by Mandeep G Thursday, May 05, 2011 2:58 AM
    Friday, February 01, 2008 9:15 PM
  • 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

     

    Saturday, February 02, 2008 1:05 AM
  • 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

     

    Wednesday, March 05, 2008 12:11 PM
  • 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.

     

    Wednesday, March 05, 2008 2:43 PM
  •  

    We can use PostgresDirect to connect to SSIS.
    Tuesday, May 27, 2008 8:19 AM
  • 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
    Thursday, July 10, 2008 11:06 AM
  • 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.

     

     

     

     

    Thursday, July 10, 2008 1:30 PM
  • 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.
    Friday, July 11, 2008 7:50 AM
  • Can you please provide me the script which pick from Postgres to SQl server 2005 you can mail me at anil.mopada@gmail.com

     

    • Proposed as answer by LOVE2ALL Friday, January 01, 2010 10:07 AM
    Wednesday, November 05, 2008 10:10 AM
  • 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
    Friday, January 01, 2010 10:08 AM
  • How can the request for an email be proposed as an "answer" ?!

    Friday, May 27, 2011 11:51 AM