Cómo hacer un log de los registros que no me aceptó la carga?

20/09/2003 - 19:29 por angel | Informe spam
Estoy haciendo cargas vía DTS de archivos de texto a
tablas SQL. Cómo puedo hacer para que me quede asentado
que registros NO me aceptó (por repetición de clave
primaria, por tipo de dato incorrecto, etc.).
O se pararía el proceso ante el primer problema? Si es
que se para, Cómo puedo hacer para que no se pare y que
me registre en algún lugar los registros que no se
pudieron cargar?
 

Leer las respuestas

#1 Gustavo Larriera [MVP]
21/09/2003 - 18:13 | Informe spam
Puedes configurar archivos de manejo de excepciones. El siguiente es un
extracto de los Books Online, en el artículo "Tasks That Transform Data" (en
INGLES)

Detecting Row-Level Errors
Tasks that transform data use exception files to record information about
failed rows. Exception files can contain:

a.. Package information, such as package name, description, and version.


b.. Step execution information, including the name of the package step
associated with the data pump operation and step execution times.


c.. Error information, including the source of the error (for example, the
data pump or a connection) and a description of the error (for example, an
insert error that occurred on EmployeeData column, row 2007).
The tasks that transform data are able to detect row-level errors before the
row is submitted to the database. For example, suppose an input row contains
missing or incorrectly formatted data. When these tasks encounter such a
row, they fail the row and do not pass it to the destination. This error
counts as one failure toward the maximum error count.

Some errors, such as duplicate keys or referential integrity violations,
cannot be detected at row level by these tasks. Such rows fail only after
being passed to the destination. The failure is noted in the exception file,
but the actual rows that failed are not logged. Thus, complete error
information is not always available in the exception logs.

If you configure an exception log for a task that transforms data, step
execution information is appended to the exception file you specify each
time the package is run. If you specify an exception file that does not
currently exist, the file will be created at package execution time. If the
step associated with the task does not run, no exception file data is
generated.

You also can create additional log files to capture source and destination
rows that failed when a task that transforms data is executing by using the
Microsoft SQL ServerT 2000 exception file options. You can use these files
to examine failed rows and troubleshoot problems with the data. The source
row and destination row log files have the same name as the exception file,
but with the extensions ".Source" and ".Destination", respectively, appended
to the name. These files are only created if source row errors or
destination row errors occur during execution of the transformation task.


To configure the data pump exception files

1.. On the Data Transformation Services (DTS) design sheet, double-click a
Transform Data task or Data Driven Query task.
You must have your source and destination connections defined before
configuring a Transform Data task.

2.. Click the Options tab.


3.. Under Exception file, in the Name box, type a file path for the text
file you want to use as an exception file, or click the browse (...) button
to locate the file.
If you enter a file that does not exist, the file will be created when the
step associated with this transformation task is run.

4.. Optionally, if you want to use the Microsoft® SQL ServerT 2000 data
pump exception file options, then under File type, clear the 7.0 format
check box and select the exception files you want to generate.


5.. Under File format, select any additional options for the exception
file.

Gustavo Larriera, MSFT MVP-SQL
Uruguay LatAm

***IMPORTANTE*** Microsoft Security Bulletin MS03-039
http://www.microsoft.com/security/s...03-039.asp

This message is provided "AS IS" with no warranties expressed or implied,
and confers no rights.


"angel" wrote in message
news:00a401c37f9c$c33b1240$
Estoy haciendo cargas vía DTS de archivos de texto a
tablas SQL. Cómo puedo hacer para que me quede asentado
que registros NO me aceptó (por repetición de clave
primaria, por tipo de dato incorrecto, etc.).
O se pararía el proceso ante el primer problema? Si es
que se para, Cómo puedo hacer para que no se pare y que
me registre en algún lugar los registros que no se
pudieron cargar?

Preguntas similares