Let's Learn SSIS

Top 10 SQL Server Integration Services Best Practices

2012-01-21T19:39:00.001+05:30

Check out this URL for best practices for SSIS

http://sqlcat.com/sqlcat/b/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

Info: Sort and Merge Components vs. Lookup Component

2011-12-22T21:31:00.000+05:30

Worth reading this article..

http://consultingblogs.emc.com/jamiethomson/archive/2005/10/21/2289.aspx

Learn SSIS : SCD - Slowly Changing Dimensions transformation

2011-09-27T14:42:00.001+05:30

Slowly changing dimension transformation was one of the mystery transformation for me till now, as i never had chance to actually implement it. So today i block my time to implement this transformation by taking a simple example.

The concept of slowly changing dimension is explained very well and in detail on wikipedia, i would request readers to have a look at below link if you are not aware of SCD concept.

http://en.wikipedia.org/wiki/Slowly_changing_dimension

Before starting on our example, let me give you some glance of the SCD

transformation provided in SSIS 2008. This transformation is used to implement Type 1 and Type 2 SCD, for other types we need to add some custom logic to our ETL. (Type 1: Identify the changes and update the record, no history will be recorded, Type 2: Any change identified we expire the old record and create new record with new values, here we save history information in old record)

OK.. Lets take simple Employee dimension example... in this example i am getting EmployeeFeed.xls file as input for my DimEmployee table (which is my dimension table) and i am using SCD transformation to identify any changes and implement DimEmployee as Type 2.

DimEmployee :

Create table DimEmployee

(

EmpKey int identity(1,1),

EmpId int,

Name varchar(100),

Designation varchar(100),

City varchar(100),

Phone varchar(10),

StartDate datetime,

EndDate datetime

)

So before we start implementing any SCD, we need to first identify attribute in the dimension table for which we need to track changes. In this example i want to track changes for Designation, City and Phone attributes. I am expecting no change in Name attribute or column.

You might have noticed that there are two columns EmpId and EmpKey why these columns are needed in dimension table??

Ans:
EmpId : This is a Business Key, which uniquely identifies a employee in entire data warehouse system.

EmpKey : Is a Surrogate key, which uniquely identifies record in dimension table, and also its a key to identify historical records.

We also have two more columns StartDate and EndDate, these two columns are used to track time of changes, if EndDate is null it means the record is most recent record in dimension table.

Steps to Implement SCD in a data flow.

1. After we add Source (which is excel in our case EmployeeFeed.xls), we need to add Data conversion transformation, to correct if there are any Data type conflicts.
2. Then we add SCD transformation to Data flow, and this will open SCD wizard, Click next on welcome screen.

3. On Select a Dimensoin Table And Keys page, select your dimension table in this case its DimEmployee, Map all the columns from source excel to destination DimEmployee table. One important thing here we do is identify Business Key, which in our case is EmpID. Then click Next

4. On Slowly changing dimension columns page, we need to select appropriate change type of the Dimension Columns and here we have three types :
Fixed Attribute--> No change expected
Changing Attribute --> Changes are expected, but no need to record history, same record will be updated.
Historical Attribute--> If this attribute is changed, old record will be expired (by setting EndDate as current date) and new record will be inserted with new attribute value

In our example, we don't expect any change for Name Attribute hence we selected this as Fixed Attribute, and rest all (Phone,Designation and City ) will be selected as Historical Attribute. Once we are done Click Next

5. On Historical Attribute Options Screen, we have two option, we can use any flag column to show which record is expired and which is most recent and other option is to use StartDate and EndDate, in this example we are using second option, and also selected StartDate and EndDate column appropriately.

6. For all other screens in this wizard just select next, and on last screen select Finish.

That's it.. we implemented SCD transformation...your data flow should look like as shown below.

If you have noticed, we have two outputs from SCD transformation, New Output and Historical Attribute Output. So if there are any new records which are not present in dimension table those records will be redirected to New Output, and all existing records with some changing attributes will be redirected to Historical Attribute output.

Running the data flow..

I have 9 records in my sample EmployeeFeed.xls file..

So when i run my data flow for first time, all these 9 records will be redirected to New Output and will be inserted to DimEmployee Table.

Next, I did some changes in EmployeeFeed.xls, Changes are marked in yellow... so there are 4 records which are changed and 2 new records added.

If you can see the data flow, two records are redirected through New Output pipeline and 4 moved through Historical Attribute output, so what happens to those 4 records is we update the EndDate to latest date, then again insert them with new changed attrribute keeping EndDate as null. as shown below.

So that's it... hope you have learned something out of this ...

Thanks...

Learn SSIS : Expressions in SSIS - Part 2

2011-09-21T19:49:00.000+05:30

In my previous post i covered Event handler, in this i will be covering Expressions, and where we can actually set the expressions to extend SSIS package functionality.

Expressions

Expressions are used to set or evaluate property values at run time in SSIS package. It provides a great extending feature, based on runtime values or workflow status a property of Control flow or container or connection manager can be set.

Expressions are made up of identifiers, literals, functions, and operators. These elements are evaluated by SSIS and return a single data value which is set as property value.

SSIS Expressions are made up of following functions or elements...

Expressions are evaluated before the validation, and its a good feature evaluate properties of components in SSIS package at run time. A lot can be done usign expression and its out of scope of this blog post to cover all about expressions.

I hope this piece of information is usefull..

Thanks...

Mathematical Functions

The Mathematical Functions category provides support for complex arithmetic including logarithms, exponents, and absolute values.

String Functions

String Functions provide support for the manipulation of character data.

Date/Time Functions

Date/Time Functions allow manipulation and processing of date and time data.

NULL Functions

NULL Functions allow manipulation of NULL values.

Type Casts

Type Casts are functions that allow data to be changed from one type to another.

Operators

Operators denote various mathematical or logical operations.

Where actually Expressions are used?

In SSIS Expressions can be used with below list of elements...

1. Derived Column Transformation

2. Conditional Split Transformation

3. For Loop Container

4. Precedence Constraints

5. Properties

Learn SSIS : Event handlers, variables, expressions in SSIS - Part 1

2011-09-14T14:34:00.000+05:30

SSIS is not just about control flows and dataflows, there are other options available in SSIS which extends functionality of SSIS package, and provides a great flexibility in desiging a successfull ETL solution. Lets take a deep insight of what are those options and how we can take benefit of them.

There are four options which extends functionality of SSIS packages.

1. Event Handlers
2. Variables
3. Expressions
4. SQL Queries

In this post i will be covering Event handlers, and in later series i will cover Variables, Expressions and SQL queries

Event Handlers:

Like any programming languages (C# or JAVA) SSIS also provides Event handlers, which provides functionality of performing some task on a specific event during runtime. At runtime of any SSIS task, series of event takes place, and SSIS Desginer has provided with one tab (Event Handler) to program any action that can be performed on occurance of that event.

Event handlers are created in the same manner as control flow but in different tab as shown.

How we create events?

Its as simple as creating control flow of package.

1. Select the executable container to which the handler will be assigned.
2. Select the event to which you wish the event handler to react to.
3. Drag control flow container and task and connect them together with precedence constraints.

How Events are actually gets handled in SSIS?

Events can be handled at task or container or package level, following is the order it will be handled.

1. Task
2. Container
3. Package

For example: If an event is triggered at the task level and no event handler is defined, the event is passed to container level, if no event handler is defined at container level then it will be passed to package level.

How many events are available?

There are 12 different events that can be handled in SSIS package. Below is the list..

OnError The OnError event is caused by the occurrence of an error.

OnExecStatusChanged The OnExecStatusChanged event occurs when an executable changes its status.

OnInformation When an executable reports information during validation or execution, the OnInformation event occurs.

OnPostExecute OnPostExecute is an event that occurs immediately after an executable has completed running.

OnPostValidate
The OnPostValidate event occurs immediately after an executable has finished its validation.

OnPreExecute The OnPreExecute event occurs immediately before an executable runs.

OnPreValidate The OnPreValidate event occurs immediately before an executable begins its validation.

OnProgress
OnProgress is an event that occurs when an executable makes measurable progress.

OnQueryCancel The OnQueryCancel event occurs automatically at regular intervals to determine if the package should continue running.

OnTaskFailed When a task has failed execution, the OnTaskFailed event occurs.

OnVariableValueChanged
The OnVariableValueChanged event occurs when a variable's value is changed.

OnWarning The OnWarning event is raised when a warning occurs.

Event handlers have a number of properties that allow you to

• assign a name and description to the event handler
• enable or disable the event handler
• determine whether the package fails if the event handler fails
• determine the number of errors that can occur before the event handler fails
• override the execution result that would normally be returned at runtime
• determine the transaction isolation level for operations performed by the event handler, and
• determine the logging mode used by the event handler

Thats all.. I hope this peice of information was usefull.

Thanks..

Learn SSIS : Contol flow task categorization

2011-09-13T21:19:00.000+05:30

Like how we categorized data flow transformations in my previous post "Dataflow-transformation-categorization" here i have categorized various control flow tasks we have in SSIS.

The task have been categroized based on the functionality they provide in SSIS Control Flow.

Data Flow Task

The Data Flow Task category contains a single task that is used to extract data, apply column-level transformations, and load data in your SSIS package.

Data Preparation Tasks

Data Preparation Tasks allow you to copy files and directories, execute web methods, work with XML documents, and cleanse data in your SSIS packages.
Data Preparation Tasks include File System, FTP, Web Service, XML, and Data Profiling.

Workflow Tasks

Workflow Tasks are used in your SSIS packages to run other packages, programs or batch files, and send or receive messages between packages. These tasks can also send e-mail messages, read WMI data, and watch for WMI events.
Workflow Tasks include Execute Package, Execute Process, Message Queue, Send Mail, WMI Data Reader, and WMI Event Watcher.

SQL Server Tasks

SQL Server Tasks allow you to work with SQL Server objects and data in your SSIS packages.
SQL Server Tasks include Bulk Insert, Execute SQL, Transfer Database, Transfer Error Messages, Transfer Jobs, Transfer Logins, Transfer Master Stored Procedures, and Transfer SQL Server Objects.

Script Task

The Script Task category contains a single task that is used to execute Microsoft Visual Studio Tools for Applications, abbreviated to VSTA, scripts in your SSIS package.

Analysis Services Tasks

Analysis Services Tasks allow you to work with Analysis Services objects in your SSIS packages.
Analysis Services Tasks include Analysis Services Execute DDL, Analysis Services Processing, and Data Mining Query.

Maintenance Tasks, and

Maintenance Tasks allow you to perform administrative functions on the SQL Server in your SSIS packages.
Maintenance Tasks include Back Up Database, Check Database Integrity, Execute SQL Server Agent Job, Execute T-SQL Statement, History Cleanup, Maintenance Cleanup, Notify Operator, Rebuild Index, Reorganize Index, Shrink Database, and Update Statistics.

Backward Compatibility Tasks

Backward Compatibility Tasks are provided for backward compatibility with SQL Server Data Transformation Services or DTS (SQL SERVER 2000).
The two Backward Compatibility Tasks are ActiveX Script and Execute DTS 2000 Package.

In addition to the data flow tasks included with SQL Server 2008, you can also create custom tasks using any programming language that supports the Component Object Model or COM programming model.

Control flow containers provide structure to your package. They are used to group tasks together or allow one or more tasks to be repeated.

There are four types of control flow containers:

Foreach Loop

A Foreach Loop container executes its tasks once for each member of an enumerated collection. For example, the tasks in a Foreach Loop container could be executed once for every row in a table, or once for each file in a folder on the hard drive.

For Loop

A For Loop container executes its tasks repeatedly until a specified expression evaluates to false.

Sequence, and

A Sequence container simply creates a logical grouping of tasks that can be managed as a unit.

Task Host

The Task Host container is created for you automatically when you add a task to the Control Flow design surface in BIDS. This container contains only the task and allows container properties, such as checkpoints and transaction, to be configured on individual tasks.

Hope this consolidation helps ...

Thanks..

Learn SSIS : Dataflow Transformation Categorization..

2011-09-07T18:52:00.000+05:30

-->

Before anyone starts learning/designing data flow in SSIS, one should know what is been offered by Microsoft in SSIS toolbox. Most people don't know how many transformations are actually available in data flow and they choose some alternative workaround to performs operations which can be done easily using available transformations. In this article i am going to provide just a brief of all the transformations available in dataflow and categorise them. After reading this article one should be aware of what is been offered in SSIS designer, and it will be helpful for all those beginners to become aware of available transformations.

Also Categorization helps us to remember transformations, as there are lot.. its difficult to remember :)

Ok so lets start...

Transformations are divided into four categories

1. Row level transformations
2. Rowset transformations
3. Multiple Input-output transformations
4. Advance Transformations.

Row Level Transformations

Row level transformations performs operations on data row by row. These transformations are most common in use and can be configured easily. Following are the transformations which comes under this category.

Audit
Cache Transform
Character Map
Copy Column
Data Conversion
Derived Column
Export Column
Import Column
Row Count

Rowset Transformation

Rowset Transformation performs operations on multiple rows, these kind of transformations are memory intensive, but can be functionally very useful. Below are the list of transformations which comes under this category.

Aggregate
Percent Sampling
Pivot
Row Sampling
Sort
Un pivot

Multi input/output transformations
As the category name implies, these transformations requires multiple input and it generates multiple outputs.
These transformations provides functionality to combine or branch data and give the data flow the overall ability to process data from one or more sources to one or more destinations.

Below is the list of transformations which fall under this category..

Conditional Split
Lookup
Merge
Merge Join
Multicast
UnionAll

Advance Transformatoins
The transformations fall under this category provides ability to perform advance operations on data in pipeline of SSIS package.

OLE DB Command
Slowly Changing Dimension
Data Mining Query
Fuzzy Grouping
Fuzzy Lookup
Script Component
Term Extraction
Term Lookup

Thats all ... This all the transformations, and we have categorize them as well.. I hope this peice of information was helpfull...

Thanks..

Process Multi-Level XML in SSIS

2011-08-12T16:52:00.000+05:30

Process Multi-Level XML in SSIS from Ramu Poonjolai on Vimeo.

Running packages from SSMS

2011-08-11T15:54:00.001+05:30

SSIS package deployment made easy in Denali..!!

2011-08-11T00:29:00.000+05:30

SSIS in SQL SERVER Denali CTP3 (The First Look)

2011-07-23T13:28:00.000+05:30

SSIS in SQL SERVER Denali CTP3 (The First Look)

As many of us may be aware of that Microsoft has already released sql server codename denali Beta, which will be a next version of sql server which is projected to release in 2012. This post will be concentrating on what you will notice in first look of SSIS designer.

Up on opening the project, you will notice following changes…

1. Grouping of tasks and transformations have been shuffled or re-categorized, based on there usage.

2. For control flow task, a new task Expression Task is added.

3. In Data flow, you can new transformation that is DQS Cleansing, its Data quality Service.

4. They have added Source Assistant and Destination Assistant

5. You can see a new tab added as Parameters, which helps to define package level parameters this looks a easy way of passing parameters to packages.

6. The variable window has a new feature to change the scope of variable on a button click, which is quite useful.

7. You can see connection manager in solution explorer, which indicates it allows sharing of Connections across packages.

Yes there will be more improvements and or extra feature.. I add them in my upcoming blogs.

Thanks for reading..

Regards,

Learner..!!

Learn SSIS : What is SSIS ?

2011-07-22T20:49:00.000+05:30

What is SSIS? its a comman question been asked and searched on google and other search eagines, this indicates lots of people are looking for information on what exactly ssis is?

Well this post will cover the general defination of SSIS, and where exactly this technology is getting used..

SSIS stands for SQL SERVER Integration services, its an additional independent windows services which is basically used to manage ETL(Extract Transform and Load) operations or workflows in any datawarehousing solution. Yes you can it as a ETL development tool, using which we design the workflows and schedule it as per business needs. The business data movement is designed in the workflows(Packages) and then scheduled to automate the task.

The SSIS can be used in following scenarios:

1. Data movement across platforms

2. Data movement between different RDBMS (Oracel / DB2 / SQL SERVER/ Flat files)

3. Custom business workflow

4. Movement of data across applications

5. Perform Maintanence tasks on SQL SERVER

6. Perform Monitoring

7. Perform huge data movement like archieving or BULK Inserts etc

8. Looping over files or any dataset.

Etc..

So the advantage of using SSIS is :

For more complex processes, particularly those that involve heavy transforms, slowly changing dimensions, data mining lookups, etc, SSIS has three advantages.

First, it manages memory very efficiently, which can result in big performance improvements compared to T-SQL alone.
Second, the graphical interface lets you build large, complex and reliable transforms much more easily than hand-crafted T-SQL.
And third, SSIS lets you more easily interact with additional external sources, which can be very handy for things like data cleansing.

Features of SSIS: http://en.wikipedia.org/wiki/SQL_Server_Integration_Services
So how do i install SSIS??

its simple, SSIS comes along with SQL SERVER, so when you install SQL SERVER you can get an option to install SSIS services too, you can download SQL SERVER from http://www.microsoft.com/sqlserver/2008/en/us/express.aspx

Hope this post might be helpfull for biggners..

--The Learner..!!

SQL Server Code Name "Denali"

2011-07-13T21:56:00.000+05:30

Hi Friends,

SQL SERVER 2012 Beta is relaeased checkout below URL for details..

SQL Server Code Name "Denali"

Also you can download SQL SEVER Denali using below URL

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/

Also checkout a presentation video from microsoft..

--Thanks
The Learner..!!

Learn SSIS : For Each loop

2011-07-12T22:50:00.000+05:30

Welcome back..Today I am going to cover ForEach loop.
ForEach loop we use when we don’t know the number of iteration loop, lets take an example…
The scenario is in adventure works database we have a view vEmployeeDepartment, so what we are going to do is we will be generating files by department name.
Let’s start..
The package will look like this…

The execute sql task will get list of departments from the view, and the foreach loop will loop over the department list one by one and generate the files for each department.

1. Execute SQL Task: To get list of departments from the view

2. ForEach Loop: Loop over all the departments we got from Execute SQL task

3. Script Task: to form a T-SQL, which fetches department wise records from table

Here is the code that needs to be entered into Script task, its nothing but variable assignment.

public void Main()

{

// TODO: Add your code here

Dts.Variables["varSQL"].Value = "Select EmployeeID,FirstName,MiddleName,LastName,JobTitle,StartDate from HumanResources.vEmployeeDepartment where Department = '" + Dts.Variables["varDepartment"].Value.ToString() + "'";

//MessageBox.Show(Dts.Variables["varDepartment"].Value.ToString());

Dts.TaskResult = (int)ScriptResults.Success;

}

4. Dataflow Task: Execute the T-SQL we formed in above step and generate the files for each department.

5.Expressions: To set destination file name dynamically, we use expressions, which will change the connectionstring of destination connection.

6. Variables: We are using following three variables
      a. varDepartments: This holds department list we got from Execute SQL task
      b. varDepartment: This holds department name for every iteration of ForEach loop
      c. varSQL: Holds the T-SQL which we form in SCRIPT task

Well guys that‘s it, run the package and see the results at the destination directory, files of each department will be generated as shown…

Learn SSIS : MERGE, MERGE JOIN and UNION ALL

2011-07-09T11:51:00.000+05:30

Well its looks I really getting fond of writing blogs. I think everyone should at least give a try writing blogs once :)

Today I am going to study some of the Dataflow transformations, MERGE, MERGE JOIN, and UNION ALL.

Yes the first question any SQL SERVER developer can ask is, these operations can very well be done in T-SQL query, Why using it in SSIS?, well the answer is, T-SQL can be used only when you are working with data which resides in SQL SEVER Tables, but what if the data is coming from different database itself like ORACLE or MYSQL or Flat File or XML or EXCEL.

Let’s take it one by one each transformation, the post also covers differences, which are actually common questions, in any SSIS interviews.

Rather than reinventing the wheel, I am taking the text from MSDN for providing the description of each transformation which I found covering everything J

MERGE:

The Merge transformation combines two sorted datasets into a single dataset. The rows from each dataset are inserted into the output based on values in their key columns.

By including the Merge transformation in a data flow, you can perform the following tasks:

Merge data from two data sources, such as tables and files.
Create complex datasets by nesting Merge transformations.
Remerge rows after correcting errors in the data.

The Merge transformation is similar to the Union All transformations. Use the Union All transformation instead of the Merge transformation in the following situations:

The transformation inputs are not sorted.
The combined output does not need to be sorted.
The transformation has more than two inputs.

Well here is the example..here we are taking input from two different sql server databases and merging them into a flatfile.

And here is what we need to set in Merge Transformation properties

This transformation is pretty simple and straight forward.

MERGE JOIN: The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin. For more information, see Using Joins.

You can configure the Merge Join transformation in the following ways:

Specify the join is a FULL, LEFT, or INNER join.
Specify the columns the join uses.
Specify whether the transformation handles null values as equal to other nulls.

Note

If null values are not treated as equal values, the transformation handles null values like the SQL Server Database Engine does.

This transformation has two inputs and one output. It does not support an error output.

Here is the same example with merge join..

And the properties window looks like this, here we need to set which type of Join we need to perform the dataset(Left outer, right outer or inner) it’s the same what we use in any RDBMS.

UNION ALL:

The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output.

The transformation inputs are added to the transformation output one after the other; no reordering of rows occurs. If the package requires a sorted output, you should use the Merge transformation instead of the Union All transformation.

The first input that you connect to the Union All transformation is the input from which the transformation creates the transformation output. The columns in the inputs you subsequently connect to the transformation are mapped to the columns in the transformation output.

To merge inputs, you map columns in the inputs to columns in the output. A column from at least one input must be mapped to each output column. The mapping between two columns requires that the metadata of the columns match. For example, the mapped columns must have the same data type.

If the mapped columns contain string data and the output column is shorter in length than the input column, the output column is automatically increased in length to contain the input column. Input columns that are not mapped to output columns are set to null values in the output columns.

This transformation has multiple inputs and one output. It does not support an error output.

Even though I have taken all sources as OLEBD, you can take any type of source and output data to any type of destination.

Well guys that’s it for today, hope you guys have learned something.

--The Learner ..!!

Learn SSIS : For Loop in SSIS 2005/2008

2011-07-06T20:40:00.000+05:30

After lots of failure attempts finally I am confident enough to write my first post. It took like 3 years for me to start writing this first post. And you know what, its not that simple!!.

Anyways feelings aside, and lets starts something which is of our interest.

Yeah we gona start with Loops in SSIS, well there are two types of loops available in SSIS 2005/2008

FOR Loop : Use when you know the count of iteration of the loop
FOREACH Loop : Use when you don’t know the count of Iteration of loop

Now I am not going to put much text to read, or give you detailed information on architecture of loops or SSIS, rather we will be learning here by examples.

So lets start with For Loops: <I will cover ForEach loop in my next article>

Lets say the requirement is everytime I execute the loop in my ssis package, three records will be inserted into a table named LoopsExample. So we will start with

1. Create a table named LoopsExample as shown

Create table LoopsExample( Pkey int identity(1,1), IterationCount int, DateLooped datetime)

Whenever the SSIS loop executes, it inserts three new records into the LoopsExample table with PKey as primary key, IterationCount as iteration number just to know how the iteration values gets change, and DateLoooped will show the date the record is inserted into the table

2. So here what we do in SSIS designer

Create a package variable named @i
Drop a For Loop container into the SSIS Control flow and double click to open For loop Editor.
Drop Execute SQL Task into For Loop container.
Configure Execute SQL task.
Set Parameter Mapping tab.
Here is the query to use in SQL Statement in Execute SQL task

Insert into LoopsExample (IterationCount,DateLooped)

Values(?,getdate())

. That’s it, on executing the package we get following output ..

As Expected :) and I hope you have learned some thing out of this..

--By Learner..!!