SQL Server Performance Forum – Threads Archive

Intermittent Failure – Please Help

This is beginning to drive me crazy… I have a DTS package that fails intermittently. I constantly get a variation of the following error message: DTSRun OnError: DTSStep_DTSDynamicPropertiesTask_1,
Error = -2147220500 (800403EC)
Error string: OleDBProperty ‘Column Level Collation Support’ already exists in the collection. Every time it fails, it displays this message, only the OleDBProperty is often different. Examples: OleDBProperty ‘Network Address’ already exists in the collection.
OleDBProperty ‘Server Name’ already exists in the collection. etc. The worst part is that it only fails about 1/2 the time. Most of the time if I execute the package manually, it runs without any problems. All it does is truncate 2 tables and import/transform some data into them. It does use a global variable for the server name, but the rest of the packages in the set are implemented the same way and don’t seem to have this problem. I have even recreated the whole package from scratch, but I get the same error half the time, other wise it runs fine. I have searched the net but only found a few unanswered references to this problem. Has anyone seen this before? BTW, this is on a SQL 2000 SP3 cluster running W2K Advanced Server/SP3
-dataninja- "The normalcy of a database is inversely proportional to that of it’s DBA."

Are the steps corresponding to the dynamic properties tasks set to 1? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I am not sure about "set to 1", but the tasks go like this: (WF = workflow, XF = transform)
[change default params] –WF1–> [truncate tables] –WF2–> [source] –XF3/XF4–> [dest1/2] There are two separate transform tasks and two destinations with their workflow properties set accordingly: WF1 – source = [change default params], dest = [truncate tables]
WF2 – source = [truncate tables], dest = [XF3], dest = [XF4]
XF3/XF4 workflow props – source = [truncate tables], dest = [XF3]/dest = [XF4]

BTW how about privileges of SQLAgent account?
It seems its failing when dts is scheduled. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

It wouldn’t have been the SQLAgent because it worked sometimes, even when run as a job. However, I think I figured it out, thanks to your help. I looked in our QA environment and found a discrepancy between the workflow steps, and it looks like the version that was pushed to production did not have the correct workflow order! I added the dependency and it hasn’t failed since! [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Thanks for your help! -dataninja- "The normalcy of a database is inversely proportional to that of it’s DBA."

I am encountering a similar problem. The DTS package executes successfully most of the time, but occassionally receive the following errors: Error = -2147217887 (80040E21)
Error string: Multiple-step OLE DB operation generated errors. Error = -2147220500 (800403EC)
Error string: OleDBProperty ‘Packet Size’ already exists in the collection. If I restart the package, it executes successfully without having to make any changes. The package performs executes several SQL Tasks which execute one at a time using Success Workflows to seperate their execution. The final step extracts data from the database and creates two seperate text files. Any advise would be appreciated. Thanks,
Mitch

Just a thought. Have you been to www.sqldts.com yet? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

http://vyaskn.tripod.com/sql_server_dts_best_practices.htm
Check workflow properties, double check that the networking component MS DTC is enabled on the Server.
Also confirm SQL server edition and service pack level. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The very first step is a Dynamic Properties Task. The workflow for each step is set to execute on the Success of the previous step. The final step writes out two text files. When I check the properties, they list the Source and Destination steps as well as the Success Precedence. Is there anything specific I should check? It appears the package does not execute any of the steps so I am assuming the Dynamic Properties task is the one failing. I’m waiting on a response back from my DBA regarding if the networking component MS DTC is enabled on the Server and to confirm SQL server edition and service pack level. If the MS DTC was not enabled, would that explain this type of intermittent error? I had not previously looked on www.sqldts.com, but did this morning and did not find any information regarding this error. Thanks,
Mitch

]]>