Devart Forums

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?

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.

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.