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,
Sam

Does your procedure return several values or just one?

What is the database you work with?

Is it oracle sql server ETC.

Mike

SQL Server. Stored procedure returns a single value and takes several parameters as input.

Sam

1. Make sure that you are using latest version
2. Create new sql script
exec your_procedure_name some parameters
make sure taht your procedure returs 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 int 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"

Peter

I have several questions:

1) I don’t see a “check SQL” object. I’m using the latest version. Is it called something else?
2) What do you mean by “add calculation open int and enter”?
3) I understand that the output of the calculation has to be connected to the destination field, but what should the input be connected to? There is no corresponding source field – that’s the whole point of getting the value from the stored proc.

Thanks for your help.

See attached screeshots