Automate DTS Logging
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/automatedtslogging.asp
-
"With DTS package automation, we naturally turn to SQL-DMO.". We actually turn to the DTSPackage Object library as opposed to the SQLDMO Object library. SQL-DMO offers no DTS control at all.
Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd -
I like your article. I am constantly trying to learn more VBScript to use in DTS packages.
I ran it in our dev environment after copying some of our most complex DTS packages there. They were completely reorganized after I ran the script in your article. Forgive me if I ask a dumb question, but do you have any idea why it reorganizes the tasks in the package? I wonder if there is a way to keep it from reorganizing them.
Do you know where I can find the meaning of the error codes logged in sysdtssteplog?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
Good article, although I agree with darren.green, its the DTS Object model that reveals all.
I like your use of the EnumPackageInfos functionality. A number of people don't know about these extra bits. Pity there isn't much info to be found about how to use them effectively.
Also, it would be worth noting that after running your script, all the packages would be set to connect to the server using SQL Security. Therefore if you have the server setup to Windows Only, not only will you get an error when you save the package, you won't get any logs either!
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
quote:
I ran it in our dev environment after copying some of our most complex DTS packages there. They were completely reorganized after I ran the script in your article.As per the article "Another thing you will notice is that the visual layout of various package components will change after this is run, but the components remain the same. " this is expected behaviour. It is not nice, but a known limitation of using the DTS object model. When using the save methods (e.g. SaveToSQLServer) you loose the layout information and any annotations. The object model just throws this information away as it cannot maintain it, since this infromation is only available in the designer itself.
--
Darren Green
Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd -
quote:
Do you know where I can find the meaning of the error codes logged in sysdtssteplog?The errorcode column just logs the standard windows/dts error code. There is no definative list as they are defined it lots of different places. These are the same as you will see in the designer, or get example the VB(Script) Err.Number property.
Best thing I find is to just search the MS KB or Google Groups for the exact error number and see what comes up. The description is normally more meaningfull, but searching often works best on the number.
--
Darren Green
Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd -
Hi,
Thanks for reading my article and offering questions and comments and pointing out my mistakes.
I am in China on vacation now. I'll be able to better respond to your questions/comments when I come back, if they are not answered at that time.
Cheers and thanks again.
Haidong
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply