Script task to open excel file failed

[gsanthosh84]

Hi,

I have package moved into Windows 2003 server (64 bit) SP2. I have a Data flow task and Script task in the package. Data Flow Task has Excel as source. I ran the package as below using the batch script.

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /f "J:\SSIS\APN\SGN\Package\Actual.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

the above batch file runs the Data flow task perfectly. But i have problem in the script task editor. Here in the script task, I try to connect the excel file and retrieve data based on some condition.

Below is my script to connect to excel data provider in the script task editor.

Dim DB_CONNECT_STRING1 As String
DB_CONNECT_STRING1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=J:\SSIS\APN\SGN\Package\APAC SC Indices AU.xls;Extended Properties='Excel 12.0 Xml;HDR=NO';"
Dim myConn1 As Object
Dim myCommand1 As Object

myConn1 = CreateObject("ADODB.Connection")
myCommand1 = CreateObject("ADODB.Command")
Dim rs As Object
rs = CreateObject("ADODB.Recordset")

Dim rs1 As Object
rs1 = CreateObject("ADODB.Recordset")
myCommand2 = CreateObject("ADODB.Command")
MsgBox("HI1")

Dim rs2 As Object
rs2 = CreateObject("ADODB.Recordset")
myCommand3 = CreateObject("ADODB.Command")
MsgBox("HI2")

myConn1.Open(DB_CONNECT_STRING1)
myCommand1.ActiveConnection = myConn1
MsgBox("HI3")


When I run this script, I get an error "Provider cannot found.It may not be properly installed". It fails in the line "myConn1.Open(DB_CONNECT_STRING1)", when it is trying to open the connection.I tried changing the provider, but it didnot help. Windows server 2003 does not have microsft office installed in it. Does this creates the problem. If so, why the data flow task runs perfectly. Can I call the excel connection manager in the script task also. This package runs in my local system because it is 32 bit machine and office 2007 installed. Is there any way to get rid of this problem.

--San