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