dbTalk Databases Forums  

RE: Problem with year less than 1900 in DTS Import

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.dts RE: Problem with year less than 1900 in DTS Import

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts


Discuss RE: Problem with year less than 1900 in DTS Import in the microsoft.public.sqlserver.dts forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
RLoski
 
Posts: n/a

Default RE: Problem with year less than 1900 in DTS Import - 12-22-2006 , 10:14 PM





The "Year" function returns an integer. You are doing a string compare. The
string "5" is greater than the string "1951" (5 is after 1).

Try:

if Year(DTSSource("Column")) < 1951 then

With this code it will do an integer compare: 5 is less than 1951.
--
Russel Loski, MCSD.Net


"SamK" wrote:

Quote:
I am basically a report writer and not a database developer.

I am importing data from Progress database to SQL Server 2000. In Progress
some of the tables have a bad data particularly the date columns. They have
the dates where the year is < 1900 (something like 0005). And this database
is at a different location from where we are located. I believe the SQL
server is having problem converting these dates.

I am using DTS wizard to import the data and transform. Modified the Visual
Basic Transformation Script for rectifying the bad data column like

If Year(DTSSource("Column")) < "1951" then
DTSDestination("Column") = "01/01/1951"
Else
DTSDestination("Column") = DTSSource("Column")
End If

Still it is erroring out.
Here is the error message:
Error at Destination for Row number 177704. Errors encountered so far in
this task: 1.
Insert error,column 10('column name',DBTYPE_DBTIMESTAMP), status 6: Data
overflow. Invalid cahracter value for cast specification.

I would appreciate your suggestions on this problem.

Thanks,
SamK

Reply With Quote
  #2  
Old   
SamK
 
Posts: n/a

Default RE: Problem with year less than 1900 in DTS Import - 12-23-2006 , 12:20 PM





Hi Russ,

Modified the code as suggested. It could process the Row number 177704 where
the value is '0205-05-17'. However errored out again at the Row number 184842
where the value is '0005-02-02'

This is the error message:
Error during Transformation 'AxScriptXform' for Row
number 184842. Errors encountered so far in this task: 1.
ActiveX Scripting Transform 'AxScriptXform' encountered
an invalid data value for 'um-da050' source column.

I would appreciate your suggestions.

Best Regards
SamK


"RLoski" wrote:

Quote:
The "Year" function returns an integer. You are doing a string compare. The
string "5" is greater than the string "1951" (5 is after 1).

Try:

if Year(DTSSource("Column")) < 1951 then

With this code it will do an integer compare: 5 is less than 1951.
--
Russel Loski, MCSD.Net


"SamK" wrote:

I am basically a report writer and not a database developer.

I am importing data from Progress database to SQL Server 2000. In Progress
some of the tables have a bad data particularly the date columns. They have
the dates where the year is < 1900 (something like 0005). And this database
is at a different location from where we are located. I believe the SQL
server is having problem converting these dates.

I am using DTS wizard to import the data and transform. Modified the Visual
Basic Transformation Script for rectifying the bad data column like

If Year(DTSSource("Column")) < "1951" then
DTSDestination("Column") = "01/01/1951"
Else
DTSDestination("Column") = DTSSource("Column")
End If

Still it is erroring out.
Here is the error message:
Error at Destination for Row number 177704. Errors encountered so far in
this task: 1.
Insert error,column 10('column name',DBTYPE_DBTIMESTAMP), status 6: Data
overflow. Invalid cahracter value for cast specification.

I would appreciate your suggestions on this problem.

Thanks,
SamK

Reply With Quote
  #3  
Old   
RLoski
 
Posts: n/a

Default RE: Problem with year less than 1900 in DTS Import - 12-23-2006 , 01:50 PM


I'm at a bit of a disadvantage right now. My VBScript ActiveX is not working
on home machine.

When I tried using VBA, it interpreted the 0005 year as 2005. The problem
is that the assignment of the source column to the destination treats it as
0005.

You could put "on error resume next"
If Year(DTSSource("Column")) < "1951" then
DTSDestination("Column") = "01/01/1951"
Else
on error resume next
DTSDestination("Column") = DTSSource("Column")
if err.Number <> 0 then
DTSDestination("Column") = "01/01/1951"
end if

End If


--
Russel Loski, MCSD.Net


"SamK" wrote:

Quote:
Hi Russ,

Modified the code as suggested. It could process the Row number 177704 where
the value is '0205-05-17'. However errored out again at the Row number 184842
where the value is '0005-02-02'

This is the error message:
Error during Transformation 'AxScriptXform' for Row
number 184842. Errors encountered so far in this task: 1.
ActiveX Scripting Transform 'AxScriptXform' encountered
an invalid data value for 'um-da050' source column.

I would appreciate your suggestions.

Best Regards
SamK


"RLoski" wrote:

The "Year" function returns an integer. You are doing a string compare. The
string "5" is greater than the string "1951" (5 is after 1).

Try:

if Year(DTSSource("Column")) < 1951 then

With this code it will do an integer compare: 5 is less than 1951.
--
Russel Loski, MCSD.Net


"SamK" wrote:

I am basically a report writer and not a database developer.

I am importing data from Progress database to SQL Server 2000. In Progress
some of the tables have a bad data particularly the date columns. They have
the dates where the year is < 1900 (something like 0005). And this database
is at a different location from where we are located. I believe the SQL
server is having problem converting these dates.

I am using DTS wizard to import the data and transform. Modified the Visual
Basic Transformation Script for rectifying the bad data column like

If Year(DTSSource("Column")) < "1951" then
DTSDestination("Column") = "01/01/1951"
Else
DTSDestination("Column") = DTSSource("Column")
End If

Still it is erroring out.
Here is the error message:
Error at Destination for Row number 177704. Errors encountered so far in
this task: 1.
Insert error,column 10('column name',DBTYPE_DBTIMESTAMP), status 6: Data
overflow. Invalid cahracter value for cast specification.

I would appreciate your suggestions on this problem.

Thanks,
SamK

Reply With Quote
  #4  
Old   
SamK
 
Posts: n/a

Default RE: Problem with year less than 1900 in DTS Import - 12-26-2006 , 09:14 AM


Hi Russ,

Thanks for your suggestion.

However this code also resulted in the same error as previous one at row
number 184842.

If Year(DTSSource("Column")) < 1951 then
DTSDestination("Column") = "01/01/1951"
Else
on error resume next
DTSDestination("Column") = DTSSource("Column")
if err.Number <> 0 then
DTSDestination("Column") = "01/01/1951"
end if
End If

And this is the error message:

Error during Transformation 'AxScriptXform' for Row
number 184842. Errors encountered so far in this task: 1.
ActiveX Scripting Transform 'AxScriptXform' encountered
an invalid data value for 'um-da050' source column.

Best Regards,
SamK


"RLoski" wrote:

Quote:
I'm at a bit of a disadvantage right now. My VBScript ActiveX is not working
on home machine.

When I tried using VBA, it interpreted the 0005 year as 2005. The problem
is that the assignment of the source column to the destination treats it as
0005.

You could put "on error resume next"
If Year(DTSSource("Column")) < "1951" then
DTSDestination("Column") = "01/01/1951"
Else
on error resume next
DTSDestination("Column") = DTSSource("Column")
if err.Number <> 0 then
DTSDestination("Column") = "01/01/1951"
end if

End If


--
Russel Loski, MCSD.Net


"SamK" wrote:

Hi Russ,

Modified the code as suggested. It could process the Row number 177704 where
the value is '0205-05-17'. However errored out again at the Row number 184842
where the value is '0005-02-02'

This is the error message:
Error during Transformation 'AxScriptXform' for Row
number 184842. Errors encountered so far in this task: 1.
ActiveX Scripting Transform 'AxScriptXform' encountered
an invalid data value for 'um-da050' source column.

I would appreciate your suggestions.

Best Regards
SamK


"RLoski" wrote:

The "Year" function returns an integer. You are doing a string compare. The
string "5" is greater than the string "1951" (5 is after 1).

Try:

if Year(DTSSource("Column")) < 1951 then

With this code it will do an integer compare: 5 is less than 1951.
--
Russel Loski, MCSD.Net


"SamK" wrote:

I am basically a report writer and not a database developer.

I am importing data from Progress database to SQL Server 2000. In Progress
some of the tables have a bad data particularly the date columns. They have
the dates where the year is < 1900 (something like 0005). And this database
is at a different location from where we are located. I believe the SQL
server is having problem converting these dates.

I am using DTS wizard to import the data and transform. Modified the Visual
Basic Transformation Script for rectifying the bad data column like

If Year(DTSSource("Column")) < "1951" then
DTSDestination("Column") = "01/01/1951"
Else
DTSDestination("Column") = DTSSource("Column")
End If

Still it is erroring out.
Here is the error message:
Error at Destination for Row number 177704. Errors encountered so far in
this task: 1.
Insert error,column 10('column name',DBTYPE_DBTIMESTAMP), status 6: Data
overflow. Invalid cahracter value for cast specification.

I would appreciate your suggestions on this problem.

Thanks,
SamK

Reply With Quote
  #5  
Old   
SamK
 
Posts: n/a

Default RE: Problem with year less than 1900 in DTS Import - 12-26-2006 , 01:56 PM


Excellent! Works Great. I added a line a of code to check for Nulls too.

If IsNull(DTSSource("Column")) Then
DTSDestination("Column") = null
ElseIf IsDate(DTSSource("Column")) = 0 Then
DTSDestination("Column") = "01/01/1951"
ElseIf Year(DTSSource("Column")) < 1951 Then
DTSDestination("Column") = "01/01/1951"
Else
on error resume next
DTSDestination("Column") = DTSSource("Column")
if err.Number <> 0 then
DTSDestination("Column") = "01/01/1951"
end if
End If
Main = DTSTransformStat_OK

Russ, Thanks a bunch for all your help!

Best Regards,
SamK


"RLoski" wrote:

Quote:
Test for null and isdate. Try isdate first, then isnull followed by isdate
test. I believe that isdate(null) returns false.

If isdate(DTSSource("Column")) = false then
DTSDestination("Column") = "01/01/1951"
elseIf Year(DTSSource("Column")) < 1951 then
DTSDestination("Column") = "01/01/1951"
Else
on error resume next
DTSDestination("Column") = DTSSource("Column")
if err.Number <> 0 then
DTSDestination("Column") = "01/01/1951"
end if
End If

--
Russel Loski, MCSD.Net


"SamK" wrote:

Hi Russ,

Thanks for your suggestion.

However this code also resulted in the same error as previous one at row
number 184842.

If Year(DTSSource("Column")) < 1951 then
DTSDestination("Column") = "01/01/1951"
Else
on error resume next
DTSDestination("Column") = DTSSource("Column")
if err.Number <> 0 then
DTSDestination("Column") = "01/01/1951"
end if
End If

And this is the error message:

Error during Transformation 'AxScriptXform' for Row
number 184842. Errors encountered so far in this task: 1.
ActiveX Scripting Transform 'AxScriptXform' encountered
an invalid data value for 'um-da050' source column.

Best Regards,
SamK


"RLoski" wrote:

I'm at a bit of a disadvantage right now. My VBScript ActiveX is not working
on home machine.

When I tried using VBA, it interpreted the 0005 year as 2005. The problem
is that the assignment of the source column to the destination treats it as
0005.

You could put "on error resume next"
If Year(DTSSource("Column")) < "1951" then
DTSDestination("Column") = "01/01/1951"
Else
on error resume next
DTSDestination("Column") = DTSSource("Column")
if err.Number <> 0 then
DTSDestination("Column") = "01/01/1951"
end if

End If


--
Russel Loski, MCSD.Net


"SamK" wrote:

Hi Russ,

Modified the code as suggested. It could process the Row number 177704 where
the value is '0205-05-17'. However errored out again at the Row number 184842
where the value is '0005-02-02'

This is the error message:
Error during Transformation 'AxScriptXform' for Row
number 184842. Errors encountered so far in this task: 1.
ActiveX Scripting Transform 'AxScriptXform' encountered
an invalid data value for 'um-da050' source column.

I would appreciate your suggestions.

Best Regards
SamK


"RLoski" wrote:

The "Year" function returns an integer. You are doing a string compare. The
string "5" is greater than the string "1951" (5 is after 1).

Try:

if Year(DTSSource("Column")) < 1951 then

With this code it will do an integer compare: 5 is less than 1951.
--
Russel Loski, MCSD.Net


"SamK" wrote:

I am basically a report writer and not a database developer.

I am importing data from Progress database to SQL Server 2000. In Progress
some of the tables have a bad data particularly the date columns. They have
the dates where the year is < 1900 (something like 0005). And this database
is at a different location from where we are located. I believe the SQL
server is having problem converting these dates.

I am using DTS wizard to import the data and transform. Modified the Visual
Basic Transformation Script for rectifying the bad data column like

If Year(DTSSource("Column")) < "1951" then
DTSDestination("Column") = "01/01/1951"
Else
DTSDestination("Column") = DTSSource("Column")
End If

Still it is erroring out.
Here is the error message:
Error at Destination for Row number 177704. Errors encountered so far in
this task: 1.
Insert error,column 10('column name',DBTYPE_DBTIMESTAMP), status 6: Data
overflow. Invalid cahracter value for cast specification.

I would appreciate your suggestions on this problem.

Thanks,
SamK

Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top