posted 8/16/2011 by kylewalker - Views: [1949]
Many of the SSIS packages you'll see on the job might use Excel workbooks as a source or destination for your data. Another trend that's becoming increasingly common is the use of 64-bit machines for your ETL processing. However, with the combination of these two things comes a little quirk with SSIS. Quite simply, the Excel data provider doesn’t work with 64-bit machines. Now, as many of you know, there is a way to force your package (that is on a 64-bit machine) to run in 32-bit mode while debugging. And that is to right-click on the project and select Properties. From there, choose the “Debugging” page and change “Run64BitRuntime” to false. But what many developers, who are locked up in Developerland, might not know is that once you deploy these packages to a server, that project property is no longer recognized. When you add the package to a job and schedule or run it, that job will fail (for the same reason it fails in BIDS when “Run64BitRuntime” is set to true). There is a setting within the job step properties (on the Execution options tab) that you have to set in order for the package to run in 32-bit mode.
And that setting is "Use 32 bit runtime". Just check that box then hit OK. Now you can schedule and run your job without it failing on the packages that use Excel. A pretty simple little tip, but might save you an hour or so of pulling your hair out.