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.
| Author |
Topic |
|
gsanthosh84
Starting Member
1 Post |
 Posted - 2009-09-29 : 09:26:08
|
| Hi,I have package moved into Windows 2003 server (64 bit) SP2. I have a Data flow task and Script task in the package. 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 Ethe 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 |
|
|
|
|
|
Script task to open excel file failed