Back to Index |

Column level Write flags

Sometimes it is necessary to include columns in your output data objects, but you don't necessarily want them to be written to. The most common examples of this are:

- While using a data object for both output and input data. Maybe you want to update 2 or 3 columns of data, but while reading from the object you want to read all the columns in the table.

- While updating tables that use database specific auto-number columns. For instance, MS SQL Server databases have a feature called IDENTITY columns and MS Access Databases use auto-number columns. These databases typically do not allow these columns to be updated directly since the database handles that for you, however you still may need to include this column in your output data source for use as a key lookup column... you just don't want it to be written to.

The Column level write flags are located under the ColumnLayout property for all SQL Database type data objects (.NET Provider, MS SQL, OleDb, Odbc).

Here is a simple example. We have a table with 4 columns. The first column (PRODUCT) is a key column in MS SQL Server that happens to be an IDENTITY column. If BlueSky Integration Studio attempted to include this field in the update statement, it would error out. So, we simply un-check the write flag column. Now BlueSky Integration Studio will use the column in the WHERE clause, because it is one of the key columns, but not include it in the fields list to update.

For example, This is what one of the output SQL statement might look like:

UPDATE MyTable SET WHSE = '4550', COST = '1000', INITDATE = '01/01/1990' WHERE PRODUCT = '12345'

Multi-use data objects

Here the Product Reference Lookup table is being written to, and read from two different Join processes. In the first Join process (Update reference table) the job is updating the product lookup table based on the PRODUCT column, being the key. Here we don't want the system to actually write to the PRODUCT column, just use it for the WHERE clause in the UPDATE SQL statement.

Moreover, in the next Join process (Load Sales), the PRODUCT column will be included in the SELECT SQL statement. All fields defined in the ColumnLayout property are always included when reading the data, regardless of whether the Write flag is checked or not.

© 2003 - 2007 Relational Solutions, Inc. - All rights reserved