Execute Commands Like SQL Task - POSTGRESQL

Discussion of issues, suggestions and bugs of Devart SSIS Data Flow Components, our product line for building SSIS-based ETL solutions, performing data access to popular cloud applications and databases.
Post Reply
cleitonj
Posts: 3
Joined: Mon 06 Apr 2020 18:07

Execute Commands Like SQL Task - POSTGRESQL

Post by cleitonj » Tue 09 Jun 2020 14:18

Hello, Everyone!

I would like to know if it’s possible to execute some commands with the Devart SSIS Data Flow Components for PostgreSQL, like TRUNCATE TABLE or DELETE FROM, as if it was a EXECUTE SQL TASK from Visual Studio.

I’ve tried with the PostgreSQL Source Editor with no success, and I need to perform a TRUNCATE TABLE in every beginning of the SSIS proccess. Using the destination component I can´t delete all the registers without pointing the primary key.

Could somenone help me with this, please?

Shalex
Site Admin
Posts: 8939
Joined: Thu 14 Aug 2008 12:44

Re: Execute Commands Like SQL Task - POSTGRESQL

Post by Shalex » Thu 11 Jun 2020 13:38

Destination Component is not designed for executing an arbitrary SQL statement. It processes inputs from the external data source.

Run SQL statements via Execute SQL Task.

Possible solutions:
1. Install additionally ADO.NET provider Devart dotConnect for PostgreSQL and use this ADO.NET connection in your Execute SQL Task.
2. If you want to truncate the target table, a workaround could be the following. Create a package with Devart PostgreSQL Source and Devart PostgreSQL Destination components. In Devart PostgreSQL Source, enter the following query: "select id from table_name". In Devart PostgreSQL Destination, set Action=Delete and ObjectName=table_name. This package should delete all records from table_name.

Christian P
Posts: 5
Joined: Sun 24 May 2020 14:17

Re: Execute Commands Like SQL Task - POSTGRESQL

Post by Christian P » Wed 17 Jun 2020 21:48

The source task allows you to run SQL statements (at least in the MySQL component). I am using it that way to TRUNCATE and call procedures, the last statement has to be something that returns something.

SELECT '' as workaround;

The component may not be designed for that but it works.

Post Reply