This is an easy fix; you can run your SSIS package in 32 bit mode. In Visual Studio go to the Project drop down and in Properties set "Run64BitRuntime" in the Debugging area to False.
My final issue was a datatype mismatch. As it turns out the string datatypes from Postgres were Unicode so I changed my varchar columns to nvarchar and that resolved the issue. Voila!
Test the connection and you should now have a successful connection! Now, I did run into two additional issues. The first error I got when trying to run the package indicated a platform mismatch. It turns out my Postgres instance was running 32 bit and wasn't playing well with my 64 bit SSIS package. You can find the details of your Postgres instance in pgAdmin by clicking the server name and looking in the Properties window.
You'll then need to build a connection string and fill out the username and password as shown below.
For the Data source specification choose PostgreSQL35W. If this is not visible then your driver did not install correctly.
Second, choose .Net Providers\SqlClient Data Provider for the Provider, and Obdc Data Provider under .Net Providers.
Yes, I know the text datatype is depreciated but this is simply a staging table that will be dropped once the data is prettied up and given a new home. You'll also notice that I ignored the fact that the id column is an identity. This is again because I'm doing a raw data dump and there won't be additional records added.
One last step is needed before we can connect to the Postgres database; you'll need to install a PostgreSQL driver. Thanks to an invaluable blog post by David Van De Sompele this was fairly easy. Download the most recent .msi file from here and run it on the SQL server. In the OBDC Administrator Utility I chose to create a Unicode System DSN in the "System DSN" tab. After adding in database/server/login click the Datasource button to implement a great tidbit from David's blog (I quote): " ... make sure the Use Declare/Fetch box is checked. The driver will fail to retrieve larger datasets if you do not check this box. I have not yet found a satisfactory answer for why this is so."
Now we're ready to fire up SSIS. I'm using Visual Studio 2010 on a Windows7 64 bit workstation and my SQL Server is also running 64 bit (when in doubt run SELECT @@VERSION in a SSMS window). Right click in the Connection Managers pane and choose New ADO.NET Connection.
We have an application that is close to end of life and I was tasked with finding a way to grab the data out of it to import to another system. After looking at the millions of records involved I decided the quick and easy way of scripting out insert statements wasn't going to work, so I decided to use SSIS. Easy, right?
This was my first venture into Postgres and while I can use command line to dig in, I preferred to download and use pgAdmin III, a GUI for PGSQL databases.
Now I needed to grab the schema so I could create a clone on my SQL 2012 dev box. Google to the rescue: open pgAdmin > connect to the Postgres server > right click database name > Backup. A window with several tabs will pop up. On the File Options tab designate the desired path to your output file in the Filename attribute and choose "Plain" for Format; leave everything else default. In the Dump Options #1 tab choose "Only Schema" under Type of Objects. The Dump Options #2 tab is really your prerogative; after a few trial runs I unchecked Verbose Messages. Last, choose the objects to be scripted in the Objects tab and click Backup.
This file will not be immediately usable. You'll now have to drop it into SSMS and massage it a bit. Postgres datatypes are not congruent with MSSQL so some research is involved in translating; that's really an individual preference based on how the data will be used in the end. Below is an example: