SSIS DB2-> SQL Server 2014 data migration issue (Timestamp -> DateTime2)

Question

we are trying to transfer data from DB2 -> SQL Server 2014 database with identical schema with column types translated like db2 Timestamp mapped to sql server Datetime2 type. When using SSIS to transfer data between tables, the timestamp -> datetime2 is failing due to data type mismatch in SSIS.

Found couple of old links related to that and they had below suggestions,

1) One suggesting to use below expression,

(SSIS-derived column) code for the transformation:

SUBSTRING(<TimestampColumn>,1,10) + " " + SUBSTRING(<TimestampColumn>,12,2) + ":" + SUBSTRING(<TimestampColumn>,15,2) + ":" + SUBSTRING(<TimestampColumn>,18,2) + "." + SUBSTRING(<TimestampColumn>,21,26)

Is this approach valid and if so is the <TimestampColumn>  in the above expression is it in DB2 Timestamp type or it is the string version of db2 TimestampColumn?

2)   another blog suggesting to add a derived column and Type cast it with (DT_DBTIMESTAMP2)[Column_Name],.

 again, in above not sure if above expects the source DB2 timestamp data in String format or will work on Timestamp type?

Please share how to handle this Timestamp -> Datetime2 mismatch in SSIS.

regards

aravias

Answer 1

Hi asethura,

The DB2's TIMESTAMP datatype allows for more fractional seconds than DATETIME2 in SQL Server or SSIS DT_DBTIMESTAMP2 so you need to make sure 1st you do not lose the last digits (if they are important).

If you you cannot, then you need to change the receiving end schema (SQL Server to possibly use VARCHAR/CHAR). And then approach #1 is the only appropriate.

Otherwise #2 is my choice.

---

Arthur

MyBlog

Twitter

Answer 2

Forgot to mention that the precision of the timestamp column type in DB2 in our schema  is set to 6  Timestamp(6) , so I assume that precision should be covered by 'Datetime2/DT_DBTIMESTAMP2'  without any truncating or loss of precision occurring upto 7 digits, is that right ?.

2) if above is right,  and if I want to try the second option, the question I have is do I have to convert the source db2 timestamp column data  to String then use this expression  (DT_DBTIMESTAMP2)[Column_Name]   where Column_Name  is the string version of the db2 timestamp column data?

3) would it be better to map Timestamp(6) in DB2 to Datetime2 or Datetime2(6) ?   we chose Datetime2   since it supports more precision (by one more decimal digit)  for any future enhancements but if there are any issues around this mapping please let me know, thanks.

Answer 3

#2 is my choice.

---

Arthur

MyBlog

Twitter