Error b/c blank row in txt file for datetime column in database

[Jeet2004]

I have a text file in following format
EmployeeID EmploymentDate
1 3/12/2004
1 12/12/2004
3
9 1/12/2003

now the problem i am facing in the dts package is it gives error if i use direct copy column in the transformation.
I tried following things
1. Used Activex script transformation as follows
If IsNull(DTSSource("Col007").value) then
DTSDestination("termination_date") =""
ElseIf DTSSource("Col007").value="" then
DTSDestination("termination_date") = ""
Else
DTSDestination("termination_date") = Trim(DTSSource("Col007"))
End If
Main = DTSTransformStat_OK

This results in all the rows inserted as <NULL> for that column

2. Used Activex script transformation as follows

DTSDestination("termination_date") = Trim(DTSSource("Col007"))
Main = DTSTransformStat_OK

same result as above all the rows nulll

now i dont understand why its doing that or whats a good way to design the package.

Any help would be highly appreciated.
Thanks.

 

[mrdenny]

Load the data into a tempory table, then massage the data as needed, then load the finalised data into your production table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Jeet2004]

For the sake of example i had used a simple two column thingi Actually its 15 columns with atleast 5000 rows
Dont know whether it woudl be a good idead to transfer them to temporary table and than massage them???

Also cant the same be done in Active x Scripts?
Thanks

 

[mrdenny]

That's no problem to put into temporary tables. I've put millions of rows into temporary tables.

It's best to do as much as possible directly in T/SQL as this is SQL Servers native language. It's going to be more CPU intensive to process through Active X than to write the update statement on a temporary table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Jeet2004]

ohh ok
I have not worked with so many rows so was concerned
Thanks for your help.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)