September 29, 2009 at 7:08 am
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
September 30, 2009 at 4:17 am
Hi,
I believe the OLEDB provider Excel is 32bit and your problems is related to this because any 64bit application won't be able to use it.
You are running the 32bit version of DTExec from the x86 directory and therefore it is OK.
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe"
Your workstation is 32bit and therefore is OK.
Your server is 64bit. BIDS is a 32bit application however you could try this. Under the project properties -> debug options. Under the project properties -> debug options change the Run64BitRuntime property to false.
In my experience debugging 32\64 bit SSIS problems on the server is tricky.
Regards
Daniel
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply