How to get number of items within each data flow tasks
I have not idea, I'm responding because I'm just curious as to WHY anyone would need to know the NUMBER of Tasks on a Data Flow? Are you a Manager and need to see how efficient your Developers are at building SSIS Packages? Are you a Consultant that gets paid by the number of lines of code? Really, how important is it to know the number of items? Number of Data Rows handled during a package execution, sure, but this? You are under no obligation to respond to this, as I said, I'm just curious.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
March 18th, 2011 11:01pm

Hi, We can get number of dataflow task in a SSIS package using a SQL like below : SELECT PackagePath, COUNT(*) AS NumberOfDataflows FROM @pkgStatsBase p CROSS APPLY p.PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; //DTS:Executable[contains(@DTS:ExecutableType,''.Pipeline.'')]') Pkg(props) GROUP BY PackagePath Where @pkgStatsBase is something like : insert into @pkgStatsBase select PackagePath , CAST(PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable[1]/DTS:Property[@DTS:Name=''DTSID''][1]','nvarchar(500)') AS uniqueidentifier) AS PackageID , PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable[1]/DTS:Property[@DTS:Name=''CreatorName''][1]','nvarchar(500)') AS CreatorName , CAST(PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable[1]/DTS:Property[@DTS:Name=''PackageFormatVersion''][1]','varchar(3)') AS smallint) AS PackageFormatVersion , CAST(PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; DTS:Executable[1]/@DTS:ExecutableType[1]','nvarchar(50)') AS nvarchar(50)) AS PackageType , PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable[1]/DTS:Property[@DTS:Name=''Description''][1]','nvarchar(2000)') AS PackageDescription , CAST(PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable[1]/DTS:Property[@DTS:Name=''VersionMajor''][1]','varchar(3)') AS smallint) AS VersionMajor , CAST(PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable[1]/DTS:Property[@DTS:Name=''VersionMinor''][1]','varchar(3)') AS smallint) AS VersionMinor , CAST(PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable[1]/DTS:Property[@DTS:Name=''VersionBuild''][1]','varchar(3)') AS smallint) AS VersionBuild , CAST(PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable[1]/DTS:Property[@DTS:Name=''VersionGUID''][1]','char(38)') AS uniqueidentifier) AS VersionGUID , PackageXML from pkgStats Now can someone please let me know, how could I get number of items/tasks within one dataflow? Regards-Koushik
Free Windows Admin Tool Kit Click here and download it now
March 18th, 2011 11:04pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics