September 12, 2007 at 3:51 pm
We have a number of jobs (backup and data transfer) that were created by my predecessor using the DTS Wizard. The problem is that if anything changes (i.e., a referenced table is no longer needed and thus deleted) the job fails. Worse, if new tables are added, it appears that since they are not referenced, they are overlooked (not absolutely sure on that one). I was told by a Microsoft SQL Engineer that the only way to deal with DTS jobs is to create them with Dynamic SQL. For instance, from what he said, using Dynamic SQL, it is possible to include with each table referenced a statement “if exists” and if not write its absence to a Log File. In that manner, the job would complete, and there would be a record of Table A not present. It appears that it would also lend itself nicely to add new data sources to a job when new tables are added to the database.
That being said, a two-part question follows:
Part 1: I am relatively new to SQL (I had some training 7 or 8 years ago, but not much practice using it ohter than checking jobs and doing some trouble shooting, etc), and I wonder is there anywhere in the SQL 2000 application where I can find all the Stored Procedures and SQL statements that were used to create a DTS job with the wizard?
Part 2: If that information is available, what is the best source I can use to learn how to take that information and create the same job using Dynamic SQL.
Any help and suggestions would be greatly appreciated.
September 13, 2007 at 9:56 am
Hi Bob,
Stored procedures for creating and managing DTS packages are in msdb, but
the common tool for working with DTS is the GUI DTS Designer in Enterprise
Manager.
When the Import/Export Wizard is used to create a scheduled job, it also
creates a DTS package in msdb, which is executed by the job. You can
find local packages i.e. packages saved to SQL Server by expanding Data
Transformation Services and Local Packages in Enterprise Manager.
I think you'll want to use the Execute SQL task with a query that checks
for the existence of tables. It's not strictly dynamic SQL. Again, see
BooksOnLine. The site http://www.sqldts.com also has a ton of good information.
I'm curious about your statement that backup jobs were created with the
Import/Export Wizard. True database backups are a different animal than
DTS. I wonder if, in this case, "backup" refers to copying tables to
preserve the data?
Greg
Greg
September 13, 2007 at 10:27 am
Greg,
We had two separate issues over the past 3-4 weeks. One did involve a backup of a database (but I believe you are correct it is technically a copy from one server to another). In that instance, someone deleted a table that was no longer needed. That night the "backup" job failed. Running the job with a log of the process told me the name of the table, but that was it. When looking through the package, there was no mention of that table (which the package was apparently still looking for).
When setting up a DTS job with the wizard, the person designing the job picks and chooses what will be included in the job (ground level). From what I saw, it appears that the package view doesn't go down to ground level but more like a 1,000 foot view of the process. Thus my question: is there anywhere in the SQL application where one who did not set up the original job can see what steps were taken in the initial creation of that job?
With that said, and my understanding that there is another way to write these jobs so something like a deleting an unnecessary table will not cause the job to fail (and will at the same time leave a record of the table deletion). My guess is that I will need access to all of that information (from the original DTS wizard job) in order to begin writing the new one.
This process I am contemplating is the moral equivilent of having one swimming lesson and then being tasked with swimming the English Channel. Thank you for your input.
Bob
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply