How To Create SSIS Package, Example With Diagrams

In this post we will try to crate a package that extract the data from 'Student Table' of Source-Database and load it successfully in 'Student Table' of 'Destination-Database'. You can Download or look into the sample database I am using.

System requirements for SSIS or Business intelligence Development:

1. You should have installed SQL Server Standard or Enterprise version.
2. I am using Visual Studio Add-in (SQL Server Data Tools) in this SSIS tutorial to design SSIS package. This needs SSDT also to be installed.

Once confirmed that SSDT is installed on machine. We are ready to create an ETL(Extract - Transform - Load) package.

So here we go!

Here are the scripts that you can run in order to generate two databases(Source and destination) with Tables in it.

Go to Start Screen or Click Start Button. Go to Microsoft SQL Server and you will find 'SQL Server Business Intelligence Development Studio' under this.

Open SQL Server business Intelligence Development Studio

This opens a instance of Visual Studio. Click on File tab and select 'New Project'. Chose 'Business Intelligence Projects' as project type and select 'Integration Services Project', type a sensible name for your project(I have given it Simple Data Transfer, that's what it does.). you can chose a location and whether you want to create a separate directory for your project under selected hard-drive location.

Selecting  project template for SSIS using visual studio.

This opens a Package.dtsx file on visual editor. There are 2 sections on left, and 2 on right side of Editor which are:

1. Variables: Here variables can be defined that can later used during execution of ETL or package. user should mention a appropriate scope for the variable defined.
2. ToolBox: As normal toolbox control of visual studio, contains all the necessary tools to crate a package under SSIS
3. Solution Explorer: Hierarchy of the folders of the current project.
4. Properties: Shows available properties for selected module/component from editor.

There are also 4 tabs on editor window namely

1. Control Flow: The main container where everything else resides.
2. Data Flow: Data Flow is the main component when we have to transfer data.

Other than these there are 2 more: Event Handlers and Package Explorer(skip for now).

Open Toolbox and double click 'Data Flow Task' or drag-drop it on editor window. This is the only task needed for this 'Simple Data Transfer' ETL Project. Double click on the 'Data Flow Task' in editor and it opens a editor for selected Data Flow Task.

Double Click the 'Data Flow Task'. Select 'OLE DB Source' from toolbox and drag it to editor. as we have to transfer the data from a SQL Server Database.

Note:

1. Flat File Source should be chosen when source is flat file.

2. Excel Source should be chosen when source is excel file. 

Double Click the 'OLE DB Source' component to configure it. Here click on 'Connection Manager' menu and select an available connection. If it seems empty in here and no connection is available then click on 'Cancel' button.

On 'Data Flow' editor, there is a 'Connection Manager' section at bottom. Right Click on this section and select 'New OLE DB Connection...'

This opens a 'Connection Manager' window. Select the server here and connect to a database selecting it from drop-down box. To log on to server either use windows Authentication or use SQL Server Authentication credentials of the user you are logging in with. Click 'OK'. This will create a new connection manager to connect with.

Now 'OLE DB Source' can be configured using this new connection. select the appropriate connection from list and say 'OK' pressing the button obviously :)

Once done with setting-up the database connection for source, chose the table to extract data from.(Student in this case). Click 'OK' and we are done with 'OLE DB Source' configuration.

Now drag-drop a 'OLE DB Destination' component from toolbox on editor window, click on 'OLE DB Source' and connect the Green Line to 'OLE DB Destination.' And Configure the destination database same as we configured source database.(just select destination databse while you select database and not source)

Now its the time to disco (mapping actually). Let us now map the columns of source and destination tables. Click on 'Mapping' menu in 'OLE DB Destination' configuration, and there will be 2 tables connecting by some links to appropriate columns.

Click on the first link connecting IDs of both the tables and delete it. As we have defined ID column for tables as Identity it will get auto-generated value by itself.

That's fine for now. We don't need so many configuration for this easy task. Save your package file and go to 'Control Flow' tab. Right click on 'Data Flow task' and click on 'Execute task'. This will execute the task and transfer the data from source table to destination table.

If it runs successfully(chances are 100% if your are following step by step ETL creation till now.), it will turn 'GREEN'.

But if it turns 'RED' don't worry, there is a tab 'Execution Result'. This tab shows the progress of the package execution. If you get an error it will be shown here. Copy the error in a notepad and check the issue.

Or if you want you can also debug SSIS package using debugging events. Right click on 'Data Flow Task' and set debugging brake points.

Cheers and comment or share if this helps!!

How To Play Multiple HTML5 Audio-Video in Sync on Safari Browser

As Safari HTML Audio Video Guide Suggests:

Syncing Multiple Media Elements Together Until the advent of media controllers, ensuring that two or more videos played at precisely the same time was a challenging endeavor.

Media controllers let you group any number of audio and/or video elements so that they can be managed by a universal set of controls, and also so that they can be kept in perfect sync, even if a network hiccup occurs.

To create a media controller, simply add the mediagroup attribute to all of the elements you wish to sync together. The value you choose to assign to mediagroup is up to you—as long as the value is the same for each slaved element, a media controller will be created implicitly.


Most of the same functions, attributes, and events available to audio and video elements are also available to media controllers. Instead of calling play() or pause() directly on the video itself, you call them on the media controller.
Controlling Media with JavaScript Syncing Multiple Media Elements Together 2012-12-13 | Copyright © 2012 Apple Inc. All Rights Reserved.


Note: Two attributes that aren’t supported by media controllers are loop and auto-play.
Accessing the controller object on any of the slaved media elements will return a controller of the grouped elements. You can also create a media controller entirely in JavaScript without needing to modify the attributes of your HTML:


If one video stalls or stutters, the other videos will automatically pause to wait for the lagging video to catch up. When the video buffers and is ready to play, the remaining videos will resume in sync.

And you can find this doc here: Safari HTML Audio Video Guide

Difference Between Implicit and Explicit Join

If you are looking for the performance difference between INNER JOIN(Explicit Join) and WHERE clause(Implicit Join), Let me clear the SQL Engine(either its mySql/SQL Server or Oracle) got more and more intelligent during the time. So there is no such difference in performance whatever you write from INNER JOIN or WHERE clause.


Let me write a simple query using WHERE clause or Implicit Join:



This SQL Query is written with only 4 tables, 5-10 tables in queries is common scenario while you are working with a good size of project. And believe me or not, writing these queries with WHERE clause is definitely going to get your head off the shoulders.

Now let me write it using INNER JOIN or Explicit Join:



The later one is hell more readable than the conventional(not now though) WHERE clause written above.

Here is an Execution Plan for INNER JOIN and WHERE clause, which is same for both the cases:

Execution Plan Comparison: Implicit Join vs Explicit Join

Execution plan credit: Blog do Ezequiel

So Is the only difference READABLITY??

Answer is Yes and No.


So here are some other points which make INNER JOIN better than WHERE clause:

1. INNER JOIN is ANSI syntax which you should use.
2. It is generally considered more readable, especially when you join lots of tables.
3. It can also be easily replaced with an OUTER JOIN whenever a need arises.
4. The WHERE syntax is more relational model oriented.
5. A result of two tables JOIN'ed is a Cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

And some of interviewers who believe in playing word tricks(I hate them really) this post is actually Difference: Implicit Joins vs Explicit Joins

Cheers !!!