There is a asbout 200 mb of data 40 tables, all the data is being transformed forom one data model to another,
the tasks balk on 4 tables.
table a - 1 table 90000+ records lots of data this would be 30% of the db 3 table joing into 1
table b - 1 table 1228 records nothing much- 3 table join into 1
table c - 1 table 40000+ records nothing much as well 2 table join into 1
table d - 1 table 25000+ records nothing much this table is the same as table b - 3 table join merge into 1
although the one table with the bulk of tha data 40% transforms properly with no issues 225000+ records
I am testing for null on every field that could be null.
The data is being transformed from MSSQL to MSSQL.
All the transformation tests work correctly, and parse correctly
I have created separate packages for the remaining tables with still the same problems.
Right the DTS logging doesn't seem to checkpoint until completion so it's often unreliable.
So try this....and probably easier than activex logging ...if you're still trying to identify the step, then log to the database - i.e. create an Audit table, and before each step, log the current time and step # using a straight insert/update. This way you can control the logging.
It also sounds like you haven't looked at the running processes while your DTS is running - see Ent Mgr under Management -> Current Activity -> Locks/ Object and Locks/Process ID - that will probably show you some locking you didn't expect and tells you exactly your problem.
You can also use the SQL Profiler tool to watch batch-by-batch what the db is doing, when all else fails; but the previous steps should be enough.
You're into the hard part - getting it to work well! Good luck -
the activex looging seems to be reliable, I am testing on the first step and the last step and the log file says it's progressing.
I may just be a little impatient I've been working on this for 5 straight days and getting tired, 90% the way though I appreciate the tips.
When you check for null, what do you do, exclude it from your export, or put a dummy value in (e.g. "n/a" )?
I bet this will be causing performance issues either way if you are implementing the check using ActiveX.
The good news is that this can be easily (and not to mention - efficiently) remedied in SQL. The bad news is that it may require to re-write some of your DTS.