Excel not supported in SSIS 64bit

  • Hi Experts,

    I am running a package with a 64bit version of sql server .The package runs fine when executed manually in management studio (imported the package).

    However, when I try to schedule it, it fails. I have tried a few things to fix this but to no avail. The summary of the error it gives me is that Excel is not supported in the 64 bit. I have tried the following to work around this;

    - in the package property, changed the run64bitruntime debug option to "false"

    - set delayed validation property to "true" for all the tasks in the package

    Do I need to change the ole db microsoft jet provider? If so, how do I go about that? Any ideas please?

  • Because you are using the Excel/Jet driver you need to make sure you use the 32-bit version of dtexec to run the package. On 64-bit installs SQL Server Agent uses the 64-bit version when you create a job to run an SSIS package. Check out this article, the answer you are looking for is near the bottom of the article.

  • A method we use is to execute the pack using the cmd mode in 32 bit. Setup the job step as a CMD package, prefixed with the following:

    "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"

    Extract the command line string for your DTS package and put it after the dtexec.exe command.

    Steve Haines

  • For anyone looking for a working solution I am using the code below on a 64 bit SQL 2008 server. Thanks Steve for the push in the right direction.

    My SSIS package adds @I_SIP_ID as a new column to the excel results then inserts into a staging table.

    DECLARE @V_PACKAGE VARCHAR(1000)

    DECLARE @I_SIP_ID INT, @V_FILEPATH VARCHAR(1000)

    SELECT @I_SIP_ID = 80, @V_FILEPATH = 'G:\SIP\NEW1.xls'

    /*CMD_SHELL HAS TO USE DOS 8.3 NOTATION SO CAN ONLY HAVE ONE SET OF DOUBLE QUOTES!

    http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/4e7024bb-9362-49ca-99d7-1b74f7178a65*/

    SELECT @V_PACKAGE = '"D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" '

    + '/FILE G:\SIP\Import_SIP_excel.dtsx '

    + '/SET \Package.Variables[User::i_sip_id].Value;' + CONVERT(VARCHAR(10), @I_SIP_ID) + ' '

    + '/SET \Package.Variables[User::s_file_name].Value;' + @V_FILEPATH + ' '

    + '/MAXCONCURRENT -1 '

    + '/CHECKPOINTING OFF'

    CREATE TABLE #T (V_OUTPUT VARCHAR(1000))

    --Insert into temp table so we pick up the results as well as exec

    INSERT INTO #T

    EXEC master..xp_cmdshell @V_PACKAGE

    SELECT *

    FROM #T

    --WHERE V_OUTPUT LIKE '%Error%'

    DROP TABLE #T

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply