Hi All,

I get following error when I use DTS to copy data from DB2 to SQL server,
DB2's field type is TIME and SQL Server's field type was automatically map
with
smalldatetime, but some tables that have same mapping don't have such
problem.
---------------------------
Copy Data from TIMES to [TEST].[dbo].[TIMES] Step
---------------------------
Error during Transformation 'DirectCopyXform' for Row number 1. Errors
encountered so far in this task: 1.

TransformCopy 'DirectCopyXform' conversion error: General conversion
failure on column pair 4 (source column 'DURATION' (DBTYPE_DBTIME),
destination column 'DURATION' (DBTYPE_DBTIMESTAMP)).

The SQL Server 200 has been installed SP3

Does anyone have any suggestions?
Thanks in advance,

Herbert

I do not know what are the boundaries of TIME on DB2 but it is my guess that
they break the limitations of SMALLDATETIME in SQL Server. Hve a look at
those restrictions for SQL Server here

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_da-db_9xut.htm

If the boundaries are the same then you are probably presenting the data to
SQL Server in a format that it interprets incorrectly.



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Herbert Mo" <hnmo (AT) stryon (DOT) com> wrote

Hi Allan,

You are right, it is due to 24:00:00 cannot be known in SQL Server's
smalldatetime, do you have a good idea to change it to 00:00:00?

Thanks,
Herbert

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

If LEFT(DURATION, 2) = "24" Then
DTSDestination("DURATION") = "00:00:00"
Else
DTSDestination("DURATION") = DTSSource("DURATION")
End If


In your left function you have not said DTSSource("Duration")
Also you will need to reconstruct the string so say it looks like this

12/12/2003 24:00:00

You need to rebuild it into

12/12/2003 00:00:00

Also have a look at

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)



remember SQL Server if you do not specify a time will sub in midnight for
you.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Herbert Mo" <hnmo (AT) stryon (DOT) com> wrote