Converting DB2 Timestamp Data to SQL Server 2005 - Problems with Unique Index

I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.

The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.

Most all the times entered into this column are a from an automated process so they are really close together timewise.

Here is what I have come up with so far:

1. Fast Load OLEDB with a batch of 10,000 records at a time

2. On the fail of the batch redirect rows to a regular table load OLEDB insert task

3. On the fail of the single insert redirect rows to script that ups the seconds one tick.

4. Attempt one last insert of the modified rows

5. If fail, then store the record off to a delimited text file

I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.

Any help would be appreciated.

Redirecting errors from the FastLoad to a regular OLEDB destination is a technique that I've used before to capture only the row that caused the error - that should work fine.

I have another question, though. If you can change this timestamp value (as you are planning in step 3), it doesn't seem to be all that significant. Why not drop just add an identity column to the unique index?

|||

Adding an Identity column to the table and then adding into the index sounds like a good idea to get around the problem. Especially since the package is going to be scheduled to run every 15 to 30 minutes to sync the DB2 table to the SQL Server table during the migration time frame.

Thanks for the suggestion. I'll let you know what we have decided to do.

|||

Upon further inspection of this process and doing a small test this method is not an option for me.

The addition of an indentity column to the unqiue index would now in essence make the index non-unique. The identity column is a unique value and would then allow for the business rule of this date time column to be broken.

I am attempting to try and see if I can capture the data column as a string for manipulation, but right now the OleDB connection is pulling the catalog info from DB2 and knows that the column is supposed to be a date time. Any attempt to use the column in the SSIS package as a character I get errors.

Still looking for suggestions before starting down the path of writing an application or off the shelf tool to handle the sync between these tables.

Thanks.

|||

You should be able to use a derived column to convert the value to a string - that is probably your best bet to avoid violating the business rule.

Code Snippet

(DT_WSTR, 10) [ColumnName]