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 dateerror_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!