ETL and Data Integration tools - Modern ETL software for everyone

Hello,

I’m evaluating your Advanced ETL Processor Professional product, and I’m trying to set up what seems to be a simple ETL operation but I can’t figure out how to do it.

I need to take records from a SQL view to populate a SQL table. In addition, I need to run a stored procedure one time to get a single “Run ID” value. I will use the “Run ID” value to populate an additional column for each destination record. In other words:

Source:

View column A
View column B
View column C
<results of “get run ID” stored procedure>

Destination:
Table column A
Table column B
Table column C
Table column “Run ID”

The logical way to accomplish this from my perspective would be to run the stored procedure first, save the result in a package variable, and then run the ETL, specifying the variable value as the column value in the destination. However, I’m not sure how to implement this using your product.

Any help you can provide is appreciated.

Thanks,
Dave

It is not obvious but possible

1. Make sure that you are using latest version
2. create new sql script
exec your_procedure_name some parameters
make sure that your procedure returns a record set
EG: select @row_id row_id at the end

3 create new package
add check sql object point it to the script you have just created
add transformation an connect both ends of check sql to the transformation
4 Open you transformation
open transformer object
add calculation open it and enter
(I assume that you are trying to get next value for the primary key)

Var I : Integer;
s: string;
begin
I:=1;
//Getting starting point
S:=GetPackageVariable('<Check Sql Value>');
if s='' then
SetPackageVariable('<Check Sql Value>',I)
else
begin
I:=GetPackageVariable('<Check Sql Value>');
I:=I+1;
SetPackageVariable('<Check Sql Value>',I);
end;
Result:=I;
end;

Or just use

begin
Result:=GetPackageVariable('<Check Sql Value>');
end;

close calculation
make sure that both ends of the calculation connected to objects

5 run the package

There is a working example in the latest version called "Passing Variables"

Latest version is 4.2.0.8

Please let us know the outcome
Mike

Works like a dream

Thank you for your help

David