Re: DTS handling bad dates

Actually the script is a little redundant -- all I need is the date
check:

If IsDate(mid(DTSSource("Col004"),1,4) & "/" &
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)) then
DTSDestination("DATE-EMP-END") = mid(DTSSource("Col004"),1,4) & "/"
& _
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)
else
DTSDestination("DATE-EMP-END") = null
end if

BUT it's still generating the two date errors:
20800218
20820218

Thanks for any suggestions

robboll wrote:
Doing this the MyImport.txt file indicated two dates that are correct
and I don't understand why it fails when it hits them:

20800218
20820218

How I am asking the VBScript to enter it is as follows:

if DTSSource("Col004") = "99999999" then
Main = DTSTransforStat_SkipRow
else
if IsDate(mid(DTSSource("Col004"),1,4) & "/" &
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)) then
DTSDestination("DATE-EMP-END") = mid(DTSSource("Col004"),1,4) & "/"
& _
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)
else
DTSDestination("DATE-EMP-END") = null
end if
end if


Is there a regional setting or something that is seeing these dates as
errors?

Thanks,

RBollinger

Charles Kangai wrote:
According to the error, the data pump is failing on trying to insert into the
destination table. Go to Properties of the Transform Data task, click on the
Options tab. Specify an exception file e.g. MyImport.txt, turn off 7.0
format, but turn on Source rows and Destination rows, and increase the
Maximum Error Count to 9999. Run the import and look for a file named
MyImport.txt.dest (if you used MyImport.txt as the file name). You will then
be able to look at the rows that fail. That might tell you why the rows are
failing.

Note that because your increased Maximum Error Count to a high number, most
of your rows will be imported. And remove the SkipRow stuff for this
troubleshooting.

Hope this helps.

Charles Kangai, MCT, MCDBA

Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services"
Author of Learning Tree's 4-day course: "SQL Server Reporting Services" URL:



"robboll" wrote:

Update: I removed the 19910631 and 19991299 rows from the test data
source and it is still failing, so they apparently don't have anything
to do with it. So it looks like the
Main = DTSTransforStat_SkipRow is working -- but something else is
causing it to fail.


robboll wrote:
I have the DTS package as follows:

Function Main()

dim i_Day
dim i_Month
dim i_Year

i_Day = Mid( DTSSource("Col002") ,7 , 2 )
i_Month = Mid( DTSSource("Col002") ,5 , 2 )
i_Year = Left(DTSSource("Col002"),4)

DTSDestination("Col001") = DTSSource("Col001")
if IsDate(i_Year & "-" & i_Month & "-" & i_Day) = true then
DTSDestination("Col002") =( i_Year & "-" & i_Month & "-" & i_Day )
else
msgbox "i_Year & "-" & i_Month & "-" & i_Day"
Main = DTSTransforStat_SkipRow
end if
DTSDestination("Col003") = DTSSource("Col003")
Main = DTSTransformStat_OK
End Function

~~~~~~~~~~~~~~~~~~~~~~~~~~

Here are a couple of bad date examples that I am running into: 19910631
and 19991299
Note the msgbox under the "else". This shows the two bad dates when
they happen, and I thought the Main = DTSTransforStat_SkipRow was
supposed to skip stamping the bad date to the smalldatetime field. The
DTS job fails with the error:

The number of failing rows exceeds the maximum specified.
Insert error, column 2 ('Col002', DBTYPE_DBTIMESTAMP), status 6: Data
overflow.
Invalid character value for cast specification.

Any suggestions greatly appreciated!!!

RBollinger



.

Relevant Pages

  • Re: DTS handling bad dates
    ... Note that because your increased Maximum Error Count to a high number, ... source and it is still failing, so they apparently don't have anything ... dim i_Month ... DTS job fails with the error: ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS handling bad dates
    ... Note that because your increased Maximum Error Count to a high number, ... source and it is still failing, so they apparently don't have anything ... dim i_Month ... DTS job fails with the error: ...
    (microsoft.public.sqlserver.dts)
  • Re: Loop through properties
    ... Sub CopyPageSetup() ... Dim oDocSource As Document ... Set oDocTarget = Documents ... high achievement, and who at the worst, if he fails, at least fails ...
    (microsoft.public.word.vba.general)
  • Re: ASP Error Handling (conn.Open) (ADODB.Connection)
    ... I am trying to make it fail with a proper error message if I ... but the ASP file fails altogether when I do that. ... dim sUser ... Sending a connection string over http to a server? ...
    (microsoft.public.inetserver.asp.db)
  • Re: prov something simple but not obvious...
    ... Type in square brackets. ... This is because Type is a reserved word in Access, ... If it still fails, open the Immediate Window and copy the SQL ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.formscoding)
Quantcast