Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate DTS Logging Expand / Collapse
Author
Message
Posted Friday, August 29, 2003 12:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/automatedtslogging.asp


Post #15806
Posted Tuesday, September 9, 2003 10:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 5, 2006 1:47 PM
Points: 69, 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
Post #77292
Posted Wednesday, September 10, 2003 9:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #77293
Posted Wednesday, September 10, 2003 5:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 29, 2014 4:53 PM
Points: 2,693, Visits: 1,207
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
Post #77294
Posted Thursday, September 11, 2003 12:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 5, 2006 1:47 PM
Points: 69, 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
Post #77295
Posted Thursday, September 11, 2003 12:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 5, 2006 1:47 PM
Points: 69, 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
Post #77296
Posted Sunday, September 14, 2003 8:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, 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



Post #77297
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse