data conversaion issue ssms2008 r2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

data conversaion issue ssms2008 r2

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-19 : 02:06:37

Dear all

we are using sql server 2008 r2 creaed ssis packges

every day i am executing ssis packges to load the data from oracle to
sql server (staging) database

my dataflow task is having only source and destinaions only

when i load my table i got this error
------------------------
when i executed openquery i got this error in mssql

select * from openquery(finuat,'select * from ssoadm.sso_audit_tbl')

Error converting data type DBTYPE_DBTIMESTAMP to datetime2.

[OLE DB Destination [31]] Error: There was an error with input column "EVENT_TIME" (56) on input "OLE DB Destination Input" (44). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

i have changed my table structure is event_time column as
datetime2(7)

any body know explain the logic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 15:37:59

whats the datatype you've given for field in sql? is it datetime2?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-20 : 04:29:43

My table column structure is datetime2(7)

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-20 : 05:46:12

Dear all
we have created SSIS packges for transfor the data from source to destion here i didn't use any conversation transformation

source : Oracle
destination : Sql server

i have one table in source database structure is

EVENT_ID LOGGED_IN_USER_ID EVENT_TIME
LOGIN 34845 2012-06-19 07:41:01.0000000
LOGIN 8830 2012-06-19 07:41:02.0000000

my destion table structure is
EVENT_ID nvarchar(16)
LOGGED_IN_USER_ID nvarchar(15)
EVENT_TIME datetime2(7)

where ever i am executin the package like

i got this error
column "EVENT_TIME" (493) on input "OLE DB Destination Input" (470). The column status returned was: "Conversion failed because the data value overflowed the specified type.

case 2:
when i fetch the data this format
select EVENT_ID,LOGGED_IN_USER_ID,CAST(EVENT_TIME AS VARCHAR(30)) ,CLIENT_MACHINE,
AUDIT_DATA from ssoadm.sso_audit_tbl

i received this type of error

There was an error with input column
"CAST(EVENT_TIMEASVARCHAR(30))" (58) on input "OLE DB Destination Input" (44). The column status returned was: "The value could not be converted because of a potential loss of data.".

how to load a table smoothly from source to destination

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 21:43:28

why are you trying to fetch date as varchar. keep it as date/datetime datatype itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-21 : 00:51:36

intially i have tried loading datetime2(7) format.

When i was fetching datetime2(7) format i got the data conversation overflown error type issue is getting

so exactly which type of conversation is required

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 01:26:01

can you try explicitly casting it to date datatype inside SSIS using derived column or data conversion task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-21 : 01:52:49

i have tried this way

source :oracle

transformation : Data conversation

destination :sql server

i have used datetimestamp2 transformation in ssis package

but i am facing the same issue

which is suitable conversaion of this format
2012-06-19 07:41:02.0000000 in data conversation transformation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 01:54:30

do you need timepart also or just datepart alone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-21 : 02:08:10

like this format is sufficient
2012-06-19 07:41:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources