sql 2000 dts activeX vbscript file copy failure

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)

sql 2000 dts activeX vbscript file copy failure

Author

Topic

beyonder422
Posting Yak Master

124 Posts

Posted - 2008-07-24 : 09:43:19

We recently did a SAN upgrade, migrated sql server 2000 cluster to new hardware, and everything seems to be working fine except one process.

A simple file copy between 2 servers.

The scenario plays out that the dts package I was using before the SAN upgrade works with other servers (large and small files),

but one process fails when copying to a specific server.
This scheduled job works with small files (<5mb), but fails with large files (>500mb), and my testing reveals that when I try a >10mb file move the process fails with the error below.

-- error message in job history -- start
Executed as user: dbservice
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_4
DTSRun OnError: DTSStep_DTSActiveScriptTask_4, Error = -2147220482 (800403FE)
Error string: ActiveX Scripting encountered a Run Time Error during the execution of the script.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE);
Provider Error: 0 (0)
Error string: ActiveX Scripting encountered a Run Time Error during the execution of the script.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_4
DTSRun: Package execution complete. Process Exit Code 1. The step failed.

-- error message in job history -- end

I recently posted an error trapping question in an attempt to gather more info, but essentially the job runs for 5-10 minutes, and it looks like the copy was successful, I see the file on the remote server, but the job always reports failure...

And when I try to use the file on the remote server it always comes up corrupt.

Could this be a permissions issue? I think not because the small file copy process works fine. I have verified the dts owner, job owner, sql server agent use accounts that are server admins on host and client servers.

I have also checked available space, looked into possible DCOMcnfg perm updates, etc., but am basically stuck having to run the process manually until I figure this issue out.

When I run the dts job manually, the process works.

HELP!!!!

---------------------
I do not define what is obvious...

beyonder422
Posting Yak Master

124 Posts

Posted - 2008-07-24 : 12:32:43

Actual copy code:

'******
' Visual Basic ActiveX Script
'******

Function Main()
On Error Resume Next
Dim objErr

Dim fileObject
Dim FileName
Dim PartExplosionFileName
Dim dtNow
Dim fs, fr
Dim fso, fw
Dim Input

Const ForReading = 1, ForWriting = 2,ForAppending = 8,TristateFalse = 0

Set fs = CreateObject("Scripting.FileSystemObject")
Set fso = CreateObject("Scripting.FileSystemObject")

Set fol = fs.GetFolder(DTSGlobalVariables("BusinessHostFiles"))

bExits = False

For Each fil In fol.Files
if lcase(fil.Name) = "bomekanban.txt" Then
FileName = DTSGlobalVariables("BusinessHostFiles") &"\" & fil.Name
If fs.FileExists(FileName) Then
PartExplosionFileName = fil.Name
bExists = True
Exit For
End If

End If

Next

Set fol = Nothing

If bExists Then

Set fileObject = fs.GetFile(FileName)

dtNow = Now
fileObject.Copy DTSGlobalVariables("PartExplosion") & "\" & PartExplosionFileName, true
Set fileObject = Nothing
End If

If objErr.Number <> 0 Then
DTSGlobalVariables("ReturnMessage") = objErr.Description
Main = DTSTaskExecResult_Failure
Else
DTSGlobalVariables("ReturnMessage") = "File Move Successful"
Main = DTSTaskExecResult_Success
End If

End Function

---------------------
I do not define what is obvious...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-24 : 13:08:15

Try enable package logging then run it again, should give you more details.

beyonder422
Posting Yak Master

124 Posts

Posted - 2008-07-24 : 13:53:15

No data displayed in errorcode or errordescription columns

---------------------
I do not define what is obvious...

beyonder422
Posting Yak Master

124 Posts

Posted - 2008-07-24 : 15:23:41

Ok....

Looks like we had a NIC hardware issue.

We recently failed our cluster over to a new server and since then everything had slowed down. Everything appeared normal enough, but some jobs were failing, files moved slowly, but nothing definative.

Today we had a major connectivity issue occur and after failing to the alternate server, all issues were resolved.

Based on system\app logs on the cluster, we deduced a NIC was failing.

What's funny\sad is that in all my SQL\server\code data gathering\symptom observation I could not find a single instance where something pointed me to network connectivity...

Granted I was screaming at the top of my lungs that something was wrong and things were running VERY slow, there was nothing blantantly visible to say "NIC" or "NETWORK"!!!!

Posting outcome for everyone else's edification.

---------------------
I do not define what is obvious...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources