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