Just a quick one – here’s some code that will generate a master package from the data in your msdb.syspackages table. It’s very generic and doesn’t use any custom metadata tables or objects. It’s standalone code that should successfully execute as is.
I usually use a modular approach for loading a data warehouse. I construct one package per target object and aim to have them as independent as possible. This allows me to re-use the same package for multiple workflows. I can use many methods to control the workflow, but usually it takes the form of a master package that is predominantly made up of Execute Package tasks.
It doesn’t take long for for the number of packages in even a small project to start to add up. And manually creating the master package is not a good use of anyone’s time!
The code has been tested in 2008R2 and 2012 (package deployment model only) and assumes you’ve used the SQL Server as your package store. It parses the msdb SSIS Packages table and creates a sequence container for the specified root and every child folder.
An Execute Package task is created in parallel mode in every sequence container that points to the packages that exist in that folder in your SSIS repository. There are two variables that need to be changed. The sServer variable which refers to the SQL server instance that the SSIS packages are deployed to, and the sRootFolder which refers to the SSIS repository folder that holds the packages you want in your master package. It includes all child folders and adds any packages they contain.
<# String sServer = @".\sql2k12";#> <!-- Change this to the server holding the MSDB database-->
<# String sRootFolder =@"";#> <!-- Change this to the root SSIS folder for which to generate master package.
Packages in this folder and all child folders will be included in the master package-->
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data" #>
<# String sConn = String.Format("Provider=SQLNCLI10;Server={0};Initial Catalog=msdb;Integrated Security=SSPI",sServer); #>
<# String sSQL = String.Format(@"
;with cte as (
SELECT pf.foldername AS RootFolderName
, pf.folderid AS FolderID
, convert(nvarchar(4000),pf.FolderName+'\') AS FolderPath
, pf.FolderName AS FolderName
, cast(null as sysname) as ParentFolder
from msdb..sysssispackagefolders pf
WHERE pf.foldername = '{0}'
union all
SELECT cte.RootFolderName AS RootFolderName
, pf.folderid AS folderid
, convert(nvarchar(4000),cte.FolderPath+pf.foldername+'\') AS FolderPath
, pf.FolderName as FolderName
, cte.FolderName AS ParentFolder
from msdb..sysssispackagefolders pf
INNER join cte
on cte.folderid = pf.parentfolderid
)
select RootFolderName AS RootFolderName
, pf.FolderPath AS FolderPath
, pf.ParentFolder AS ParentFolder
, pf.FolderName AS FolderName
, sp.name as PackageName
, ROW_NUMBER() OVER (PARTITION BY pf.FolderPath ORDER BY sp.Name ASC) AS StartId
, ROW_NUMBER() OVER (PARTITION BY pf.FolderPath ORDER BY sp.Name DESC) AS EndId
from cte pf
left join msdb..sysssispackages sp
on sp.folderid = pf.folderid
Order By FolderPath, PackageName
",sRootFolder); #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="SSIS Package Store" ConnectionString="Data Source=<#=sServer#>;Initial Catalog=msdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS Package Store;" />
</Connections>
<Packages>
<Package Name="Master Package" ConstraintMode="Parallel" AutoCreateConfigurationsType="None">
<!--Tasks-->
<Tasks>
<# DataTable tblPackages = ExternalDataAccess.GetDataTable(sConn,sSQL); #>
<# foreach (DataRow row in tblPackages.Rows){ #>
<# if(row["StartId"].ToString() == "1") { #>
<# if(row["FolderName"]=="") { #>
<Container Name="\" ConstraintMode="Parallel">
<# } else { #>
<Container Name="<#=row["FolderPath"]#>" ConstraintMode="Parallel">
<#}#>
<# if(!row.IsNull("PackageName")) { #>
<Tasks>
<# }
} #>
<# if(!row.IsNull("PackageName")) { #>
<ExecutePackage Name="Execute <#=row["PackageName"]#>">
<SqlServer ConnectionName="SSIS Package Store" PackagePath="\<#=row["FolderPath"]#><#=row["PackageName"]#>" />
</ExecutePackage>
<#}#>
<# if(row["EndId"].ToString() == "1") { #>
<# if(!row.IsNull("PackageName")) { #>
</Tasks>
<# } #>
</Container>
<#} #>
<# }#>
</Tasks>
</Package>
</Packages>
</Biml>
Create a new BIML file in your solution, paste the code in and just specify the connection string to your MSDB database and the root folder (inclusive) in your SSIS package store of the packages you want to be part of the master package.
An Execute Package task is created in parallel mode in every sequence container that points to the packages that exist in that folder in your SSIS repository. It should create a package with a separate sequence container for each folder and every package placed in parallel mode in the relevant sequence container. There’ll also be a connection to your ssis package store.
It doesn’t do nested folders but feel free to add it in – it’d need either a recursive TSQL function or a recursive .NET function hacked into the BIML code. BIML has no .Net debugger and intellisense doesn’t work, so writing any kind of complex .net code is just asking for trouble I think.
This is the type of task that I see myself using BIML for a lot more; automating the tedious, simple parts of SSIS package and solutions. Manually creating a execute package task takes probably close to a dozen clicks and mouse drags that simply pick things off a list. This is what scripting like this excels at. More complex logic like precedence constraints or automating SSIS creation…. It doesn’t take long for the helper scripts to become more complex, harder to debug and take longer to maintain than the files they create.
Say I have a project I’ve deployed to a MasterDataLoad directory which I specify as my sRootFolder. The BIML code will query the following SSIS repository:
and output the following package:
Now all I’d need to do is rearrange it , rename or remove any sequence containers I don’t need and add any precedence constraints specific to the workflow I’m creating.
No comments:
Post a Comment