DI Studio -- Data Validation transformation help

[Roc]

The following instructions are from the help file for the Data Validation Transaformation. I need to see the error table results. What is not clear is #4. Does it create a table automatically or do I have to create a table for the errors records to be stored?  I tried createing an error table and enteringg that in the options tab. Did not store any errors. Anyone familiar with this?

Configure Data Validation Settings
1. You can use the properties windows for the Data Validation transformation to perform the necessary configuration to validate your data. For example, the locations of the error and exception tables are specified in the sample job. Then, status handling conditions and actions are specified. Finally, the processing of duplicate and invalid values is configured for the job. Perform the following steps to complete this configuration:

2. Open the properties window for Data Validation transformation on the Diagram tab in the Job Editor window.

3. Click the Options tab. Then, click Data Validation to access the Data Validation section of the tab.

4. Enter a name for the error table file in the form LIBREF.FILENAME in the Enter an error table name field. In the sample table, the entry is sourcelib.PROD_ERROR_TABLE. The libref (sourcelib in this case) is assigned on the SAS Application Server when SAS is started on that host. The libref points to the library that is used to store the source table.

5. Click the Status Handling tab. Then, click New to add a new data exception row to the table.

Select Email Exception Report from the Action column for the table to access the Action Options window. The exception report stores messages that describe the actions that take place when you run the job.

6. Enter the e-mail address of the error report destination in the Email Address field. Click OK to save the address.

7. Click the Duplicate Values tab. Then, click New column to display the Duplicate Values window.

8. Move the appropriate column from the Available Columns field to the Selected Columns. The sample job uses the PRODUCT_NBR column.

9. Select appropriate values in the Tables to check and Action if duplicates fields. The sample job retains the default values of Source and Move all rows to error table. These values ensure that source rows with duplicate values are moved to the error table that is specified on the Options tab. The Duplicate Values window for the sample job is shown in the following displa

[LinusH]

It seems you have an old version, corresponding to 9.3 or earlier?

So I can't reproduce this, since in 9.4 this has improved much. Error and exception tables are now handled like the correspoónding ones in Lookup transforamtion.

Check the code and the log and try to follow the logic. Perhaps an error table is not created when there are no errors....?

[Roc]

There are definately duplicates because the table with the validated data is smaller than the source table and we are on DI Studio 4.21. All I need is the errors and I cannot get them?  Is there a community for DI Studio? That is all they use.

[LinusH]

Well, the "Data Management Forum" is the proper place. If you ask, the community administrator may move this thread for you.

But as I said earlier, "they" need to dig into the code and the log to understand why the error table is not created for them, at least not in the expected location. So please get back when you have some log snippets (if you need them explained).

Assuming that the right options have been set on the Duplicate Values tab.

[Roc]

I very much appreaciaite the response, but I belive this is just a general question about SAS and the way it does error handling with Data Validation. Do I need to create a table for the errors or does SAS generate a table. The Help instructions are not clear.

[Mattmutalofomahabank]

Using SAS Data Integration Studio 4.9, I use the Data Validation transformation.  I select all columns from source table on Mapping tab.  On Duplicate Values tab I select a column and "action if duplicates:" i select "Move all but one row to error table".  On the Error and Exception Tables tab, I select Create error table and choose all columns.  I run the transformatino and it works, creating both a output table and an error table.  I can put those in an extract transformation.

However, when I try to save my job, it gives me an error message - error writing metadata.  The I/O subsystem returned error message '    ERROR: Library I0000698 does not exist.'  while attempting to dreate the event container in the Foundation repository.  I can save the job but when I reopen the job it comes up with a message that it is removing the transformation.

So do I need to define the library for the output table somewhere?

Thanks.

[Roc]

See if you can create a table for the error records then use the replace feature to have the new table replace the temp table as the error record location.