Post reply

SSIS with Excel

  • November 24, 2009 at 1:19 pm

    #214249

    I'm running into an issue that my 64 bit Windows 2008 R2 server doesn't seem to have drivers for Excel! Locally, I am writing to Excel files on a 08 server, but when I deploy my package, it throws errors that it can't talk to Excel. There has to be a way to connect to Excel from a 64 bit server....any suggestions?

  • November 24, 2009 at 3:16 pm

    #1083706

    the 32 bit excel ole db provider should work in a 64 bit environment.

    hth

  • November 24, 2009 at 3:20 pm

    #1083708

    you must run the ssis package on 32bit mode.

    follow these instructions:

    http://msdn.microsoft.com/en-us/library/ms141766.aspx

  • November 25, 2009 at 6:37 am

    #1083952

    Perfect! Worked like a charm, thanks much.

  • November 30, 2009 at 7:29 am

    #1085395

    Correction...didn't work! I switched things in development area and then setup the job to use 32-bit runtime...here's my error code.

    Executed as user: VIRCHOWKRAUSE\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:43:55 AM Error: 2009-11-25 08:44:01.76 Code: 0xC00F9304 Source: TEExtract Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. End Error Error: 2009-11-25 08:44:01.76 Code: 0xC020801C Source: Non Staff Exp Chg to XLSX Excel Destination [73] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2009-11-25 08:44:01.76 Code: 0xC0047017 Source: Non Staff Exp Chg to XLSX SSIS.Pipeline Description: component "Excel Destination" (73) failed validation and returned error code 0xC020801C. End Error Error: 2009-11-25 08:44:01.76 Code: 0xC004700C Source: Non Staff Exp Chg to XLSX SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2009-11-25 08:44:01.76 Code: 0xC0024107 Source: Non Staff Exp Chg to XLSX Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:43:55 AM Finished: 8:44:01 AM Elapsed: 6.052 seconds. The package execution failed. The step failed.

  • November 30, 2009 at 11:02 pm

    #1085647

    Open the agent job. Go to the step which executes the ssis package. Then go to Execution options and change the flag "Use 32 Bit runtime"

  • December 1, 2009 at 6:28 am

    #1085737

    I have that box checked, but still doesn't work. Are you saying to uncheck?

  • December 1, 2009 at 7:32 am

    #1085791

    From what I've read about this issue, your job step needs to be set to Operating System (CmdExec), and you have to type the full path to the 32-bit version of ssis to run the pkg, complete with all the necessary parameters.

    MS has really missed the boat with how to use 64-bit systems and the basic interaction needed with certain key 32-bit things, such as drivers to access Excel and various text files.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 1, 2009 at 7:38 am

    #1085802

    where do i set that? In the Command Line tab of Management Studio? Do you have a link to any examples?

    Thanks!

  • December 2, 2009 at 8:00 am

    #1086509

    You can set it on the General tab of the "job step properties".

    Just type it in the command box.

    Example: "d:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe" /SQL "\<package name>" /SERVER <server Name> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

    Mike

    “I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey

    ___________________________________________________________________

  • December 2, 2009 at 8:56 am

    #1086581

    I continue to receive errors saying that it cannot connect to the Excel Source. However, the first sheet (of 6) is getting filled, then fails.

    Here's my code:

    "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /FILE "L:\SSIS Packages\SSIS2008TEExtract.dtsx" /MAXCONCURRENT "-1" /CHECKPOINTING OFF /REPORTING E /Decrypt "packagepwd"

  • December 3, 2009 at 6:01 am

    #1087017

    Is this Excel 2003 withthe 65K row limit?

    If so, I am not sure if SSIS can automatically open a new sheet.

    I am thinking that your connection is to a specific sheet (could be wrong on this).

    If that is the case, you may have to put a counter in your SSIS and then have it switch connections.

    I will look into this if I can get some time today.

    Mike

    “I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey

    ___________________________________________________________________

  • December 3, 2009 at 7:28 am

    #1087071

    This is Excel 2007

  • June 6, 2011 at 8:25 am

    #1334417

    Just to add to what Mike said....

    you have to have 32 bit version of drivers installed on server. when you install sql server with integration services, it doesnt install 32 bit version by default. You must select either Business Intelligence Development Studio or Management Tools - Complete during setup.

    http://msdn.microsoft.com/en-us/library/ms141766.aspx

    Saurin

  • October 13, 2011 at 9:29 am

    #1394151

    Thanks!

Viewing 15 posts - 1 through 14 (of 14 total)

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