Problems with dynamic connection string.

  • I've done this before and it worked. Not sure why not now. This is for loopping through a directory and processing XL files there. This is for an XLSX file.

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::File_Path] + @[User::Latest_File] + ";Extended Properties=""EXCEL 12.0 XML;HDR=YES";"

    It didn't like that one, but now I'm seeing that I should have preceded those internal double quotes with a \ and not another double quote.

    However, it's not liking this one either:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::File_Path] + @[User::Latest_File] + ";Extended Properties=\"Excel 12.0;HDR=Yes\";"

    Delay Validation is set to True.

    Here's the extended errors I'm getting. I see near the bottom it says "Could not find installable ISAM".

    Should I be using a different provider?

    ===================================

    Package Validation Error (Package Validation Error)

    ===================================

    Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    Error at Data Flow Task [SSIS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

    Error at Daily_CurrentSnapshots_DirectShip_CM [Connection manager "Excel Connection Manager 1"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".

    (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------

    Program Location:

    at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)

    at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)

    at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)

    at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

    at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

  • In your second connection string you used the JET OLE DB, while this should be the ACE OLE DB.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (5/20/2014)


    In your second connection string you used the JET OLE DB, while this should be the ACE OLE DB.

    Ok. I typed that in from a website that had that.

  • Ok, I went back to the ACE provider and things are working.

    Turns out the problem was that the "Latest_File" variable needs an initial "feeder" value that will be replaced by the script that gets the latest file name. I didn't have that in there, expecting it to take the value generated by the script, as the script runs first. I also turned validation off, but it ignored that.

  • nonghead-webspam (5/21/2014)


    Ok, I went back to the ACE provider and things are working.

    Turns out the problem was that the "Latest_File" variable needs an initial "feeder" value that will be replaced by the script that gets the latest file name. I didn't have that in there, expecting it to take the value generated by the script, as the script runs first. I also turned validation off, but it ignored that.

    DelayValidation is not ignored. Where did you configure it?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (5/22/2014)


    nonghead-webspam (5/21/2014)


    Ok, I went back to the ACE provider and things are working.

    Turns out the problem was that the "Latest_File" variable needs an initial "feeder" value that will be replaced by the script that gets the latest file name. I didn't have that in there, expecting it to take the value generated by the script, as the script runs first. I also turned validation off, but it ignored that.

    DelayValidation is not ignored. Where did you configure it?

    On the Excel Connection manager itself.

  • nonghead-webspam (5/22/2014)


    Koen Verbeeck (5/22/2014)


    nonghead-webspam (5/21/2014)


    Ok, I went back to the ACE provider and things are working.

    Turns out the problem was that the "Latest_File" variable needs an initial "feeder" value that will be replaced by the script that gets the latest file name. I didn't have that in there, expecting it to take the value generated by the script, as the script runs first. I also turned validation off, but it ignored that.

    DelayValidation is not ignored. Where did you configure it?

    On the Excel Connection manager itself.

    And what error does it give when SSIS ignores it?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • On the Excel Connection manager itself.[/quote]

    And what error does it give when SSIS ignores it?[/quote]

    ===================================

    Package Validation Error (Package Validation Error)

    ===================================

    Error at Load to Cache [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    Error at Load to Cache [SSIS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

    Error at Load to Cache [SSIS.Pipeline]: One or more component failed validation.

    Error at Load to Cache: There were errors during task validation.

    Error at Daily_CurrentSnapshots_DirectShip_CM [Connection manager "Excel Connection Manager 1"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "The Microsoft Access database engine cannot open or write to the file 'G:\Operations\DATA\Cco_Reports\Direct_Ship\'. It is already opened exclusively by another user, or you need permission to view and write its data.".

    (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------

    Program Location:

    at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)

    at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)

    at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)

    at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

    at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

  • It seems if fails at validating the source.

    What if you set DelayValidation to True on the package?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I'm stating the obvious, but does the account under which SSIS is running have permission to access that file? Do you (or someone else) have the file open in Excel already?

    Regards

    Lempster

  • Lempster (5/23/2014)


    I'm stating the obvious, but does the account under which SSIS is running have permission to access that file? Do you (or someone else) have the file open in Excel already?

    Regards

    Lempster

    Yes, it runs fine when I put the file name into the variable.

  • Koen Verbeeck (5/23/2014)


    It seems if fails at validating the source.

    What if you set DelayValidation to True on the package?

    I'll try. Thanks.

  • Ye It works. just make DelayValidation = True on your package.

    thank you for your help.

  • Wow, it took you a long time to test that! 😉

    Regards

    Lempster

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

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