Get the Major;Minor;Build Number for ETL packages

I was assigned one task to maintain all version numbers for our 90 packages. Possible?! I doubted it until I found this way.

Think about this, SSIS package is a XML-based file and SQL server works tightly with XML type. This is the basical thought.
By the way , if you deploy all packages into SQL server intead of file system you can fetch the version too however it is not the one in BIDS.

Back! How to fetch the version numbers from the packages deployed in the file system. Here you go,

sp_configure 'show advanced options', 1;
reconfigure;
GO
sp_configure 'xp_cmdshell', 1;
reconfigure;
GO

use tempdb
go
DECLARE @Path VARCHAR(2000);
SET @Path = 'C:\Sandbox\*.dtsx';

DECLARE @MyFiles TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, FullPath VARCHAR(2000));
DECLARE @CommandLine VARCHAR(4000) ;

SELECT @CommandLine =LEFT('dir "' + @Path + '" /A-D /B /S ',4000);
INSERT INTO @MyFiles (FullPath)
EXECUTE xp_cmdshell @CommandLine;
DELETE
FROM @MyFiles
WHERE FullPath IS NULL
OR FullPath='File Not Found'
OR FullPath = 'The system cannot find the path specified.'
OR FullPath = 'The system cannot find the file specified.';

IF EXISTS (select * from sys.tables where name = N'pkgStats')
DROP TABLE pkgStats;
CREATE TABLE pkgStats(
PackagePath varchar(900) NOT NULL PRIMARY KEY
, PackageXML XML NOT NULL
);

DECLARE @FullPath varchar(2000);
DECLARE file_cursor CURSOR
FOR SELECT FullPath FROM @MyFiles;
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @FullPath;
WHILE @@FETCH_STATUS = 0
BEGIN
declare @sql nvarchar(max);
SET @sql = '
INSERT pkgStats (PackagePath,PackageXML)
select ''@FullPath'' as PackagePath
, cast(BulkColumn as XML) as PackageXML
from openrowset(bulk ''@FullPath'',
single_blob) as pkgColumn';
SELECT @sql = REPLACE(@sql, '@FullPath', @FullPath);
EXEC sp_executesql @sql;

FETCH NEXT FROM file_cursor INTO @FullPath;
END
CLOSE file_cursor;
DEALLOCATE file_cursor;

SELECT SUBSTRING(PackagePath,LEN(PackagePath) - CHARINDEX('\',REVERSE(PackagePath),0)+2,LEN(PackagePath)) AS PackageName
, PackagePath
, CreatorName
, PackageFormatVersion
, PackageType
, PackageDescription
, VersionMajor
, VersionMinor
, VersionBuild
, VersionGUID
, COUNT(*) AS NumberOfTasks
FROM (
select PackagePath
, 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]','varchar(50)') AS varchar(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 char(38)) AS VersionGUID
, PackageXML
from pkgStats
) p
CROSS APPLY p.PackageXML.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)
GROUP BY PackagePath
, CreatorName
, PackageFormatVersion
, PackageType
, PackageDescription
, VersionMajor
, VersionMinor
, VersionBuild
, VersionGUID;

Advertisements

SSIS-Lookup-Partial Catch Sucks!

It is obvious that I am lazy. Not any update on my blog in the past one month.

Well, here I only wanna remind all SSIS developers to take consideration very seriously to use the “partial catch” in Lookup component in SSIS.

My experience learned from the project informs me this fact. It costs 9 hours to finish loading 12 million records to go through some 40 lookup component, one of which uses “partial catch”. By contrast, if changing to full catch mode, it only takes 40 minutes! Amazing~~it is true. I am extremely surprised by this.

Technically, why I would try the “partial catch”? Here is the case. There is a dimension table with a low boundary(min value) and a high boundary(max value) combined as a business key. As it goes, you wanna fetch the surrogate key for your fact data which flows into the pipeline. So what can we do? if using full catch, you lose the chance to modify the TSQL and you cannot do a range compare. One of options is to use the partial catch then modify the TSQL something like this select * from (select * from...) where ?>=minValue and ?=O.MinNumberOfDays and H.num<=O.MaxNumberOfDaysHowever, this is not a good option for the enterprise data warehouse.

Alternatively, it is highly suggested to use the full catch. How? using CTE or a “numbering” table to expand the range as row by row. Looks like below TSQL.(Personally, I like the way of the table one–to create a physical table to store sufficient numbers.)
select num=convert(int,num ), OrderAging_Key from
(select top 10000 rOW_NUMBER() over (order by O1.object_id)-2000 as 'Num'
from sys.objects O1 , sys.objects O2, sys.objects O3, sys.objects O4) as H inner join dbo.Dim_OrderAging O
on H.num>=O.MinNumberOfDays and H.num<=O.MaxNumberOfDays

Hope this thread helps.