Derived Column could not be created. 0x80070005 "Access is denied."
-
I have a set of SSIS packages which import Microsoft Access database tables in SQL 2008 R2, which are loaded and executed by an ASP NET webpage. Both the webpage and the packages are hosted on machine A, while the SQL server is hosted on machine B.
The first package which just deletes one of the SQL table's contents loads and executes fine, but the remaining packages which deal with the Access to SQL importing will load, but do not execute.
After logging the onError is as follows:
"Derived Column Customers, clsid {49928E82-9C4E-49F0-AABE-3812B82707EC}" could not be created and returned error code 0x80070005 "Access is denied.". Make sure that the component is registered correctly.
Prior to this I had to allow 32-bit applications on the application pool in order to prevent a compatibility issue.
After reading similar issues it seems likely it is a permission issue, but I have ensured the following domain accounts all have write, read, and execute permission in the locations of both the dtsx packages and the Access databases.
* The ApplicationPool identity account
* SQL Server Agent (MSSQLSERVER)
Any help would be extremely appreciated. I can give any more information if it will help
Edit:
The package is located in the file system of the application server. It was developed on a different machine, and ran fine on the development machine.
After trying to run the package on the deployment machine using DTEXEC.exe I got the following error message.
Error: 2015-01-21 09:44:54.59
Code: 0xC0011002
Source: {6819ED7D-7F44-4251-A809-90B5A9A56581}
Description: Failed to open package file "C:\NewLaRR\AccessToSQLLaRR\68to68\D
elete Items.dtsx'" due to error 0x800C0006 "The system cannot locate the object
specified.". This occurs when loading a package and the file cannot be opened o
r loaded correctly into the XML document. This can be the result of specifying a
n incorrect file name when calling LoadPackage or the specified XML file has an
incorrect format
.
-
Where is you package is located ? if its exists on the application server, then try to run this package manually on that server.
if the package run successfully, and fail when try to run it from the asp.net file than, its a permission issue most probably unable to access the file.
try to give the aspnet user account the rights for that particular folder and run the package and see if its working or not.
-
twin.devil (1/20/2015)
Where is you package is located ? if its exists on the application server, then try to run this package manually on that server.if the package run successfully, and fail when try to run it from the asp.net file than, its a permission issue most probably unable to access the file.
try to give the aspnet user account the rights for that particular folder and run the package and see if its working or not.
Edit: This seems to be happening for all packages, even the one which executes okay from the ASP NET site.
The package is located in the file system of the application server. It was developed on a different machine, and ran fine on the development machine.
After trying to run the package on the deployment machine using DTEXEC.exe I got the following error message.
Error: 2015-01-21 09:44:54.59
Code: 0xC0011002
Source: {6819ED7D-7F44-4251-A809-90B5A9A56581}
Description: Failed to open package file "C:\<File Path>\D
elete Clients.dtsx'" due to error 0x800C0006 "The system cannot locate the object
specified.". This occurs when loading a package and the file cannot be opened o
r loaded correctly into the XML document. This can be the result of specifying a
n incorrect file name when calling LoadPackage or the specified XML file has an
incorrect format
.
I copied the file path directly from windows explorer, and the NETWORK SERVICE account has permission, so I'm not entirely sure why this is happening.
-
try to place the packages on another driver like D,E etc and execute the dtexec Utility again. as per error it is a security issue. as dtexec unable to read the dtsx file.
if you have two version of the sql server installed on your server then you can also check this Link
-
twin.devil (1/21/2015)
try to place the packages on another driver like D,E etc and execute the dtexec Utility again. as per error it is a security issue. as dtexec unable to read the dtsx file.if you have two version of the sql server installed on your server then you can also check this Link
I moved the packages to a different drive and redid the dtexec. Unfortunately I got the same error as above. The command I used was as follows:
C:\Program Files\Microsoft SQL Server\110\DTS>dtexec.exe /f "G:\Delete Items.dtsx"
So it should be explicitly using the correct utility executable. I temporarily gave the folder "full permission" to "Everyone" alongside the NETWORK SERVICE to try and reduce any permission issues, but to no avail.
-
try to open the cmd.exe as administrator on the server and then execute statement you have mentioned earlier.
However, When you run the dtexec utility its run under the current user permission. its a bit different when you execute the dtsx under aspnet or sql server agent.
-
The delete package executed successfully through dtexec as administrator.
The others gave me the same error as before, unfortunately:
OnError Data Flow Task Customers,{25CD0553-6250-4448-AAD3-E011CE618292},{0710E248-236C-47B8-A19F-2466DF32282C},22/01/2015 10:12:41,22/01/2015 10:12:41,-1073450900,0x,Component "Derived Column Customers, clsid {49928E82-9C4E-49F0-AABE-3812B82707EC}" could not be created and returned error code 0x80070005 "Access is denied.". Make sure that the component is registered correctly.
It's odd. The package executes fine on the development machine.
Edit: I can confirm the other packages work on the development machine using the dtsexec method (As well as through debugging the ASP NET site)
-
diversion.dummy (1/22/2015)
The delete package executed successfully through dtexec as administrator.The others gave me the same error as before, unfortunately:
OnError Data Flow Task Customers,{25CD0553-6250-4448-AAD3-E011CE618292},{0710E248-236C-47B8-A19F-2466DF32282C},22/01/2015 10:12:41,22/01/2015 10:12:41,-1073450900,0x,Component "Derived Column Customers, clsid {49928E82-9C4E-49F0-AABE-3812B82707EC}" could not be created and returned error code 0x80070005 "Access is denied.". Make sure that the component is registered correctly.
It's odd. The package executes fine on the development machine.
ok its mean dtexec is running properly, can you share the structure details of the package which is failing right now? it would be helpful to narrow down the issue.
-
Here's the data flow of the package. The other packages are the same type of structure. (Except the delete package which is working)
Let me know if you want more information, or if I misunderstood
-
okay, you are using ADO.Net source.
which type of source your are using oracle/access/PostgreSQL ????
-
I'm using an Access DB. ADO NET was used in the previous version of this package, and seems to be the only data source type that allows custom data source and workgroup file functionality.
On the ASP NET site a user uploads an Access file, it is stored on the application machine, and the package references that location.
-
As far as i can understand your delete package is running because the source and destination is RDBMS(MS QL SERVER) but in the package you are having issue you have two different connect one for ms access and other for SQL SERVER. Please verify if you have installed the MS Access driver properly on the application server.
-
The MS Access driver seems to be properly installed. I did a repair just in case but it didn't seem to make a difference. Another application on the same machine relies on the access driver working, so it should be a good indicator that it is installed correctly.
I just spent some time ensuring the DSN was pointing to the correct location, and changing that didn't affect the logging at all, even though the temporary change I made stopped the package from working on the development machine.
-
ok try this
1) create a copy of the package which is giving error on your dev environment.
2) Remove the OLE DB Destination.
2) Replace the Derived Column Customer with Rowcount. can you check it how to use Row count here
3) Run and test it on DEV Machine.
4) take that package and run it the Application server.
5) run the package with dtexec.
if the there is no error means, Derive Column Customer is the culprit here. which could mean that you are using a custom Derive Column transformation and reference dll are missing when you deployed it on the server.
-
twin.devil (1/22/2015)
ok try this1) create a copy of the package which is giving error on your dev environment.
2) Remove the OLE DB Destination.
2) Replace the Derived Column Customer with Rowcount. can you check it how to use Row count here
3) Run and test it on DEV Machine.
4) take that package and run it the Application server.
5) run the package with dtexec.
if the there is no error means, Derive Column Customer is the culprit here. which could mean that you are using a custom Derive Column transformation and reference dll are missing when you deployed it on the server.
Ah, I didn't explain what the Derive Column does; sorry I should have said earlier.
I'm using the Derive Column to insert a new column into the table. the variable is taken from the ASP NET Session (String, DT_WSTR).
I couldn't seem to get Rowcount to replace this functionality?
Is there another component I could use to replace Derive column so I can follow the process you described above?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply