We had a SQLServer Agent/SSIS Job scheduled and running on a test server and everything was fine until we created a script to recreate it for Production from the Microsoft SQL Server Management Studio (right click on the job->Script Job As->Create To).
As soon as we had the job scripted, we dropped the job in test and Recreated it in test.
Of course, now we can't get the thing to run.
Here's some of the error's we're getting:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.5000.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 3:40:01 PM
Error: 2011-09-15 15:40:05.77
Code: 0xC0202009
Source: USDecoratedCatalogGroup Connection manager "SourceConnectionOLEDB"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2011-09-15 15:40:05.77
Code: 0xC020801C
Source: Data Flow Task Source - Query [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" 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: 2011-09-15 15:40:05.77
Code: 0xC0047017
Source: Data Flow Task DTS.Pipeline
Description: component "Source - Query" (1) failed validation and returned error code 0xC020801C.
End Error
Error: 2011-09-15 15:40:05.77
Code: 0xC004700C
Source: Data Flow Task DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2011-09-15 15:40:05.77
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:40:01 PM
Finished: 3:40:05 PM
Elapsed: 3.89 seconds
Now, I can still run these .dtsx packages fine on my local development machine, however as soon as I copy them to the test box, and attempt to run them from the SQL Server Job Activity Monitor, it throws the errors above.
I've read in some posts on this problem that there is a 64 or 32 bit setting problem, however not sure how to check that.
It seems to me that you're missing an OLE DB driver (provider) on the server you're talking about. Or the OLE DB driver your package uses to communite with the source system was not installed properly. If you search for 0x80040154,
you will see some links from microsoft talking about registering some DLL files using regserver32.
I guess if you can identity the OLE DB driver you may try to
update/fix it, or register its DLLs.
I'm not sure that makes sense, since has been running successfully for weeks and there are several other packages on that box that are also running without any problem.
Sometimes when you a package from one server to another, the package stops running. The reason is that the package saves passwords and encrypts them using specific keys to the machine on which it was first developed. So when you move the package to a different
server, the package cannot decipher the connection strings and cannot connect to servers. This is a possibilty.
There are some issues if you're running on 64-bit Windows.
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.5000.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:24:46 AM
Error: 2011-09-16 11:24:49.06
Code: 0xC0202009
Source: USDecoratedCatalogGroup Connection manager "SourceConnectionOLEDB"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2011-09-16 11:24:49.06
Code: 0xC020801C
Source: Data Flow Task Source - Query [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" 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: 2011-09-16 11:24:49.06
Code: 0xC0047017
Source: Data Flow Task DTS.Pipeline
Description: component "Source - Query" (1) failed validation and returned error code 0xC020801C.
End Error
Error: 2011-09-16 11:24:49.06
Code: 0xC004700C
Source: Data Flow Task DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2011-09-16 11:24:49.06
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:24:46 AM
Finished: 11:24:49 AM
Elapsed: 2.312 seconds
drdexter33
Contributor
2289 Points
889 Posts
SSIS Problem->SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004...
Sep 15, 2011 08:31 PM|LINK
We had a SQLServer Agent/SSIS Job scheduled and running on a test server and everything was fine until we created a script to recreate it for Production from the Microsoft SQL Server Management Studio (right click on the job->Script Job As->Create To).
As soon as we had the job scripted, we dropped the job in test and Recreated it in test.
Of course, now we can't get the thing to run.
Here's some of the error's we're getting:
Now, I can still run these .dtsx packages fine on my local development machine, however as soon as I copy them to the test box, and attempt to run them from the SQL Server Job Activity Monitor, it throws the errors above.
I've read in some posts on this problem that there is a 64 or 32 bit setting problem, however not sure how to check that.
Thanks... Doug
FileFoundExc...
Contributor
2633 Points
428 Posts
Re: SSIS Problem->SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x...
Sep 15, 2011 10:56 PM|LINK
It seems to me that you're missing an OLE DB driver (provider) on the server you're talking about. Or the OLE DB driver your package uses to communite with the source system was not installed properly. If you search for 0x80040154, you will see some links from microsoft talking about registering some DLL files using regserver32.
I guess if you can identity the OLE DB driver you may try to update/fix it, or register its DLLs.
drdexter33
Contributor
2289 Points
889 Posts
Re: SSIS Problem->SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x...
Sep 16, 2011 01:26 PM|LINK
I'm not sure that makes sense, since has been running successfully for weeks and there are several other packages on that box that are also running without any problem.
Thanks.
Doug
drdexter33
Contributor
2289 Points
889 Posts
Re: SSIS Problem->SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x...
Sep 16, 2011 03:12 PM|LINK
anyone?
FileFoundExc...
Contributor
2633 Points
428 Posts
Re: SSIS Problem->SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x...
Sep 16, 2011 03:33 PM|LINK
Sometimes when you a package from one server to another, the package stops running. The reason is that the package saves passwords and encrypts them using specific keys to the machine on which it was first developed. So when you move the package to a different server, the package cannot decipher the connection strings and cannot connect to servers. This is a possibilty.
There are some issues if you're running on 64-bit Windows.
For more check this post http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/11/09/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx
It might be easier if you post the error all of it because there might a clue here or there.
drdexter33
Contributor
2289 Points
889 Posts
Re: SSIS Problem->SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x...
Sep 16, 2011 03:37 PM|LINK
Well here's all of it.
I'll keep digging. Thanks
drdexter33
Contributor
2289 Points
889 Posts
Re: SSIS Problem->SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x...
Sep 20, 2011 05:26 PM|LINK
The only way I was able to fix this was to create new connections for each package.
We were using 2 for each of 8 packages.
One for the database connection
One for the Excel destination file.
Thanks everyone.
Doug
</div>