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


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:

Source=""C:\Temp\BaseFiles\LETTER.xls";"Extended Properties=

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?


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.

By : KiwiNige


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.

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).

By : Michael

This video can help you solving your question :)
By: admin