Return a value from a SQL script

Hi

AETLE 6.3.7.18
SQL Server 2017

I'm trying to create a step in a package which runs a SQL script that gets a single value (MAX(id) from a_table) and I want to store that value in a package variable for use in a subsequent step in the package.
I've looked in the docs and searched in the forums but can't see how to achieve this.
Could you advise please?
Due to the distributed nature of the application I need to query a table on one DB server and then use that value to update a table on another DB server. If it is possible to do that in a single SQL script then that would remove the need for storing the value in a package variable but I don't think a single script could have more than 1 DB connection could it?

Thanks

You must use two separate connections to achieve your goal. We will create a working example for you

Good afternoon Sir

Since you want just one variable "SQL data check action" would be the easiest option for you
/wiki/advanced-etl-processor/packages/actions/sql-data-check-action/

Otherwise use set variable action
/wiki/advanced-etl-processor/packages/actions/set-variable-action/

Here are some screenshots for you






Please keep us posted on your progress

Hi Peter

The SQL data check worked perfectly.
Thank you for your help