What do i mean by shell packages? You’re spot on – that’s an excellent question to begin with. For the purpose of this post, I define shell package as an empty package that is renamed appropriately and contains all the required connection managers.
It is a common pattern among SSIS developers to create separate packages for each unit of work. For example, you will create a package for each table that needs to be loaded.
The only problem: manually creating hundreds of packages, sometimes even more, is time consuming and boring. If you think about it, first steps of creating all these packages is always the same: Right click on SSIS Packages, select New SSIS Package, rename the package, and create Connection Managers.
Wouldn’t it be nice if there is a way to automate these boring steps? I hear you screaming yes.
Package templates help you to some extent. You create a package with the required connection managers and save it as a template. The subsequent packages will automatically contain all connection managers the template has, you don’t have to add them manually to all packages. You still have to add and rename packages though.
Let’s take a look at a much better and time saving method!
I use BIML to dynamically create multiple SSIS packages for each table that needs to be loaded. All packages will be renamed consistently and will contain connection managers.
Demo:
Execute the following SQL to create source and destination databases.
CREATE DATABASE SrcDatabase ;
CREATE DATABASE DestDatabase ;
Next, create some tables in the destination database.
USE DestDatabase;
GO
CREATE TABLE dbo.DimProduct (
ProductKey INT IDENTITY(1, 1) PRIMARY KEY
,ProductCode VARCHAR(10) UNIQUE
) ;
CREATE TABLE dbo.DimCustomer (
CustomerKey INT IDENTITY(1, 1) PRIMARY KEY
,CustomerCode VARCHAR(10) UNIQUE
) ;
CREATE TABLE dbo.DimEmployee (
EmployeeKey INT IDENTITY(1, 1) PRIMARY KEY
,EmployeeCode VARCHAR(10) UNIQUE
) ;
Again, our objective is to create one package for each of the tables in the destination database.
Open BIDS, I’m using SSIS 2008R2 for this example. You’ll have to install BIDS Helper from here. Right click on the project and add New BIML File. Your solution explorer look like this:
Double click the BIML file and paste the following script. If you see issues while copy pasting, check this link.
<#@ template language=”C#” hostspecific=”true”#>
<#@ import namespace=”System.Data” #>
<Biml xmlns=”http://schemas.varigence.com/biml.xsd”>
<Connections>
<Connection Name=”SrcDatabase” ConnectionString=”Data Source=(local);Initial Catalog=SrcDatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI” RetainSameConnection =”true” DelayValidation =”false”/>
<Connection Name=”DestDatabase” ConnectionString=”Data Source=(local);Initial Catalog=DestDatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI;” RetainSameConnection =”true” DelayValidation =”false”/>
</Connections>
<Packages>
<# string DestCnStr =”Data Source=(local);Initial Catalog=DestDatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI”;
DataTable tables = ExternalDataAccess.GetDataTable(DestCnStr,”SELECT t.name FROM sys.tables t”) ;
foreach (DataRow row in tables.Rows)
{ #>
<Package Name=”Load_<#=row[0]#>” ConstraintMode=”Linear” >
<Tasks>
<Container Name=”Dummy — remove container” ConstraintMode=”Linear”>
<Tasks>
<ExecuteSQL Name=”Dummy — remove task” ConnectionName=”SrcDatabase”>
<DirectInput>select 1</DirectInput>
</ExecuteSQL>
<ExecuteSQL Name=”Dummy 1 — remove task” ConnectionName=”DestDatabase”>
<DirectInput>select 1</DirectInput>
</ExecuteSQL>
</Tasks>
</Container>
</Tasks>
</Package>
<# } #>
</Packages>
</Biml>
After copying the code, right click on the BIML file and select Generate SSIS Packages.
Bingo!
Multiple packages with connections managers have been created in a matter of minutes.
You just have to delete the sequence container and proceed with package development. Why? We created connection managers in the BIML script, but since we are not using them anywhere in the package, they won’t be included in the packages. So, I tricked it by creating a dummy task. This is still easier compared to the traditional process of creating multiple packages.
Don’t get me wrong. This example is probably the most basic of what BIML has to offer. I’m spending some time lately with BIML and I hope to do more posts as I learn new things.
Reference: http://bimlscript.com/Browse/Snippets
