datetime with six-position microsecond precision of SYSIBM.TIMESTAMP lookup with sql server datetime or datetime7 -gives datatype mismatch error -any idea

Question

datetime with six-position microsecond precision of SYSIBM.TIMESTAMP lookup with sql server datetime or datetime7 -gives datatype mismatch error -any idea

---

ilikemicrosoft

Answer 1

Please give some examples to explain your problem... and maybe post a screendump of your package...

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 2

Thank you so much at last some one responded to my post.. i have be struggling with this past 2 days...

Ok..here is the problem

We are pulling data from DB2 Database (We have permission only to SELECT Statement on DB2 database..So no way we can get the datatype or schema)

We are pulling data from DB2 Database to our Staging Area(Staging database is SQL SERVER 2008-We have complete control over Sql server 2008)

1) Am using OLE DB Source to query the DB2 Database...

2)Am doing incremntal load so i have to use lookup to compare StartTranID Column (We used Datetime,Datetime2..)of stage area with START_TRAN_ID Column of DB2 (which we dont know the schema) corresponding table columns to get the modified records

When i preview the query in OLEDB Source of DB2...The START_TRAN_ID has data in following format

START_TRAN_ID

10/12/2005 1:45:50 PM

7/30/2004 8:34:47 AM

4/20/1999 1:29:42 PM

So in the incrmental load am using Lookup dataflow with Sql server tables which has Datetime2..Note: we tried all the possible datetime datatypes'

Anyway...when i map the columns in the lookup..

i got the following error message

Cannot map the input column ,'START_TRAN_ID(DB2)' TO look up column'StartTranID(Sql server) 'because the data type do not match

hope this info will help you to guide me to resolve the issues..

Note:

But when i do History load (without lookup)...

the START_TRAN_ID of DB2 data is getting load into Sql Server

StartTranid (Sql Server)

2005-10-12 13:45:50.7155360
2004-07-30 08:38:47.1828790
1999-04-20 13:29:42.7786540

but when i do the look up i got the error message as metioned above...but what i guess is START_TRAN_ID Column in DB2 Table is of type..SYSIBM.TIMESTAMP....which is give the problem when i map with either datetime or datetime2 column in Sql Server to get  the incremental load...

Please help me out...

---

ilikemicrosoft

Answer 3

1) What is the SSIS datatype of your source (click on the green data flow path that came from your source and look at the metadata). Is it DT_DATE / DT_DBDATE / DT_DBDATE2 / DT_DBTIMESTAMP / DT_DBTIMESTAMP2 / DT_DBTIMESTAMPOFFSET / DT_STR / DT_WSTR / ETC?

2) And what is the datatype in your SQL Server table? date / datetime /datetime2?

3) Are you doing any transformations with the column between the source and destination?

Make sure that the datatypes in SSIS match the datatypes in SQL Server: http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 4

Without any conversion i mean after the oldedbsource (SELECT QUERY TO FETECH DATA FROM db2 -Source) i clicked on dataflow green line and what i have found is START_TRAN_ID-datatype is DT_DBTIMESTAMP

And it got inserted into the sql server table with exactly the same value
ex:1999-04-20 13:29:42.7786540

it has the microsecond as well  7786540

datatype of sql server colum is datetime2(7)

the above is well and good --this is called as history load it works fine

But Now for incrmental load i have to do LOOKUP with sql server table datetime2(7) for comparision

So when i try to do the mapping i got datatype mistmatch error

What am doing incremental load is the flow as follows

1)OLDEDB SOURCE -Which has select query to query from DB2 Table

2)Look up with sql server table by mapping START_TRAN_ID column of the db2 table (Available input columns) with StartTranid column of sql server table (Available lookup colmns) --this is where i got struch..i got error statting that datatype mismatch

---

ilikemicrosoft

Answer 5

You've posted this same question twice.  Posting it twice doesn't get answers faster, it just causes people to not take you seriously.

The problem here is pretty simple, now that you've provided data type information.

Your data flow (from DB2) has the date as a DT_DBTIMESTAMP.  Your SQL Server has a data type of DATETIME2, which is equivalent to DT_DBTIMESTAMP2.  DT_DBTIMESTAMP is not the same data type as DT_DBTIMESTAMP2.  That's why the Lookup component is complaining.

You need to convert the data you're requesting from DB2 to a DT_DBTIMESTAMP2, OR you need to change the SQL Server data type to DATETIME.  Either will do.

---

Talk to me now on