none
Azure Database for PostgreSQL

    Question

  • Here is a use case that I currently have.  

    I am developing using SQL Server / SSDT / Visual Studio 2017 to build SSIS packages that will be eventually deployed 
    to SSISDB on AZURE  SQL Server thus enabling us deploy the packages to Integration Services catalog and execute under SSIS IR.   

    It works flawlessly for many SSIS packages.


    A current project is to write a simple data transfer mechanism  to move data from a 
    "Azure Database for PostgreSQL server" to a "Azure SQL Database" both exist under same subscription.

    There are multiple ways to do this

    a)  use Azure Data Factory "Copy Data" feature.  However, this would be outside the workflow in the SSIS package I am building using Visual Studio.
        The copy from "Azure Database for PostgreSQL server" to a "Azure SQL Database" should happen as part of workflow.

    b)  the second way is to create a DSN on a local machine and use ADO.NET in SSIS to connect.  
    However, I am not sure if SSIS package when deployed to SSISDB on Azure would work because there is no DSN I can create in azure space.
        Is there anyway to to this ?  If a VM with SQL Server is used that might be a potential solution but 
    please note we are NOT using a VM on Azure cause of cost considerations.  

    c)      A VM in AZURE could potentially allow me to install Postgres and use pg_dump/pg_restore .. I havent tried this.  But again will require a VM
    https://docs.microsoft.com/en-us/azure/postgresql/howto-migrate-using-dump-and-restore

    d)     A fourth way would be to use .Net/C# to write custom controls... We haven't tried this.

    e) Use a third party control.. But the problem remains that the package will be deployed to SSISDB in Azure so it might not work.

    f)     Since microsoft embraced postgressql opensource in Azure platform, I'd have expected them to support it in SSIS or atleast in 
    Azure-Featurepack for Integration Services.
    https://docs.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis?view=sql-server-2017
    However, I don't see any dataflow component to connect to "Azure Database for PostgreSQL server" as a source.  
    Hopefully Microsoft can add it soon.

       
    Has anyone faced this issue of migrating data from "Azure Database for PostgreSQL server" to a "Azure SQL Database" in an SSIS package built in visual Studio  ?  
    If so is there a simple straightforward way ?

    Wednesday, July 11, 2018 1:25 PM

All replies

  • For b), c), and e), you can create DSN and install command-line utilities/PostgreSQL drivers/3rd party components on your Azure-SSIS IR via custom setup: https://docs.microsoft.com/en-us/azure/data-factory/how-to-configure-azure-ssis-ir-custom-setup.  For d), you can use SSIS Script Task.
    Wednesday, July 11, 2018 4:27 PM