Create Package to run all packages in a Directory

gravatar

Steve Powell

The framework we use produces lots of packages, one for each table, for each stage of our load process. We tend to group these packages together in a MASTER package that runs all the packages for a load phase in parallel as ExecPackage tasks. This script generates the master package by searching a directory for all the DTSX packages in it.

published 06.25.13

last updated 06.25.13


Share

Tags

  • ExecutePackage Task
  • Files in Directory
  • Master Package
  • Nested Packages
                            


<#@ template language="C#" tier="1"#>
<#@ import namespace="Varigence.Hadron.Extensions.SchemaManagement" #>
<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>
<#@ import namespace="Varigence.Hadron.Extensions" #>
<#@ import namespace="Varigence.Languages.Biml" #>
<#@ import namespace="Varigence.Languages.Biml.Connection" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.IO" #>

<!-- This is the path to the directory that will be searched, all files in this dir that mnatch the file spec will be included, it must end with a \ character -->
<# var folderpath=@"D:\ThisDir\ThisSubDir"; #>
<!--	This only seraches the directory specified if you want to recurse down a directory tree 
		you need to change the SearchOption.TopDirectoryOnly value to SearchOption.AllDirectories
		throughout the script -->

<!-- This is the name of the package that will be produced -->
<# var pkgName=@"DIM_MASTER" ;#>

<!-- This is the wildcard search spec for the files that will be included in the generated package -->
<# var FileSpec=@"DIM_*.dtsx" ;#>
	
	
<Biml xmlns="http://schemas.varigence.com/biml.xsd">

	<Connections>
		<!-- Need to Build a file connection for each package we want to run-->
		<# foreach (var file in Directory.GetFiles(folderpath ,FileSpec, SearchOption.TopDirectoryOnly)) { #>
			<FileConnection Name="<#=Path.GetFileNameWithoutExtension(file)#>_FileConn" 
							FileUsageType="ExistingFile" 
							CreateInProject="false" 
							FilePath="<#=folderpath#><#=Path.GetFileNameWithoutExtension(file)#>.dtsx"/>
		<#  } #>
	</Connections>
		
	<!-- Start of the package we are going to build-->
	<Packages>
		<Package Name="<#=pkgName#>" ConstraintMode="Parallel">
			
			<!-- Package level vars -->	
			<Variables>
				<!-- var for holding the base path that we will use to link all the connections -->	
				<Variable DataType="String" Name="local_Base_Path_For_SSIS_Pkgs" Namespace="User">
					<#=folderpath#>
				</Variable>	
			</Variables>
				
			<!--	There's no logging in the master package we are creating. It is assumed the sub packages have logging enabled.
					If not you could add a logging framework here whihc would track the packages that started, succeeded and failed. -->
				
			<Connections>	
				<!-- Create a Package connection for each of the sub packages we will access -->
				<# foreach (var file in Directory.GetFiles(folderpath ,FileSpec, SearchOption.TopDirectoryOnly)) { #>	
					<Connection ConnectionName="<#=Path.GetFileNameWithoutExtension(file)#>_FileConn" >
						<!--	The connectionString property will be built dynamically using the local_Base_Path_For_SSIS_Pkgs VAR. 
								This enables all the sub packages to be relocated. A change to the local_Base_Path_For_SSIS_Pkgs VAR 
								to the new path will relink them all correctly. This makes the master package a bit more portable.-->
						<Expressions>
							<Expression ExternalProperty="ConnectionString">@[User::local_Base_Path_For_SSIS_Pkgs] + "<#=Path.GetFileNameWithoutExtension(file)#>.dtsx"</Expression>
						</Expressions>
					</Connection>
				<#  } #>	
							
			</Connections>
					
			<Tasks>
				<!-- Now build an ExecPkg task for every package we want to include-->
				<# foreach (var file in Directory.GetFiles(folderpath ,FileSpec, SearchOption.TopDirectoryOnly)) { #>
					
					<!-- Quick trap/test to make sure we are not putting a link to the package we are building in
							This would be bad as it would recurse into itself and probably blow up at some point. -->
					<# if (Path.GetFileNameWithoutExtension(file) != pkgName) { #>
						
						<!-- The Exec Task itself is rally simple. You could adjust settings here if you needed to -->
						<ExecutePackage Name="<#=Path.GetFileNameWithoutExtension(file)#>">
							<File ConnectionName="<#=Path.GetFileNameWithoutExtension(file)#>_FileConn"/>
						</ExecutePackage>	
						
					<#  } #>	
				<#  } #>
			</Tasks>
				
				
		</Package>
	</Packages>
</Biml>		 
                        

The framework we use produces lots of packages, one for each table, for each stage of our load process. We tend to group these packages together in a MASTER package that runs all the packages for a load phase in parallel as ExecPackage tasks.

This script generates the master package by searching a directory for all the DTSX packages in it.

This script has three variables defined in it, the path to the directory that holds all the files you want to include in the package. A name for the package that will be generated and a wildcard filename specification that can filter the packages in the directory you specify.

The generated package has a variable in it which allows for all the paths to the sub packages to be repointed to a new location should the files move.

The package it generates has no dependencies between the sub packages. You would need to add any such dependencies manually after the package was created if you need them.

The BIML itself is fairly simple, the scripting that iterates over the files in the directory isn't exactly rocket science but it's a bit of an obscure use we are putting it to.

Hopefully someone will find this useful. Any feedback or suggestions would be appreciated.

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

Mihir

1:54pm 08.11.14

Hi Steve,

Nice solution !!!

Is it possible to generate a Dimension Master package, with ReferenceType for Execute Package Task set to Project Reference? and if you can provide with some example

Thanks Mihir