Chaps....

I am facing a problem with DTS 2005 (or actually IS 2005, DTS's "successor")
that I am really not able to resolve as of now.

I have a package that reads a flat file and writes data into a database
using an OLE DB connection.
It does some transformations using a Script Component, but nothing dramatic,
just some datetime cleansing
and type corrections.

The destination table in SQL Server has 2 fields of the type "datetime".
However, no matter which type of connection I use to access the destination
table (e.g. OLE DB as stated above), the metadata of SQL Server read by the
destination connection component shoes "DB_TIMESTAMP". I have built new
connections, rebuilt the whole DTS/IS Package, rebuilt the table in SQL
Server, rebuilt all mappings and the script, with no impact: the package
(i.e. its dataflow) fails because, as it says:

[SQL Server Destination [377]] Error: An OLE DB error has occurred. Error
code: 0x80040E07 An OLE DB record is available. Source: "Microsoft SQL
Native Client" Hresult: 0x80040E07 Description: "Error converting data
type DBTYPE_DBTIMESTAMP to datetime."

Now, when you look at the Advanced Editor in the Destination Connection
Object, you can see the "DBTYPE_DBTIMESTAMP" is the type the SQL Server
DTS/IS derives from reading the database's metadata.
Trying to edit this by hand works fine in the editor, but is overwritten
immediately by SQL Server with the original values.

The question is: Why is that? Why does the engine interpretes a "datetime"
field as a "DBTYPE_DBTIMESTAMP"? Have I overlooked something?

Just in case this is a beta issue: we are using the April CTP, i.e. SQL
Server 2005 Build 9.0.1116, and the Visual Studio DTS/IS Designer that comes
along with it (i.e. no "regular" VS.Net 2K5 Beta 5 with vb.net c# etc...).

We have successfully upgraded from Beta 2, which brought some changes to the
Script-Code for Transformations, but everything works fine except the
problem mentioned above.

Please help.

Tim

OLE DB does not have a datetime datatype it has a DBTYPE_DBTIMESTAMP which
maps to datetime

http://msdn.microsoft.com/library/de...ClassTopic.asp

That said because you are getting the right kind of conversion attempt.

Can you throw out a value from the input so I can see what does not get
mapped?

Is it a locale issue i.e. datetime would allow me to use

'13/12/2005 00:00:01'

But not if i was American.




--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Tim W." <CyberSmarty_HH (AT) newsgroup (DOT) nospam> wrote