question

Hello, I'm trying to figure out a solution on this as a proof of concept. I have a table called "test_table_with_errors" with 4 entries, 3 of which have invalid dates as nvarchars. Then I have another table called "test_table_after_error_removal" which requires the date to be valid and a third table called "error_log_table" (which I want to be a generic error table) which I want to write the recid of the record from "test_table_with_errors" and the table name of "test_table_with_errors" into "error_log_table".

I can get SSIS to write the recid into "error_log_table", but I can seem to insert the table name.

Here is my table creation code:

CREATE TABLE test_table_with_errors
(
    recid INT NOT NULL,
    add_date NVARCHAR(20) NOT NULL,
    descr NVARCHAR(50) NOT null
);

INSERT INTO test_table_with_errors (recid, add_date, descr)VALUES(1, '12-40-2010', 'invalid date');
INSERT INTO test_table_with_errors (recid, add_date, descr)VALUES(2, '12-23-2010', 'valid date');
INSERT INTO test_table_with_errors (recid, add_date, descr)VALUES(3, '99-99-2010', 'invalid date');
INSERT INTO test_table_with_errors (recid, add_date, descr)VALUES(4, '0', 'invalid date');

CREATE TABLE error_log_table
(
    recid INT NOT NULL,
    table_name NVARCHAR(20)
);

CREATE TABLE test_table_after_error_removal
(
    recid INT NOT NULL,
    add_date date NOT NULL,
    descr NVARCHAR(50) NOT NULL
);

In my ssis I have:

In a Dataflow: OLE DB Source: test_table_with_errors --> OLE DB Destination: test_table_after_error_removal -- (OLE DB Desination Error Output) --> OLE DB Destination: Error_Log_Table.

Problem I run into is that I can't add a column or a hard value to be inserted into the table name column of Error Log Table.

My results are: SELECT * FROM test_table_with_errors; SELECT * FROM test_table_after_error_removal; SELECT * FROM error_log_table;

test_table_with_errors:

recid add_date descr
1 12-40-2010 invalid date
2 12-23-2010 valid date
3 99-99-2010 invalid date
4 0 invalid date

test_table-after_error_removal:

recid add_date descr
2 2010-12-23 valid date

error_lob_table:
recid table_name
1 NULL 3 NULL 4 NULL

when I want the error log table to look like:

recid table_name
1 test_table_with_errors
3 test_table_with_errors
4 test_table_with_errors

Thanks for your help ahead of time!

Quinton quinton.quast@progressive-medical.com