August 5, 2011 at 3:16 pm
Hi,
I have a very critical issue that we are facing on our 64-bit server.
Environment Details:
Operating System : Windows Server 2008 R2 64-bit Enterprise Edition
Database System : Microsoft SQL Server 2008 R2 64-bit Enterprise Edition
Issue Details:
I am attempting to load a .xls 2003 Excel sheet into one our tables using SSIS.
Initially, when i attempted to load the file, an error popped up at the OLE DB Destination about the conversion of non-unicode and unicode data from the excel sheet into the database table.
I read a blog that suggested to use data conversion task to handle the error. I used the task, converted the input columns (D_WSTR) into (D_STR) and used proper data lengths.
The SSIS package executes with no issues.
When i attempt to run the package from an agent job step, the package does not run. For some reason, the command line is completely screwed up.
I installed the ACE 12.0 64-bit driver since the JET driver is not supported on 64-bit machines. I have the SSIS project option "Run64bitruntime" set to false.
There were a few issue regarding the extended properties with additional "" so i removed them too.
My main concern is why is it still using the JET driver in the command line in the agent job since i read that the ACE driver is backward compatible.
Following is the command:
/FILE "H:\SQL _2008_SSIS\Crystal Clear _ SSIS Packages\Test_Project\Test_Project\Salomon_CUSIP_Xref.dtsx" /CONNECTION "Excel Connection Manager";"\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\vendor_apps\datafeed\Salomon\Sal-XRef.xls;Extended Properties=EXCEL;\"" /CONNECTION "jabdw3265.cc";"\"Data Source=jabdw3265;Initial Catalog=cc;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Salomon_CUSIP_Xref-{A31BABBC-2653-4BF3-A67F-416B594E107D}jabdw3265.cc;Auto Translate=False;\"" /X86 /CHECKPOINTING OFF /REPORTING E
Please help !!!
August 8, 2011 at 12:11 am
Are the connections being overwritten in the Job Step - Data Sources tab?
Also, fyi, there's an execution option in Job step to configure 32 bit execution for use with the Jet driver.
August 8, 2011 at 4:33 am
You specify the provider in the SSIS package itself.
So you probably made an Excel connection manager, and there a connection string is created that specifies the JET provider.
Edit the SSIS package to work with the ACE provider, redeploy and then test your Agent job.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2011 at 10:58 am
Hi,
I don't think i am explicitly mentioning any connections in the job step. All the connections are built withint the package itself.
Yes, i have the 32-bit runtime execution option in the job step enabled.
Also, i did already see a few people bring up the issue with the additional double quotes being inserted in the extended properties values such as """"EXCEL 8.0; HDR = YES """". I did manually edit the command line and removed the additional quotes but the next time i open the job step, to my surprise, the complete command gets horrifyingly screwed up.
It would be really great if you could give me some detail on this. I am doing my research into this too but i would really appreciate if your expertise can help me resolve this.
Thanks
August 8, 2011 at 11:14 am
Yes.
I have an excel connection manager within the package itself. I have also installed the ACE 12.0 64-bit OLE DB provider for Excel since i came to know that the JET 4.0 OLE DB provider is not supported on 64-bit machines.
The excel sheet is a .xls file so it is a 2003 office file. When setting up the excel connection in the package, i selected microsoft office 97-2003 in the excel version field. The package was running fine in BIDS but craps out in agent job step. So i tried to change it to use Microsoft office 2007 since i read that the ACE driver is backward compatible, the package runs fine again but still errors out at runtime from an agent job step.
My questions are:
1) Should i also install the 32-bit version of the ACE driver ?
2) Why is the command line using the JET driver where as the connection manager in the excel connection is set to office 2007 which i believe must be using the ACE driver?
I am very confused. Please help !!
August 8, 2011 at 7:19 pm
I did some testing on my laptop and got it to work with the driver from here:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734
In summary, I couldn't load the 32 bit 2010 version of the drivers because it said it was incompatible with office 2010, BIDS would not recognise the 64 bit 2010 version of the drivers. So I went with the 2007 drivers which succeeded. The excel file was a plain .xls file. I created a new blank package and loaded it into a new table generated by my oledb destination.
Things of note:
When you select the Excel Connection Manager and hit F4 the connection string reads as follows:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<filename>.xls;Extended Properties="EXCEL 12.0;HDR=YES";
The command line of the job step reads as follows:
/FILE "C:\Users\Sam\Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\Package.dtsx" /X86 /CHECKPOINTING OFF /REPORTING E
Notice that there are no /CONNECTION options which will override the connection string.
Are your connection strings set by an expression or configuration at any point?
My laptop:
Windows 7 Pro x64
Office 2010 x64
Microsoft SQL Server 2008 10.0.2531.0
Here's a log of what I did:
Loaded AccessDatabaseEngine x64
http://www.microsoft.com/download/en/details.aspx?id=13255
Restart PC.
Created new blank project in BIDS
Added DFT
Added Excel Source
Created new excel connection via Excel Source with 2007 setting.
Attempted to set table for query:
Driver not registered.
Deleted Excel Source and connection
Set to 32 bit paackage execution in BIDS project
Restarted BIDS
Added Excel Source
Created new excel connection via Excel Source with 2007 setting.
Attempted to set table for query:
Driver not registered
Deleted Excel Source and connection
Uninstalled driver
Tried to load AccessDatabaseEngine x86
Failed to install due to 64 bit office
Loaded 2007 Office System Driver
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734
Restarted PC
Added Excel Source
Created new excel connection via Excel Source with 2007 setting.
Attempted to set table for query: Succeeded
Connect to oledb destination with local adventureworks connection and constructed default table
Created test job in SSA
Test Execution via start job at step:
Failed due to file security on package
Granted SSA service account permissions to package file.
Test Execution via start job at step:
Failed: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
Set job step execution option to 32 bit mode.
Test Execution via start job at step:
Failed: excel file locked or security issue
Granted SSA service account permissions to excel file.
Test Execution via start job at step:
Successful
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply