Metro Early Adoption Program for SQL Server Code Name "Denali"

This video introduces the Metro Early Adoption Program for SQL Server Code Name "Denali", a program you can self-nominate a project or solution that you think is a good fit for the next release of SQL Server.

If you are unable to view video in my blog then you can visit the below link

Visit Channel 9 Video page here

SSIS improvements in SQL Server code name-Denali - Channel 9 Video

Components of an Integration Services Package

Integration Services provides various objects to build our SSIS package.Each of these objects has their own characteristics and play an important role in the package building and execution. So let’s understand what they are and what they do.

What is package?

• A package is a collection of control flow elements, data flow elements, connection managers,event handlers, variables and various configurations. We use these objects and assemble them into a single package.
• It’s a unit of work that is retrieved, executed and saved.

following is the quick summary of the objects that builds a package.

Object	Description
Control Flow elements	Implements workflow in a package. Process oriented. Interacts with other processes Executes in a linear fashion.
Data Flow elements	Contains sources and destinations. Information oriented Extracts,Transform and loads the data from various/multiple sources.
Connection Manager	Connects to the different types of data sources (for e.g. Flat File, Oracle, SQL Server,Excel files, csv files and many other data sources) Extracts and loads the data.
Variables	provides temporary storage during the package execution It can be used in expressions to update values dynamically during the transformations. Used to update values for the property expressions Used to define conditions for the precedence constraints
Event Handlers	Runs in response to the events raised by the packages, tasks,containers.
Log Providers	Provides logging mechanism to packages, tasks, containers. Can provide/log various important details about the package execution.

Happy Learning !!!

Execute SSIS Package based on File Existence

Often we have a requirement to check the file existence before we execute our package and it’s a best practice as well. So let’s understand how to achieve this in SSIS.

Quick Answer : The File.Exists () method will check the file existence on the specified path. The File.Exists() method will be available once you include System.IO; namespace to your script editor.

Complete Demo.

1. Open the SSIS Package/Create a new SSIS Package
2. Go to Control Flow
3. Create three new variables with the Package Level Scope. – See the image 1 below
4. Create one file (Excel or Flat File) as per your requirement and store it on a C:\
5. Variables declaration
   1. FileExists – type is Boolean
   2. FileName – type your file name in a value column.
   3. FilePath – type your file path in a value column
6. Go to  Control Flow and drag the Script Task to the container.
7. Double click on the  Script Task and go to Script Tab and assign the ReadOnly and ReadWriteVariables as shown in the below Image 2
8. Click Edit Script
9. Now, you will be in the script Editor.
10. Declare the namespace as  using System.IO;
11. Go to Main() method and copy the below code.
12. File.Exists will check the file existence for the specified path

public void Main()
{
  string fileNameWithPath=string.Empty ; 

//prepare the complete file path with name

fileNameWithPath =Dts.Variables["FilePath"].Value.ToString ()  + Dts.Variables["FileName"].Value.ToString (); 

            //check for the file existence
            if (File.Exists(fileNameWithPath))
                Dts.Variables["FileExists"].Value = true; 

            //display the result
            MessageBox.Show("File Exists " + Dts.Variables["FileExists"].Value.ToString()); 

            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success; 

        }

Now you are ready with the code. The above code will return true or false based on the file existence and you can use this value in the later part of the package to make conditional execution of the SSIS Package.

Conditional Execution of the package using Precedence Constraints

1. First connect your Script Task with the other Task Container i.e. Data Flow Task or any other task which you want to execute based on the file existence.
2. Now, double click on the green line (pipe line) which connects script task to another task.
3. This will open a Precedence Constraint Editor .
4. Under the Constraint Options group Select  --> Evaluation Operation as Expression as show in Image 3
5. In the Expression text box type the expression you want. In our case it' is :  @FileExists=true. This means if the variable is true the the next task will execute.
6. See the Image 3 for more clarity

Images

Image 1: Variables Declaration

Image 2 – Script Task Editor

Image 3 – conditional Execution using SSIS Precedence Constraints.

Happy Learning !!!

What is Task Host Container in SSIS?

What is Task Host Container in SSIS?

1. It’s a default container where every single tasks fall into.
2. If we don’t specify a container then the task will fall into Task Host container.
3. The Task Host is not configured separately, instead, it is configured when we set the properties of the task it encapsulates.
4. SSIS extends variables and event handlers to the task through task host container.

Happy Learning !!!

Tuning Your SSIS Package Data Flow in the Enterprise (SSIS Webcast Watch)

Tuning Your SSIS Package Data Flow in the Enterprise video gives you a very good understanding on the SSIS tuning part.

Download Video from here:

http://msdn.microsoft.com/en-us/library/dd795224.aspx

There are couple of other video links are available for download on SSIS. So don’t forget to check that. Those are really good videos and gives lot of knowledge on the performance tuning of SSIS packages.

Happy Learning !!!

What is Package in SSIS?

Let’s understand the basics of Package in SSIS. This could also be a good interview question for SSIS.

• A package is a unit of work that is retrieved, executed and saved.
• It’s a collection of various objects i.e. Control Flow elements, data flow elements,event handlers, variables, logging which together builds a package.
• Various functionalities can be added into package using Control Flow elements, Data Flow elements.
• We can set the security settings on the package.
• We can also incorporate transactions in packages to better control the package execution.
• Using connection managers in package helps us to connect with different data sources to extract and load data.
• Logging in packages allows us to better control the package execution and also enables efficient error handling.
• Event handlers handles the events raised during the package runtime. 

Happy Learning !!!

Download BI Architecture and Design Guide – Microsoft Business Intelligence

Today, I came across very useful design guide for Microsoft BI and thought to share with the readers.
This guide is available for download on CodePlex.com. It contains following chapters.

1. BI Framework
2. Data Extraction
3. Data Staging
4. Data Transformations and Data Quality
5. Data Loading
6. Dimensional Modeling
7. Data Partitions
8. Online Analytical Processing (OLAP)
9. Data Mining

Please visit : http://biarchanddesignguide.codeplex.com/ for more information on the design guide and to download the material.

Happy Learning !!!

Support for DTS in SQL Server 2008

As we all know that SQL Server 2000 Data Transformation Services is already deprecated but SQL Server 2008 still supports DTS (Limited Functionality). SQL Server 2008 provides optional 32 bit management, run-time, and design-time support for packages that were created by using the DTS tools and object model.

Note:

1. Please note that not all the functionality of DTS is supported in SQL Server 2008.
2. To get the DTS support in SQL Server 2008 you might have to manually install additional, optional components that are not available during setup.

For more information on which are the functionalities of DTS is supported in SQL Server 2008, please visit the below link

http://msdn.microsoft.com/en-us/library/bb500440(v=SQL.105).aspx

Happy Learning !!!