locked
How to get number of tasks per DTS package? RRS feed

  • Question

  • Hi,

    I have around 20 DTS packages in my SQL Server 2000 instance. I would like to know a system table/view which gives me a snapshot of each package and its associated tasks.

    Please let me know.

    Regards,
    Phani Note: Please mark the post as answered if it answers your question.
    Wednesday, October 14, 2009 12:15 PM

Answers

  • Let me put the actual question. We would like to estimate how much time it takes per package for upgrading it from 2000 to 2005. Can you suggest the best way out to figure out this?

    Regards,


    Phani Note: Please mark the post as answered if it answers your question.

    No, there is no way of working this out. It depends on many many variables, mainly what do the tasks actually do.
    Another factor will be the skill level/experience of the person/people doing the upgrade.

    It is impossible to estimate given the paucity of information here so I won't even try.

    http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
    Wednesday, October 14, 2009 12:49 PM

All replies

  • Wednesday, October 14, 2009 12:19 PM
  • Thanks for the reply Jamie. I would like to know sysdtstasklog if can be used for this purpose. Based on what I understand, it gives one row per task of each package when it is executed. There is a risk that the specific path in the package if doesnt get executed atleast one, it will not come into this table.

    Please clarify.

    Regards,
    Phani Note: Please mark the post as answered if it answers your question.
    Wednesday, October 14, 2009 12:25 PM
  • Thanks for the reply Jamie. I would like to know sysdtstasklog if can be used for this purpose. Based on what I understand, it gives one row per task of each package when it is executed. There is a risk that the specific path in the package if doesnt get executed atleast one, it will not come into this table.

    Please clarify.

    Regards,
    Phani Note: Please mark the post as answered if it answers your question.

    Exactly right. So the answer is still "no".

    http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
    Wednesday, October 14, 2009 12:30 PM
  • Let me put the actual question. We would like to estimate how much time it takes per package for upgrading it from 2000 to 2005. Can you suggest the best way out to figure out this?

    Regards,


    Phani Note: Please mark the post as answered if it answers your question.
    Wednesday, October 14, 2009 12:33 PM
  • Let me put the actual question. We would like to estimate how much time it takes per package for upgrading it from 2000 to 2005. Can you suggest the best way out to figure out this?

    Regards,


    Phani Note: Please mark the post as answered if it answers your question.

    No, there is no way of working this out. It depends on many many variables, mainly what do the tasks actually do.
    Another factor will be the skill level/experience of the person/people doing the upgrade.

    It is impossible to estimate given the paucity of information here so I won't even try.

    http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
    Wednesday, October 14, 2009 12:49 PM
  • Hi,

    I have around 20 DTS packages in my SQL Server 2000 instance. I would like to know a system table/view which gives me a snapshot of each package and its associated tasks.

    Please let me know.

    Regards,
    Phani Note: Please mark the post as answered if it answers your question.

    Hi,
    The following query might help. It returns the name and type of every task in the package (including those in the eventhandlers if there are any):

    SELECT	Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
    							./p1:Property[@p1:Name=''ObjectName''][1]','nvarchar(max)') as TaskName
    ,		Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
    							./@p1:ExecutableType','nvarchar(max)') as TaskType
    FROM	(
    		select	cast(pkgblob.BulkColumn as XML) pkgXML
    		from	openrowset(bulk 'C:\tmp\Package.dtsx',single_blob) as pkgblob
    		) t
    CROSS	APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
    							//DTS:Executable[@DTS:ExecutableType!=''STOCK:SEQUENCE''
    						and	@DTS:ExecutableType!=''STOCK:FORLOOP''
    						and	@DTS:ExecutableType!=''STOCK:FOREACHLOOP''
    						and not(contains(@DTS:ExecutableType,''.Package.''))]') Pkg(props)
    

    All you need to do is replace 'C:\tmp\Package.dtsx' with the path to one of your own packages!

    HTH

    @JamieT

    http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
    Sunday, October 18, 2009 10:36 AM
  • Thank you so much Jamie. I will get back to you once I go through the information that you have provided.

    Regards,


    Phani Note: Please mark the post as answered if it answers your question.
    Monday, October 19, 2009 9:06 AM