Error in ActiveX script while execting

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 2005 Forums

SSIS and Import/Export (2005)

Error in ActiveX script while execting

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2007-11-30 : 10:29:18

HI Experts,
I am having 2 severs (SQL2000 & SQL2005), there is already one DTS package in SQL 2000, now i have migrated DTS package to SSIS package. Now the problem is DTS package is having ActiveX script and it was executing perfectly in 2000 server but after migrated to 2005 server if i am executing the coverted SSIS package it is giving one error and the error is displaying here.

Error 1 Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. SearsCDCC_Transfer (1).dtsx 0 0

and i am also displaying the ACtiveX code as well and here oma11pngrdb02 is 2000 server and sant01pngrdb is 2005 server.

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

Dim gsErrorFile
gsErrorFile = "\\oma11pngrdb02\App_Data_Reporting\Sears\errors2.txt"

Function Main()
Main = DTSTaskExecResult_Success
set objftp = CreateObject("ftpx.aspftp")
objftp.sServerName = "10.1.3.175"
objftp.sUserID = "COS.FTP"
objftp.sPassword = "SENDFILE"

if objftp.bConnect then
dim fname, lfname

fname = "$IOMH04.PRDFFTAP.ST462330"
lfname = "\\oma11pngrdb02\App_Data_Reporting\Sears\cdccfile.txt"
If objFTP.bGetFile (fname, lfname ) then
Main = DTSTaskExecResult_Success
Else
logError("Error getting file: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
End If
Else
logError("Error connecting: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure

end if
End Function

Function logError(sErrorMsg)
Dim objErrorFile, objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objErrorFile = objFSO.OpenTextFile(gsErrorFile, 2,True)
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.writeLine FormatDateTime(Now(),vbShortdate) & " " & FormatDateTime(Now(),vbShorttime)
objErrorFile.writeLine sErrorMsg
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.close
Set objErrorFile = Nothing
Set objFSO = Nothing
End Function

Can any one give me the solution as early as possible, and your help is soo Appreciable

-Thanks N Regards,
Kanthi.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-03 : 10:41:31

Well, looks like you have to redo whatever the activeX script try to do in the new way.

The overall server environment has changed. Try to use the new ftp task. Also there are other custom ftp samples that could be called from stored proc (in t-sql syntax, which is more tansferable between sql2000 and sql2005 than vba script).

ekb18c
Starting Member

18 Posts

Posted - 2007-12-09 : 17:13:37

You should be able to run the activeX script on 2005 by using the active x script task.

Find the 'script task' that the migration turned your 'active x script' to and replace it with 'active x task' from the 'Control Flow items'.

Paste the active x script in the active x task and you should be all good.

Language = vb script language
Script = Your script
EntryMethod = main

Hope that helps, you will notice that migration tool won't migrate active x scripts correctly as well as dynamic properties. You need to manually redo the dynamic properties and manually add in 'Active X Task' to your new SSIS packages.

OR

Use the 'Execute DTS 2000 Package Task'.

Works either way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources