Strange behavior on Excel Connection when using Package Configuration

  • I have a package that works perfectly. The beef of the package takes several tables and imports them into excel. It was created by a developer using the wizard.

    If I take a copy of the package and generate a new ID I can make changes and it still runs fine.

    The minute that I enable package configurations so that I can make changes from the Config table without opening the package I get errors. This happens independent of the agent. One thing that makes me wonder is the password field on the Excel connection. I've tried leaving it off as well but had no luck.

    Error: 2009-06-22 15:40:02.59

    Code: 0xC0202009

    Source: PackageName Connection manager "DestinationConnectionExcel"

    Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

    An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

    End Error

    Error: 2009-06-22 15:40:02.61

    Code: 0xC00291EC

    Source: Preparation SQL Task Execute SQL Task

    Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection.

    End Error

  • Jason, are you in a 32 bit or 64 bit environment? There are known issues with the 64 bit Jet driver. I doubt if that's the only issue since it seems to work with package configurations disabled, but it's worth a look.

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • I've done battle with similar stuff. I can't put my finger on a link right now, but the way I'd approach this is to use an OLE DB Destination instead of an Excel Destination. OLE DB is less "touchy" and it'll run in 64-bit.

    Apologies for the brevity...

    Hope this helps,

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • It's on x64 but I'm calling it with DTExec to get around that:

    "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "D:\SSISShare\package.dtsx" /CHECKPOINTING OFF /REPORTING V

  • Andy Leonard (6/23/2009)


    I've done battle with similar stuff. I can't put my finger on a link right now, but the way I'd approach this is to use an OLE DB Destination instead of an Excel Destination. OLE DB is less "touchy" and it'll run in 64-bit.

    Ok, I'm looking into that.

    Don't have that provider option and I'm looking to see if this download is the right one.

  • Hi Jason,

    There's a way to configure the connection string property of an OLE DB Connection Manager that will allow it to connect to Excel. You can then write to the spreadsheet via an OLE DB Destination adapter.

    Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • i think that the import/export wizard did a poor job of creating the package.

    the data flow task is the recommended task for the insertion of data into excel, not the execute sql task. usually, an ole db source component is used to extract the data and the excel destination is used to load the data.

    hth

  • The execute SQL Task that I'm having trouble with is before actually putting data in.

    I've created the connection fine, but when I try to execute SQL against it like the following small snippet:

    CREATE TABLE `GroupREG` (

    `KeyID` Long,

    `CostGroupCode` LongText,

    `CostPoolDesc` LongText,

    `ActivityCode` LongText,

    `SalaryPerActivity` Currency,

    `SalaryPercent` Double,

    `Amount` Currency

    )

    GO

    CREATE TABLE `GroupSEC` (

    `KeyID` Long,

    `CostGroupCode` LongText,

    `CostPoolDesc` LongText,

    `ActivityCode` LongText,

    `SalaryPerActivity` Currency,

    `SalaryPercent` Double,

    `Amount` Currency

    )

    GO

    I get the following:

    #Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

    PackageStart,servername,username,Package1,{B1DAFB1E-E84B-464E-9633-E6C250348268},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,0,0x,Beginning of package execution.

    Diagnostic,servername,username,Package1,{B1DAFB1E-E84B-464E-9633-E6C250348268},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,0,0x,Based on the system configuration, the maximum concurrent executables are set to 10.

    Diagnostic,servername,username,D:\SSISShare\Allocation.xlsx,{2279A28E-2A9D-462B-BDDF-5B8B541DDADA},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.

    Diagnostic,servername,username,D:\SSISShare\Allocation.xlsx,{2279A28E-2A9D-462B-BDDF-5B8B541DDADA},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The external request has completed.

    PackageEnd,servername,username,Package1,{B1DAFB1E-E84B-464E-9633-E6C250348268},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,1,0x,End of package execution.

    Throughout all the settings, I can't see where the issue is. There's no username or password on the file. The file is local. I tried blank password with no luck either.

    Googling now.

  • Andy Leonard (6/23/2009)


    Hi Jason,

    There's a way to configure the connection string property of an OLE DB Connection Manager that will allow it to connect to Excel. You can then write to the spreadsheet via an OLE DB Destination adapter.

    Andy

    I've been able to do this using that download that I found. There's still the whole 64bit problem when using that provider. When I ran it in 32 bit mode it ran successfully the first time but the file wouldn't open because it said it was corrupted. Subsequent runs fail.

    Still digging. Please pass on any links if you find them.

  • Have you tried using separate execute SQL tasks for each Create statement? I don't know if the Excel provider allows batches...

    Also, you say that this fails only when you enable package configurations. Have you confirmed that the package is being configured how you think it is? I know this seems obvious, but it might be worth setting up a msgbox to display the value of your configured items just to make sure the package is getting the expected values. The msgbox won't work with SQL Agent, but should do if you execute using DTExec on your dev machine.

    If I remember correctly, the create table statement will create a range in excel. If the range is already defined, then this might causing an error too.

    Kindest Regards,

    Frank Bazan

  • Frank Bazan (6/23/2009)


    Have you tried using separate execute SQL tasks for each Create statement? I don't know if the Excel provider allows batches...

    It won't even do it with just one.

    Frank Bazan (6/23/2009)


    Also, you say that this fails only when you enable package configurations. Have you confirmed that the package is being configured how you think it is? I know this seems obvious, but it might be worth setting up a msgbox to display the value of your configured items just to make sure the package is getting the expected values. The msgbox won't work with SQL Agent, but should do if you execute using DTExec on your dev machine.

    yes, I've made changes and then looked at the configuration table. The changes show up there.

  • More ideas for you...

    If you are trying to write to Excel and you have IMEX set to 1 in your connection string, your excel file will be in read-only mode.

    You said it was independent of SQL Agent, so I assume you've checked your privileges to the file... but may be the original developer set the encryption level in a way that is causing your login problems when you run the package?

    Have you checked the logic that creates the excel file? How is the file name derived? If you are copying from a template, does the copy process occur correctly? Does the package get as far as creating the new file? If so, when you open the file in excel, is the named range you are trying to create already there? If it is you won't be able to create it again without deleting the existing range beforehand.

    If you shut down the package and reload with package configuration set to true, you should find all the configured values in your design environment. You can check these all evaluate correctly by running the package again with breakpoints set up and locals window displayed.

    I've run out of ideas now you'll be pleased to hear 😉

    Cheers

    Frank

    Kindest Regards,

    Frank Bazan

  • All great ideas, but don't seem to fit with this one.

    The configuration failure issues are moot at this point as I'm dealing just getting it to work with using an ODBC connection instead of ExcelDestination.

    I finally have it putting the data into the sheet, but it doesn't format like it used to. I have to actually create the xlsx file first, and then be sure to use Excel 12.0 XML instead of just Excel 12.0 or it won't work.

    I'm about to put it aside for a few minutes as it's driving me crazy at this point.

  • the excel source and destination components weren't designed to work with excel 2007 (yet another quirky "gotcha" in ssis). the work-around is use the ole db source and destination components with the Jet ACE 12 OLE DB Provider. more info is provided at the following link: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/e9cca2de-0ee6-4468-8302-f817e0746eb9/[/url] make sure that "Excel 12.0" is included in the extended properties of the connection string.

    hth

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

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