none
SSIS Command script for Creating SQL job in 32 bit mode RRS feed

  • Question

  • Hi Friends,

    In my SSIS package, I'm trying to load Excel source file into table, Excel file format is ".xls" file. I'm getting the below error.

    Error:

    The Requesterd OLEDB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32 bitmode.

    Catalog script:

    -- Create the SQL Agent job.
    -----------------------------------------------------------------------
    set @Command =
             N'/ISSERVER "\"\SSISDB\'+@Folder+'\'+@ProjectName+'\'+@PackageName+'\"" '
      
            +N'/SERVER "\"'+@@SERVERNAME+'\"" '
            +N'/Envreference ' +@reference_id +
            +N' /CALLERINFO SQLAGENT'
            +N' /REPORTING E ' 
      

    Please let me know what change I need to make in the above catalog script.T

    Thanks in advance.

    Regards,

    Lucky


    • Edited by LuckyAbd Thursday, July 27, 2017 2:48 AM
    Thursday, July 27, 2017 2:48 AM

All replies

  • You will need to add +N' /x86' in the script to flag the package for 32bit runtime.
    Thursday, July 27, 2017 3:15 AM
  • Hi,

    Could you please confirm in which step I need to add +N' /x86' ?

     N'/ISSERVER "\"\SSISDB\'+@Folder+'\'+@ProjectName+'\'+@PackageName+'\"" '
      
            +N'/SERVER "\"'+@@SERVERNAME+'\"" ' + N'/X86'
      +N'/Envreference ' +@reference_id +
            +N' /CALLERINFO SQLAGENT'
            +N' /REPORTING E '

    Thanks,

    Lucky


    • Edited by LuckyAbd Thursday, July 27, 2017 3:21 AM
    Thursday, July 27, 2017 3:20 AM
  • That looks right, however you'll need to make sure there's blank spaces before after the /x86 when everything gets concatenated together.  Right now it looks like it would concatenate as /X86/Envreference instead of /X86 /Envreference.
    Thursday, July 27, 2017 3:55 AM
  • Hi,

    Thank you for the response.

    I have totally 4 packages, 3 packages are running fine because excel source file ".xslx". 1 package had the above 32 bit issue due to excel source file format as ".xls".

    If we make change the above change, other 3 64 bit packages will get any impact ?

    Thanks,

    Lucky

    Thursday, July 27, 2017 4:10 AM
  • Hi LuckyAbd,

    Do you execute the 4 SSIS packages with one job?

    If one job corresponding to one package, you could change the command manually.

    Theoretically, you could run the SSIS package on 32 bit mode as soon as the version of Excel is 32 bit and you have installed 32 bit driver of Excel. Please do a test and check what happens.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 27, 2017 7:50 AM
    Moderator
  • You have a missing driver on the server.

    Try to install the drivers from this link and try again!

    Microsoft Access Database Engine 2010 Redistributable

    Good luck.

    Thursday, July 27, 2017 11:46 AM
  • Hi,

    Thank you for the response.

    I have totally 4 packages, 3 packages are running fine because excel source file ".xslx". 1 package had the above 32 bit issue due to excel source file format as ".xls".

    If we make change the above change, other 3 64 bit packages will get any impact ?

    Thanks,

    Lucky

    The 32 bit flag is per job step, so your 3 other SSIS packages would still be ran in 64 bit mode unless otherwise specified.
    Thursday, July 27, 2017 6:04 PM