SSIS With PostgreSQL : Connect to PostgreSQL with SSIS Components
Follow the instructions to set up ODBC Connection manager to begin use SSIS Components with PostgreSQL database.
You can find downloading, installing, and configuring ODBC drivers instructions for PostgreSQL here
- To use PostgreSQL database in you SSIS solution you need to drag-and-drop "ODBC Source" into the data flow tab of your SSDT:
2.Then open component settings (double click on the icon) and press the "New" button.
3.If the connection manager is configured for the first time, on the Configure ODBC Connection Manager window, press the "New" button again.
4.Now you can see a Connection manager setting window. Press the "Refresh" and navigate to the dropdown near "Refresh" button.
5.Choose PostgreSQL ODBC Connection from available connections in the drop-down list. Instructions on how you can create ODBC Connection read .here
6.Congratulations, you halve successfully created the ODBC Connection manager. Select a required connection manager from drop-down list, and then select "Data accesses mode: "Table Name", then select needed table from drop-down list below. Make sure that everything is ok by opening "Preview Query Results" with "Preview" button and check results. You will see you data in the Preview window.
7.The next you would need to connect your ODBC Source with the transformation components according to your preference:
8.The final step would be to configure the ODBC Destination. Drag-and-drop ODBC Destination onto the data flow and open settings (double click on the icon). Now you can see ODBC Connection managers similar with ODBC Source. Select the required connection manager and select a destination table.
In the "Data access mode" property select "Table Name - Row by Row"
Congratulations! You have been successful in connecting your PostgreSQL databases with SSIS!