invalid dates after import - SQL Server Forums

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 2000 Forums

Import/Export (DTS) and Replication (2000)

invalid dates after import

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-09 : 21:27:17

Hi friends
another question on DTS
I am using a DTS package that imports data from foxpro table to sql server.
There is a field in a foxpro table i.e date datatype.it may contain empty data.i mean something like this //
when i import to sql server it storing it as 1899-12-30 00:00:00.000

but i want it to be either null or empty

i am wondering how to set that option in my dts package?
any ideas please
many Thanks :)

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-09 : 21:34:29

I even tried set default to null in my sqltask which creates the actual table but to no avail.

Cheers

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-09 : 21:35:13

A couple of ways around it, depending on what type of data transfer you're doing:
- if you're using a simple Copy Column transfer, you might be able to manipulate your source query (ie the one that reads the data from FP) and replacing the '//' with NULL
- if you're using an ActiveX copy task, you can use an ActiveX script to manipulate the data

HTH,

Tim

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-09 : 21:40:39

Hi Tim
I am using copy column transaformation(Transform data task) but i'm wondering where i can add code u suggested that replaces the '//' with NULL
Thanks

Cheers

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-09 : 21:46:53

In the transform data task (first step), you either specify a table name to read from, or a query that extracts the data.

I assume you're using the former method. Create a FP query that returns the data you need and use this as your source query

I'm not familiar with FP's query syntax so I can't help you in that respect.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-09 : 22:14:53

I just tried that.(actually in fp we can sql syntax)
i tried like
select PKEY ,STAT ,TREATTYPE ,ALIASCODE ,LONGNAME,
iif(empty(ENDDATE),{},enddate) as enddate ,
FLAVOURS
from acccodes

as u can see in enddate col i cannot have null values(instaed of {}) bcoz FP cannot determine datetype and gives an error.i even tried like
iif(empty(ENDDATE),'',enddate) as enddate
it does not give any error but still sql server stores date 1899-12-30 00:00:00.000
seems like i need a new sqltask that updates this field after import
what do u suggest Tim

Cheers

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-09 : 22:32:18

You shouldn't need to run an update after import.
If FP cannot give you what you need, you'll have to manipulate the data during the transform process.
Instead of using a Copy Column task for this field, use an ActiveX task. You will then have total control.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-09 : 22:36:58

ok,now i am that one right now
it shows main function like
' Copy each source column to the destination column
Function Main()
Main = DTSTransformStat_OK
End Function
can i just write update stmt like
update table set field= -blah-blah--??

Cheers

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-09 : 23:17:00

No - all you need to do is use the script to manually map the source columns over to the destination ones. Check out BOL for details.
In the field mapping tab, select the source and destination columns and select New --> ActiveX task (I haven't got the screen in front of me, so forgive me if the terms are wrong). It should then automatically create some script that maps the source date field to the destination one. All you need to do is modify that code as required

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-09 : 23:23:23

Thanks Tim Yes,BOL has some examples :-)
Thanks u very much for ur help.appreciate that

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-09 : 23:36:40

Hi Tim
I just added following script
'we have sometimes empty dates
lcisdate=isdate(DTSSource("enddate"))
if lcisdate>0 then
DTSDestination("enddate") = DTSSource("enddate")
else
DTSDestination("enddate") = Null
end if
it is working but instead storing null now it is storing 1900-01-01 00:00:00.000 !!!
am i doing anything wrong here??

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-09 : 23:42:58

Yes, i got it :-)
i just removed else condition .
i set null as default for this column.
Thanks

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources