DTS Missing Field

i've run into a problem with running a dts job that imports an access db
into sql located on same win 2003 server. the database has no relationships
and has
worked fine until i recently added a new field in a table in access.
crazy as it may sound, the dts job moves every field over fine except the
new field. I tested the job by creating a new table altogether and it
transforms
fine.
there is nothing special about the data type, etc. it's just another integer
field like other existing fields.
Where can I look in a log for a detailed description of a job? is it from
inside QA?
I should mention that my dts job is dropping and recreating each table in
sql 2000.Hi Scott,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
As my understanding of your information, you have a DTS package run as a
job. Then DTS package will import the data from a access database to an SQL
Server database. It runs fine in the past. Then you add a new column in the
Access database table then when the job runs, you found that the data in
the new add column is not imported to the SQL Server, right? If I
misunderstood, please feel free to let me know.
Based on my experience, after you design the DTS package, when there is
some modification on the table, such as add a column or delete a column,
it will be reflected in the old DTS package. However, the data transform
between the database should be specified. It could not be specified in the
DTS package automatically.
So, in your Enterprise Manager, please open the DTS package in the design
window, and right click the Transform Data Task ( which is the arrow link
the transformation data source( access database) and the data destination
(SQL server database)), choose 'Properties'. In the Source tab and the
Destination tab, you will notice that the new added column is there in both
table and then in the Transformation tab, you will see the transformation
of the columns between the source and the destination. Then, you will
notice that there are no transformation between new-added columns in the
source and destination. Please click the 'New' button and choose the 'Copy
column', then press OK. Then there is a new window titled 'Transformation
Options' prompt, you should specify the source column and the destination
column, which are the new added columns. Then, you could run the DTS
package and check if the DTS package execut and got the expected
transformation. Then you could run this DTS package as your job.
You could check what the transformation is doing by checking the properties
of the destination and the source and the transformation in the DTS package
design window and use the Profiler of SQL Server to catch what was
executed. For your case, I would recommend that you should check that if
the new-added columns' transformation is added in the DTS package.
Hope this helps and if you still have questions, please feel free to post
new message here and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Scott,
I am reviewing your post and since we have not heard from you for some
time, I wonder if my answer is helpful in solving your problem or if you
still have any questions that we could help. For any question, please feel
free to post message here and we are glad to help.
Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.