| Author |
Topic |
|
qman
Constraint Violating Yak Guru
442 Posts |
 Posted - 2010-08-26 : 14:59:19
|
| I get the following error when trying to map a DB2 TimeStamp Column to a SQL Server TimeStamp Column via SSIS.The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_DBTIMESTAMP" and "DT_BYTES"I can't use datetime datatype in SQL, it must remain as a TimeStamp.Any idea what can be done for this?Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-26 : 15:05:29
|
TIMESTAMP in SQL Server has nothing to do with DATE and TIME.It is a kind of rowversion. You can't convert anything to TIMESTAMP and it makes no sense.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2010-08-27 : 08:27:30
|
| Thanks, that's good to know....Here is my issue then. I have a DB2 table that contains a column called CREATE_TS that uses a timestamp data type.My SSIS reads this table and copies all records into a SQL Server table. The SQL CREATE_TS column now uses a datetime data type.The CREATE_TS SQL column is my primary key. The problem now is that the dates that came into SQL get rounded, you also loose some of trailing nanoseconds past the decimal point. As a result, I get primary key errors, which states that you can't have duplicate records. Is there something in SSIS I can do to fix this? I want the data imported into SQL to match the data from DB2.Sample DB2 Date using TimeStamp: 2010-08-25 09:14:11.364798Sample SQL Date using DateTime: 2010-08-25 09:14:11.363 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-28 : 02:42:56
|
| you can add a rownumber transformation in ssis which adds a rownumber to row of data and make it primary key in SQL. or change primary key to include some other column in your table provided you can ensure composite group value is unique------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-08-28 : 02:51:20
|
| You can upgrade to SQL Server 2008 and use the datetime2 data type - which has the precision you are looking for. If that is not an option, then you have to create a surrogate key (identity - probably) and put your DB2 timestamp into a character data type (e.g. char(26)).Using a character data type is going to be problematic, because you won't be able to use the date functions with it without having to convert. And, the conversion is going to round/truncate the values.Jeff |
|
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2010-08-30 : 07:56:32
|
| I wish we could go to 2008, currently we are forced to remain on 2005.I think my only other option is to create the key using multiple columns. Or create my own unique column using rownumber.ThanksQmaan |
|
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2010-08-30 : 09:02:30
|
| I would like to I include a rownumber for each source table row. Then take this rownumber, and pass it to a custom SQL Column called row_number.I cant figure out how to do this within SSIS.In case it helps, I have a record count global variable within SSIS.Is there a way to pass this variable each time to SQL Server depending on what row you are on? |
|
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2010-08-30 : 09:07:37
|
| Forget my last statement, I simply attached a Identity to the destination row_number column. I am going to use this column as my primary key. Worked for me.... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-30 : 09:57:03
|
| ok. glad that you sorted it out------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
TimeStamp/DateTime error.