How do I access Excel data source from an SSIS package deployed on a 64-bit server?

Question

I have an SSIS package that exports data to a couple of Excel files for transfer to a third party. To get this to run as a scheduled job on a 64-bit server I understand that I need to set the step as a CmdExec type and call the 32-bit version of DTExec. But I don't seem to be able to get the command right to pass in the connection string for the Excel files.

So far I have this:

DTExec.exe /SQL \PackageName /SERVER OUR2005SQLSERVER /CONNECTION 
LETTER_Excel_File;\""Provider=Microsoft.Jet.OLEDB.4.0";"Data 
Source=""C:\Temp\BaseFiles\LETTER.xls";"Extended Properties=
""Excel 8.0;HDR=Yes"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

This gives me the error: Option "Properties=Excel 8.0;HDR=Yes" is not valid.

I've tried a few variations with the Quotation marks but have not been able to get it right yet.

Does anyone know how to fix this?

UPDATE:

Thanks for your help but I've decided to go with CSV files for now, as they seem to just work on the 64-bit version.

Solution 1

Unless it's a business requirement, I suggest you move the connection string from the command line to the package and use a package configuration to define the path to the Excel file (in order not to hard-code it). This will make it easier to maintain.

1. Define a variable @ExcelPath.
2. Use connection's Expression property to construct a connection string - an example: "Data Source=" + @[User::FilePath] + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;"
3. Assign a value to @ExcelPath in the package configuration.

---

Take a closer look at the connection string above. It's taken from a working package. I'm not sure about this, but maybe you don't need any quotes at all (the ones above are only there because the expression editor requires them).

---

I have also had some problems with SSIS on 64-bit SQL Server 2005. That post from my blog does not answer your question, but it is somewhat related so I am posting the link.

Solution 2

There is no 64-bit Jet OLEDB provider, so you can't access Excel files from 64-bit SSIS.

However, you can use 32-bit SSIS even on 64-bit server. It is already installed when you installed 64-bit version, and all you need to do is run the 32-bit DTEXEC.EXE - the one installed Program Files (x86)\Microsoft Sql Server\90\Dts\Binn (replace 90 with 100 if you are using SSIS 2008).