May 20, 2014 at 5:57 pm
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)
May 20, 2014 at 11:44 pm
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
May 21, 2014 at 8:35 am
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.
May 21, 2014 at 12:53 pm
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.
May 22, 2014 at 12:09 am
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
May 22, 2014 at 10:58 am
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.
May 22, 2014 at 12:10 pm
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
May 22, 2014 at 1:46 pm
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)
May 23, 2014 at 1:22 am
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
May 23, 2014 at 2:04 am
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
May 23, 2014 at 5:45 am
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.
May 23, 2014 at 5:46 am
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.
January 19, 2016 at 10:52 pm
Ye It works. just make DelayValidation = True on your package.
thank you for your help.
January 20, 2016 at 1:15 am
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