Create Several Shell SSIS Packages In Minutes Using BIML

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:

image

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.

image

Bingo!

Multiple packages with connections managers have been created in a matter of minutes.

image

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.

image

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

@SamuelVanga

Generate Uniqueidentifier with SSIS

If you are trying to generate Uniqueidentifier/Newid() in SSIS data flow, you will soon realize that there is no out-of-the-box transformation you could use. Fear not. Script Component can be used to create Uniqueidentifier columns.

In the below package, I’ve an OLE DB source. Then, i dragged script component to the data flow. I selected transformation as the script component type and connected green arrow from source to script transformation.

Next, open the script transformation editor. In the Inputs and Outputs page, expand Output 0, select output columns and click add column in the bottom, give it a name, select unique identifier [DT_GUID] as the data type, and click ok. I named mine UniqueId.

SSIS NEWID() Script Add Column data type

In the script page, make sure Microsoft Visual Basic 2008 is selected as the scripting language. I just like working with VB. Click edit script, then copy paste the following script.

' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<microsoft.sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute()> _
<clscompliant(False)> _
Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.UniqueId = System.Guid.NewGuid()
End Sub
End Class

 

When you add a destination, UniqueId will be available as a new column in the mappings page.

SSIS UniqueIdentifier Mappins page New Column

@SamuelVanga

Book Review: SQL Server 2012 Integration Services Design Patterns

This book is written by Andy Leonard, Matt Masson, Tim Mitchell, Jessica Moss, and Michelle Ufford. These are all highly talented people we are talking about. Andy is constantly teaching SSIS patterns in his training classes and articles. Matt, for me, is the face of SSIS team at Microsoft. Same goes to other authors, they are common appearances in forums and community events helping people learn SSIS.

Within a few hours of reading SQL Server 2012 Integration Services Design Patterns, it stood out that none of the authors were trying to impress by showing what they all know in SSIS. Instead, they focused on describing solutions and patterns in a great detail (exactly why i paid for).

Each chapter is a collection of solutions and best practices to common data integration problems. For loading flat files go to chapter 7, for data warehouse patterns go to chapter 11. Each solution is written in detail with lots of pictures and step by step instructions. You could have the book open at work and follow through each step to solve a problem without running into any issues.

There is also pages and pages of code that you could immediately use. You’ll learn frameworks and metadata driven execution patterns. I didn’t find a companion cd or website from where you can simply copy and paste this code. That leaves you with having to type-in; a little depressing.

It is fun to read as well. I smiled when page 30 said VCR play button (referring to the debug button).Literature in the book is plain, clear, and casually written. It was like reading a blog post - simple and refreshing.

If you’re on a mission to learn everything about SSIS, I wouldn’t recommend this to you. This isn’t a know-all-SSIS book. Authors didn’t hide this fact either. Cover of the book says “Improve your efficiency as a data integration developer”. This focuses on patterns to data integration problems. If your job involves moving data using SSIS, this is a must have. SSIS 2012 Design Patterns teaches building faster, efficient, and reusable packages for your data integration needs.

I’m @SamuelVanga on Twitter.

SCRIPT: Basic Information About Indexes

I’m having a little fun with documenting basic information about indexes in my current project. I’m posting the scripts here mostly for me to come back when I need them in the future, but hopefully some of you might find them useful.

Find tables without any index:

This script gives you the list of tables that don’t have any index.

; WITH A
AS
(
SELECT SchemaName = OBJECT_SCHEMA_NAME(t.OBJECT_ID) ,
TableName = t.name
FROM   sys.tables t
WHERE  OBJECTPROPERTY(t.OBJECT_ID,'TableHasIndex') = 0
)
SELECT SchemaName ,
TableName ,
TwoPartName = SchemaName+'.'+TableName
FROM A

Find tables without a clustered index:

This script gives the list of tables without a clustered index.

; WITH B
AS
(
SELECT SchemaName = OBJECT_SCHEMA_NAME(OBJECT_ID) ,
TableName = OBJECT_NAME(OBJECT_ID)
FROM sys.indexes
WHERE index_id = 0
AND OBJECTPROPERTY(OBJECT_ID, 'IsUserTable') = 1
)
SELECT SchemaName ,
TableName ,
TwoPartName = SchemaName+'.'+TableName
FROM B
ORDER BY SchemaName

Find indexes on a table along with columns covered:

Use this script to get the index names and a comma separated list of columns included in the index.

; WITH C
AS
(
SELECT TableName = t.name
, IndexName = i.name
, ColumnName = c.name
FROM   sys.tables t
INNER JOIN
sys.indexes i
ON t.OBJECT_ID = i.OBJECT_ID
INNER JOIN
sys.index_columns ic
ON ic.OBJECT_ID = i.OBJECT_ID
AND ic.index_id = i.index_id
INNER JOIN
sys.columns c
ON c.OBJECT_ID = t.OBJECT_ID
AND c.column_id = ic.column_id
)
SELECT DISTINCT TableName, IndexName, ColumnNames
FROM cte A
CROSS APPLY
(
SELECT ColumnName + ', '
FROM cte B
WHERE A.TableName = B.TableName AND A.IndexName = B.IndexName
ORDER BY TableName, IndexName
FOR XML PATH('')
)
D (ColumnNames)

Notice that I generously used CTE’s here. I like to reuse column aliases instead of repeating length expressions everywhere. CTE allows you to do that.

@SamuelVanga

Parallel Execution in SSIS with MaxConcurrentExecutables

MaxConcurrentExecutables, a package level property in SSIS determines the number of control flow items that can be executed in parallel. The default value is -1. This is equivalent to number of processors (logical and physical) plus 2.

For example, in the below package running on my machine with 4 processors and MaxConcurrentExecutables = -1, you can see 6 tasks have completed execution and 6 are currently running. It’s executing 6 at a time because 4 processors + 2 = 6 threads.

maxconcurrentexecutables ssis

This applies to all versions of SSIS. Parallelism is powerful when your goal is to complete a process as quickly as possible, specially when the tasks in a control flow are independent of each other.

If you’re thinking of increasing this setting to an infinity hoping to achieve a Nobel prize in performance tuning… slow down. If the words throughput, threading, multi-tasking scares you, you should be careful with this property. In most cases, the default setting can get the job done just fine.

Follow me on Twitter.

@SamuelVanga

Generating Surrogate Keys With SSIS

A surrogate key is an auto generated value, usually integer, in the dimension table. It is made the primary key of the table and is used to join a dimension to a fact table. Among other benefits, surrogate keys allow you to maintain history in a dimension table. Despite of the their popularity, SSIS doesn’t have a built in solution for generating surrogate keys. Let’s take a look at a few alternatives in this post.

First, create the following table. We will import data from Person.Person table (AdventureWorks sample) into this table. Note PersonSK is the surrogate key.
CREATE TABLE [dbo].Person
(
[PersonSK] INT IDENTITY(1,1) NOT NULL
,
[FirstName] NVARCHAR(50) NULL
,
[LastName] NVARCHAR(50) NULL
,
CONSTRAINT PK_PersonSK PRIMARY KEY [PersonSK]
) ;

With IDENTITY()

Drag a data flow task on to the control flow and configure the OLE DB source.

Next, drag OLE DB destination and connect it to the source. Specify the connection manager, and choose Table or view – fast load as the data access mode. This performs a bulk insert and is the fastest of all.

Destination table has three columns, but source has only two columns. In the mappings page, map input and output columns for FirstName and LastName and ignore the mapping for PersonSK.

When you run the package, becasue PersonSK is an identity column in the table, SQL Server will automatically generate values for you. This solution is easy and fast, but sometimes depending on your ETL methodology, you can’t rely on IDENTITY().

With Script Component

I frequently use Script Transformation. The steps are nicely written in this post by Phil Brammer (b).  This is simply a script used as a transformation. The script generates a row number for each row passed through the data flow.

With ROW_NUMBER()

SSIS (data flow) engine generated the new row number when using script transformation. Instead, you can use ROW_NUMBER() when working with SQL Server data source to let the database engine do the work for you. This can be faster.

If you’re doing an incremental load, first find the maximum key value from the destination. I’ll use the following query. It’ll return zero if there were no rows, else it returns the maximum value.

SELECT  ISNULL(MAX(PersonSK),0) SK
FROM    dbo.Person

Add an Execute SQL Task to the control flow and set the result property to single row. Then, add a variable to hold the return value.

Next, connect a data flow task to the execute sql task. We will use the following SQL statement in the OLE DB source editor. In addition to the LastName and FirstName columns, we are using ROW_NUMBER() function to generate a unique number for every row.

SELECT
ROW_NUMBER() OVER(ORDER BY [LastName]) SK
, [LastName]
, [FirstName]
FROM
Person.Person

The query will generate numbers starting from 1 for each row, but while loading to destination we don’t want to start from 1. We want to find the maximum value in the destination and start from the next highest value. So, I’m adding the max value to every row number using parameters in the OLE DB source.


In the OLE DB destination, check the box that says Keep Identity. By do this we are asking SSIS to keep the identity values that are  generated by the source. In the mappings page, you’ll see a new input that was created in the OLE DB source. Map it to PersonSK surrogate key.

Go ahead and run the package. If it’s all good you will see cute little green tick marks like below.

Summary

In this example, we looked at different options to generate surrogate keys while loading dimensions using SSIS. We used IDENTITY() constraint in SQL Server. We talked about Script Component. Finally, we saw making use ROW_NUMBER() function. Last option is twice as fast as using the Script Component with around 20,000 rows and an index on LastName column.

Status Column in SSISDB Catalog Database

I occasionally review the search terms people use to get to this blog and secretly celebrate knowing my post helped them. If i know it didn’t, i add it to the list of items i should write about. This post is a result of one such thing.

Note: If you read my previous post about Logging in SSIS2012, please stop reading here. This doesn’t have any new information. I wrote this one first, but published the Logging post ahead of this and still publishing this to reach Googlers.

I assume you know about the SSISDB catalog database. Several tables/views have a column called [status] showing the status of an execution. It is an integer column with 1 through 9 as the possible values.

Description for these numbers isn’t available anywhere in the database but buried in books online like this.

Here is an example query you can use to translate the status numbers to descriptions.

USE SSISDB ;

SELECT
    
project_name  
     [status description]
    
= CASE [status]
              
WHEN 1 THEN 'created'
              
WHEN 2 THEN 'Running'
              
WHEN 3 THEN 'canceled'
              
WHEN 4 THEN 'failed'
              
WHEN 5 THEN 'pending'
              
WHEN 6 THEN 'ended unexpectedly'
              
WHEN 7 THEN 'succeeded'
              
WHEN 8 THEN 'stopping'
              
WHEN 9 THEN 'completed'
    
END
FROM  
[catalog] .[executions]
WHERE  execution_id = 30032 -- replace with your execution_id
;

 

SSISDB catalog execute status

Another example is with [Catalog].[Executable_Statistics]. Status column in this view shows the execution status of an executable. An executable is any package or a container or a task.

SELECT  e.package_name ,
e.[executable_name] ,
es.[execution_path] ,
es.[execution_duration] ,
[execution_result] = CASE es.[execution_result]
WHEN 0 THEN 'Success'
WHEN 1 THEN 'Failure'
WHEN 2 THEN 'Completion'
WHEN 3 THEN 'Cancelled'
END
FROM   
[catalog].[executables] e
INNER JOIN [catalog].[executable_statistics] es ON es.[executable_id] = e.[executable_id]
WHERE   e.execution_id = 40044

 

@SamuelVanga

Tips to improve your blog, really!

As I learnt a few things about blogging, I made a list of tips to get better at blogging. Thought I would share.

  1. Pour your heart out to answer a question. When someone asks a question, it means they followed your post hoping they would find an answer to their problem. If they are stuck, take responsibility. Do everything you can do to help them.
  2. Don’t plagiarise. It is just not the right thing to do.
  3. Don’t like your own posts. We know you like them, you don’t have to explicitly click like to show that.
  4. Don’t start with Hi or Hello. You’re writing a blog, not a letter.
  5. Don’t sweat it. Write what comes to you and how it comes to you.
  6. Be natural. You’ll be at your best when you are yourself.
  7. Use images. Images deliver the message quickly. If it’s a technical post, throw in lot of screen prints, else find a funny and relevant picture to use.
  8. Name your images. One extra opportunity do drop keywords for SEO. Search engines read image names and rank your post better. Posts also show up in image search.
  9. Use lists.
  10. Check spellings. There is no excuse for spelling mistakes.
  11. Check grammar. Difficult specially for people with foreign mother tongues (including me). It’s okay to be not perfect, but keep an eye on it.
  12. Check font. Inconsistent font size looks ugly. Will make the readers go away.
  13. Title is the key to a good post. Choose a title to attract people, not Google.
  14. Make use of URLs. Google will read URL text to show your posts in search results. People don’t read this. Feel free to use as many keywords as you want.
  15. Let the readers comment. Don’t post a comment saying you received so and so feedback from somebody.
  16. Be careful with series of posts. They are both powerful and dangerous. In depth series like this by Devin Knight, for example, will make the readers come back, a series on keyboard shortcuts won’t.
  17. Use your energy wisely. Blogging requires a lot of energy: time and thought. Use it and write to solve problems.
  18. Blog because you want to, not because someone else is blogging.

As always, comments are most welcome.

@SamuelVanga

Workspace database server was not found

You might constantly see a warning message that appears like the one below when creating Analysis Services Tabular projects. It basically says, workspace database server ‘ServerName’ was not found.

image

You’ll have to change this setting from the model properties. The server should be an Analysis Services Server running in Tabular mode.

image

You’ll have to deal with this every time. It’s such a pain. Right? Fixing it for good is easy. Simply click on Tools, go to Options and expand Analysis Services. Change default workspace server and default deployment server to an Analysis Services server instance that’s running in tabular mode.

image

image

image

~Sam.

How to execute a package from another package?

I hear you. You’ll use the Execute Package Task. This mechanism of executing one package from another is popularly knows as Parent Child paradigm. Let me tell you, this isn’t new in 2012. There are, however, a few changes to the Execute Package Task. Let’s take a look.

Demoland!

I added two packages to the project and conveniently named them Parent.dtsx and Child.dtsx.

Parent Child Packages SSIS 2012

Child Package

In the child package, I added a script task and used the following script. This will display a message box and tells that it is executing.

MsgBox(“Howdy! I’m the child package”)

image

Parent Package

In the parent package, I added an Execute Package Task and renamed it to Run Child Package.

image

In the Package page of the Execute Package Task editor, there is a new property called reference type. It is set to Project Reference by default. This means you can point the execute package task to another package within the same project. I selected Child.dtsx from the drop down.

image

The following is the output when I execute the parent package.

image

In prior versions, you’ll have to choose either file system or SQL Server as the source for child package and have connection managers to all the child packages in the parent. For example, if you have one parent package calling 50 child packages, the parent needs 50 connection managers to each of those child packages. This is still supported for legacy packages – change the reference type property to External Reference.

Passing a variable from parent to child

You often pass variables from parent package to the child package; connection strings, parent package start time, parent package name (ok. May be). Again, in the previous versions, you would use a parent package configurations to do this.

In this example, I’ll pass parent package name to the child package.

Another change to the Execute Package Task is parameter binding. You can map a parameter or a variable from the parent package to a parameter in the child package.

In the below image, I created a parameter in the child package. I wrote about SSIS parameters in an earlier post.

image

From the parent package, open the Execute Package Task Editor. In the parameter bindings page, I mapped the child parameter to the parent’s PackageName system variable.

image

Then I changed the script in the child package as follows:

MsgBox(“Howdy! I’m the Child Package. I was called by ” & Dts.Variables(“$Package::ParentName”).Value.ToString)

When I execute the parent package, I see…

image

Zip It

In this post, I looked at using Execute Package Task to call a package from another and pass variable from parent to child.

@SamuelVanga