Tuesday, March 20, 2012

Referencing the temporary Dataset in Lookup control SQL

Does anyone know how to reference the ongoing dataset within the dataflow in sql?

So here is the scenario I have.

1) OLE DB SOURCE
2) Lookup a value and that column gets added to the dataset
3) Lookup another value but this time I would rather code the sql rather than select a referencing table. How do I reference the current dataset?

Thanks,
ChestonI'm not clear on what you are trying to do.

You can use SQL in a lookup transformation. You can even build a lookup table with literal values in SQL in that lookup transformation without hitting a table.|||Let me see if I can be more clear on what I am trying to do.

Table 1 - Ole DB source control
Lookup 1 - Reference another table and based on keys add a foreign key
Lookup 2 - I wish to perform a sql statement to update the newly added foreign key from lookup 1 to a default value if it is null. How do I reference the dataset?

So in the Lookup 2 I select SQL query:
Update dataset set foreign key = 0 where foreign key is null

Then continue on with the dataset with more lookups/transformations.

Does this help?

Cheston|||Use a derived column transformation to set that column to 0 if it is null.

No comments:

Post a Comment