Accessing Excel data source running SSIS package on 64 bit server

Question

Hello,

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 up as a CmdExec type and call the 32 bit version of DTExec. But I don't seem 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 LETTERExcelFile;\""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

which gives me and 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 do it?

Cheers

Nige

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

Answer 2

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

Answer 3

The real question is why hardcode the connection string in the call versus from a config file or db?

Answer 4

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 10 if you are using SSIS 2008).

Answer 5

You can use an Excel connection in 64bit environment. Go to the package configuration properties.

Debugging -> Debugging Options -> Run64BtRuntime -> change to False In addition if you use SQL Agent go to the job step properties and then check the 32 bit runtime.

Answer 6

I kinda did what Dr Zim did but I copied the DTExec file C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe to C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ folder but named the 32 bit one to DTExec32.exe

then I was able to run my SSIS script through a stored proc:

set @params = '/set \package.variables[ImportFilename].Value;"\"' + @FileName + '\"" '

set @cmd = 'dtexec32 /SQ "' + @packagename + ' ' + @params + '"' --DECLARE @returncode int exec master..xp_cmdshell @cmd --exec @returncode = master..xp_cmdshell @cmd --select @returncode