Sample for table synchronization and query with parameters

[cguanes]

Where can I find a sample how to do sinchronization of one table based on the updates from another table (one way only)
Also I need to run a query with parameter and I cannot find how do that in the documentation.

Here is there my scenario:

I have a server (server 1) with a table that is updated everyday, it contains timestamp.
I need to update a table in another server (server 2) with the data updated on server 1.
For this, I query in server 2 for the max timestamp, then I query in server 1 for all records newer than this timestamp
and insert/update (synchronize) the server 2 table.

I did this with MS SSIS but I have problem with the metadata (sybase stored procedure) so I want to try with Expressor.

Thanks

[hsheng]

There's a few approaches you could take. I think one approach might be to parameterize a sql query called from the read custom operator.

You would either have a 2-step dataflow or 2 separate dataflows:

1. The first dataflow could select and record the max timestamp and then write out a single varchar column containing your custom query. You can use the Read SQL operator to obtain the max timestamp from the target table and either use the transform or write custom operator to create a query string and write it out to file.
2. In the 2nd dataflow, you would use read custom operator to read in the query from the file and execute that query against the source table. The extracted data would then be inserted via write table to your target.

UJ is prototyping this out and should post shortly an approach that will work for you.

[cguanes]

I tried to do a two-step dataflow but I couldn't figure out how to pass the parament (custom operator) and I couldn't find something equivalence to the "merge-join" operator of the SSIS.

Thank you.

[jlifter]

As mentioned above, this will require either a dataflow with two steps or two single step dataflows.

In step 1 of the two step dataflow, or in the first of the two dataflows, you should run a query against the second table to extract the latest date. Use the SQL Query operator. For this operator, you can enter virtually any SELECT statement (not a stored procedure). Your statement would be something like:

SELECT MAX(timestamp_column) AS MAXDATE FROM table2_name

This will return a single value.

Follow the SQL Query operator with a Transform operator, which is followed by a Trash operator. In the Transform operator, include a function rule. The input and output parameters to this rule are the latest date. In the transform function use the utility.store_datetime function to write this single value to the database embedded within expressor.

Now in step 2 of the two step dataflow, or in the second of the two dataflows, you will use a Read Custom operator to retrieve records from table 1. See the knowledge base articles on using the dssql datascript module and calling stored procedures for guidance on how to use the dssql datascript module. Also, review the product documentation on using the Read Custom operator.

You will first need to construct the SELECT statement you want to run. Use the utility.retrieve_datetime function to extract the latest date value from the database embedded within expressor and incorporate this value into your SELECT statement. After you've built up the SELECT statement as a string, use the execute function to run this query against table 1, then use the cursor returned from execute to iterate through the result set.