SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate DTS Logging


Automate DTS Logging

Author
Message
Haidong Ji
Haidong Ji
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/automatedtslogging.asp



darren.green
darren.green
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 1
"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
Robert W Marda
Robert W Marda
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4459 Visits: 113
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
philcart
philcart
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15025 Visits: 1441
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

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
darren.green
darren.green
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 1
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
http://www.sqldts.com/

Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd
darren.green
darren.green
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 1
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
http://www.sqldts.com/

Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd
Haidong Ji
Haidong Ji
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 60
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search