ODBC TUTORIAL

Connect to PostgreSQL database from SQL Server Integration Server

Updated: 31 Jul 2024

Introduction

The SQL Server Integration Service (SSIS) is an ETL (Extract Transform and Load) (Extract Transform and Load) Tool proposed by Microsoft for building enterprise-level data integration and transformation solutions.

 

It can be used for basic data integration and workflow applications but also can be used to perform broad data migration tasks. It features a data warehousing tool used for data extraction, transformation, and loading. 

 

Prerequisites

Required Softwares: 

  • Microsoft Visual Studio (version 2014 or latest) 
  • SQL Server Data Tool (SSDT) 

 

This tutorial assumes that you have ODBC drivers installed and a data source configured. If you have not yet done these steps, please follow these instructions. Ensure that Microsoft Visual Studio and ODBC Driver architecture matches. For example, if you have a 64-Bit Microsoft Visual Studio installed then you need to have the 64-Bit version of Progress DataDirect ODBC drivers installed. But most likely, your visual studio would be a 32-bit version and you would need a 32-bit driver. 

 

1. Download and install the Progress DataDirect ODBC driver using the 15-day trial software. In this tutorial, we are connecting to PostgreSQL Database.  

 

2. Configure a data source for your database. See the DataDirect documentation for help with setting up your data source. You will learn how to configure a PostgreSQL Data source in the following steps.

 

Configure Connection to PostgreSQL ODBC Driver Using the ODBC Administrator

1. After the DataDirect PostgreSQL ODBC driver has been installed, open ODBC Administrator to configure the connection and click on Add.   

 

 

2. Choose the “DataDirect X.X PostgreSQL Wire Protocol” and click on “Finish.”   

 

 

3. The ODBC PostgreSQL Wire Protocol Driver Setup window will pop up.   

  

 

  

 

Enter your credentials, click on “Apply” and then click on “Test Connect.”   

 

4. The PostgreSQL Logon screen will pop up. Provide logon credentials and click on “OK.”   

 

 

A screenshot of a computer Description automatically generated  

 

5. A dialog box will confirm that the connection is established.   

 

 

 

Configure the DataDirect ODBC PostgreSQL Driver with Microsoft Visual Studio.

1. Open Microsoft Visual Studio. You need to create a new SSIS project by going to File → New → Project shown in the below picture: - 

 

 

2. A new window will appear, inside that select Integration Service Project (If not appearing, left side of the window go to Templates → Business Intelligence → Integration Services) as shown in the below picture: - 

 

 

  • Specify a suitable project Name and choose the desired location using Browse... on the lower side of the window, as shown in the above picture. 

 

  • Must ensure SSIS Toolbox is present on the left side of the window and Solution 'Integration Service Project' is present on the right side of the window. As shown in the below picture: - 

 

 

  • If the SSIS Toolbox does not appear on the left side of the window then, Right Click on Control Flow Page and select SSIS Toolbox. As shown in the below picture:-