import .txt file with 00/00/0000 dates causing error

Question

import .txt file with 00/00/0000 dates causing error

Paul Marino

SSC Veteran

Points: 233
More actions
July 16, 2004 at 8:19 am

#65674

I have created a dts package to import a .txt file every night. The package drops the table then creates a new one before it imports the data. I used the datetime datatype for the date field (not sure if it should be smalldatetime).
I have a couple of issues with the dates which are formatted like this "mm/dd/yyyy". the default value is 00/00/0000.
I changed the transformation of the date field from "copy column" to "date time string". I think this should work but I'm not sure because the 00/00/0000 dates are causing an error, when I execute the package.
I would appreciate any info that might help me. Thanks
Paul

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

Not sure that's a valid date. I typically use 1/1/1900 or some other trigger date to mark invalid dates. Can you search and replace in the text file?

Paul Marino SSC Veteran Points: 233 More actions · Answer 2

Thanks for your suggestion. I think that would work but I have a system that is going to create a new .txt file with 00/00/0000 dates every night. I'm trying to schedule the import package every night after the new .txt file is created.

Is there some way I can have the transformation skip over the 00/00/0000 or enter a null value in it's place?

Thanks for you help

Paul

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 3

Hi

Import the text file into a staging table ( with the datecolumn char(10) ).

Then use sql to scrub the data.

1. truncate stageTable

2. Source -> stageTable

3. insert realTable(,,,datecolumn,,,)

select ,,,case isdate(datecolumn) when 1 then datecolumn else null end,,, from stageTable

/rockmoose

You must unlearn what You have learnt

Steven. SSCrazy Eights Points: 9806 More actions · Answer 4

In the past I have used an active x transformation (ie vbs)to modify the date format ie change the / into a -

Steven

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 5

Yeah,

But

select convert(datetime,'00/00/0000')

is just as invalid as

select convert(datetime,'00-00-0000')

You could use vbs to convert '00/00/0000' to '01/01/1753' ( or what you prefer )

to make it a valid date.

or vbs to convert '00/00/0000' to NULL value.

vbs:ers how do you do this ?

/rockmoose

You must unlearn what You have learnt

Paul Marino SSC Veteran Points: 233 More actions · Answer 6

I tried to do it as an activeX script as part of the transformation but I couldn't get it to work.

I did get it to work with a staging table, and I'm very happy about that.

My vbs code that did not work looked like this (any idea why?)

Function Main()

if isDate(DTSSource("ReservationDate")) then

DTSDestination("ReservationDate") = DTSSource("ReservationDate")

end if

Main = DTSTransformStat_OK

End Function

Thanks

Paul

Steven. SSCrazy Eights Points: 9806 More actions · Answer 7

If I remember correctly there is a test button, which runs your code against the first 200 lines of data. Using that is a good way of testing your code

Also you will need to add an else into your code, ie what to do when its not a date

Steven

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 8

Glad it worked out for You.

For data manipulation / cleaning I always use SQL.

In DTS, if I use it, I put as little business logic as possible, if any.

The staging table approach is very efficient and fault proof.

*sql/bulkinsert/dts/bcp **SQL

DirtySource ->* StageTable --cleanvalidatelogerrors-->** ProductionTable

/rockmoose

You must unlearn what You have learnt

Paul Marino SSC Veteran Points: 233 More actions · Answer 9

I'm getting an error with one of the fields that that I am bringing over from the stage table to the destination table.

the stage table has a data type of varchar and the destination table has a datatype of money for this field. The error indicates that I need to convert so I put the following in the select part of my sql statement that moves the data from stage to destination.

convert(money ,[LoanAmt] ) ,

Thanks for any input about why this is not working

Thanks

Paul

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 10

The first thing that springs to mind is that the varchar field contains data that cannot be converted to a numeric value ( or money ).

select * from stageTable where isnumeric([LoanAmt]) = 0

/rockmoose

You must unlearn what You have learnt

Paul Marino SSC Veteran Points: 233 More actions · Answer 11

Right again.

What started out to be a very frustating day is turning into a bright weekend.

I didn't realize there was so much to SQL. Did you take a class or is there a particularly good book that you used to get up to speed.

Thanks for your help

Have a great weekend.

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 12

Hi,

Actually the only book I have read (front to back) is

C.J. Date "An Introduction to Database Systems"

This will give a good theoretical understanding of databases and the relational model.

Apart from that I recommend BOL ( A very good documentation of Microsofts implementation of a database system ).

The web has some good sites ( this one, http://www.sqlteam.ccom, http://www.sqlsecurity.com and more )

ALso I am sure there are many good books and courses on T-SQL out there.

Unfortunately I cannot recommend one off hand, ask this forum and You will surely get replies 🙂

You to have a great weekend.

/rockmoose

You must unlearn what You have learnt