October 31, 2016 at 7:02 am
The situation is:
Server A: runs alle the ssis jobs.
Excel is located on a fileserver.
Connection string Excel:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Fileserver\somewehre\Excelfile.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";
The job that runs the job with the ssis package uses a proxy.
The account that is used is: domain\ReportSources
This account has file authentication to the \\Fileserver\somewhere
This account has no privileges on server A.
The strange thing is when I add the account domain\ReportSources to the administrator group of Server A, the ssis package runs fine. When it is nog in the administrator group I get the following error.
Executed as user: domain\ReportSources. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 12:18:38 PM Error: 2016-10-31 12:18:39.89
Code: 0xC0202009 Source:
Connection manager "cm_Excel"
Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.
Source: "Microsoft Office Access Database Engine"
Hresult: 0x80004005
Description: "External table is not in the expected format.".
End Error Error: 2016-10-31 12:18:39.89
Code: 0xC020801C
Source: T_DFL_Process Excel Excel Source [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "cm_Excel" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error Error: 2016-10-31 12:18:39.89 Code: 0xC0047017
Source: T_DFL_Process Excel SSIS.Pipeline
Description: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
End Error Error: 2016-10-31 12:18:39.89
Code: 0xC004700C Source: T_DFL_Process Excel SSIS.Pipeline
Description: One or more component failed validation.
End Error Error: 2016-10-31 12:18:39.89
Code: 0xC0024107 Source: T_DFL_Process Excel
Description: There were errors during task validation. E
nd Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 12:18:38 PM Finished: 12:18:39 PM Elapsed: 1.826 seconds.
The package execution failed. The step failed.
So with if the account (from the proxy/credential) is admin on the server running the package, it works.
The file is on another server.
What do I miss?
October 31, 2016 at 7:10 am
Quick question, does the "user" have access to the share on the file server?
😎
October 31, 2016 at 10:37 am
Yes, the account does have access to the folder and the files in it.
November 1, 2016 at 1:18 am
It must have something to do with authentication/privileges on the Server A on which the SSIS package is hosted and executed by a SQL job.
Because when I elevate the account to Administrator I do not get an error. This means the account does have access to the file on the fileserver.
I reinstalled AccessDatabaseEngine, because I thought that interprets the excel file format.
That did not help.
November 1, 2016 at 4:50 am
spidey73 (11/1/2016)
It must have something to do with authentication/privileges on the Server A on which the SSIS package is hosted and executed by a SQL job.Because when I elevate the account to Administrator I do not get an error. This means the account does have access to the file on the fileserver.
I reinstalled AccessDatabaseEngine, because I thought that interprets the excel file format.
That did not help.
Is that a Domain Administrator account?
😎
November 1, 2016 at 6:27 am
No, the account is simple service-account without logon privileges.
The account has no windows privileges on server A.
November 28, 2016 at 4:26 am
I thought I found the cause of the issue but NO. But I still was astonished.
When you create a SSIS package, you create connection managers.
These connection managers and the values used during development are stored in the package.
When running the packages in a SQL job, we use dtsConfig files that supply the values for the connection managers.
To my surprise SQL falls back to these stored values when it cannot find/access the dtsConfig file without any warning or error.
The service account running the job had no folder privileges on the folder with the dtsConfig files and it used the stored values (which were in most parts still valid values) and not generating any error.
When i granted the privileges on the folder it did use the correct connectionsettings stored in the dtsConfig file.
very, very strange that SQL does nog give any hint that it cannot access the configured dtsconfig file.
November 30, 2016 at 12:06 pm
spidey73 (11/28/2016)
I thought I found the cause of the issue but NO. But I still was astonished.When you create a SSIS package, you create connection managers.
These connection managers and the values used during development are stored in the package.
When running the packages in a SQL job, we use dtsConfig files that supply the values for the connection managers.
To my surprise SQL falls back to these stored values when it cannot find/access the dtsConfig file without any warning or error.
The service account running the job had no folder privileges on the folder with the dtsConfig files and it used the stored values (which were in most parts still valid values) and not generating any error.
When i granted the privileges on the folder it did use the correct connectionsettings stored in the dtsConfig file.
very, very strange that SQL does nog give any hint that it cannot access the configured dtsconfig file.
Off the top, I would guess that would have something to do with how warnings and errors are configured to be handled. In the package are there any error handlers that you might have missed? I do recall messages in past work that alert me to missing dtsConfig files.
----------------------------------------------------
November 30, 2016 at 9:57 pm
This was removed by the editor as SPAM
December 5, 2016 at 12:10 pm
When I understand this correctly the Job doesn't work when run from a SQLAgent Job. Have you checked that the SQLAgent Service account has access to the share on the remote Server?
December 6, 2016 at 12:28 am
The job step runs with a proxy. The account used in the proxy/credential is a domain account with privileges on the folder, but NO privileges on the server on which the job is ran.
When I add the domain account to the admin group on this server, the job runs oke.
December 9, 2016 at 6:34 pm
spidey73 (12/6/2016)
The job step runs with a proxy. The account used in the proxy/credential is a domain account with privileges on the folder, but NO privileges on the server on which the job is ran.When I add the domain account to the admin group on this server, the job runs oke.
Great, so I figure you are okay now here. I still wonder about that issue where you got no indication that SSIS could not find a dtsConfig file. Did you figure out if you had event handlers inside of SSIS made to handle that?
----------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply