Load data from multiple Excel sheets to Database in SSIS

Introduction:

In this article, I will explain How to Load data from multiple Excel sheets (5 Sheets) in one Excel file into single table by using SSIS.

Let us consider the following MS-Excel spreadsheet with data in 5 different sheets. Each sheet contains 3 columns of data.

Solution:

• First to create a SQL Table which will store excel sheets data.

CREATE TABLE [OLE DB Destination] (

[productname] nvarchar(255),

[sale] float,

[saledate] datetime

)

• Now create a variable @Sheetname of string type and assign the value Jan2010$. Ensure the variable value ends with “$”.

• Create two tasks in control flow as follows.

• Double click the Foreach loop container, go to the Collection tab.

• Select “Foreach ADO.Net Schema Rowset Enumerator” in Enumerator Configuration list.

• From the drop down of Connection, select “New connection...”

• Click on the “New” button of the Configure ADO.Net Connection Manager.

• You get another popup window Connection Manager. In Connection Manager list we have selected “Microsoft Jet 4.0 OLE DB Provider” from “.Net Providers for OleDb” drop down.

• Browse the file which has multiple Excel sheets from which we will load data.

• Go to the "All" tab, go to the "Advanced" tab, and set the Value as "Excel 8.0" for "Extended Properties".

• And press Test the connection. You got the message “Test Connection succeeded”.

• In the Collection tab, select the Schema as "Tables". This will configure the Foreach loop container.

• Added index value “2” to the variable of @User::SheetName in Foreach loop container.

• In DataFlow Task, add an “Excel Source” and double click on Source connection to Configure the excel connection manager as follows

• Select “Table name or view name variable” in Data Access mode, and assign

the variable value to variable name.

• Added “OLEDB Destination” and assign the SQL table to it.

Finally the Data Flow Task as shown below.

Note: all Excel sheets in the source and SQL Table in destination must have the same structure. 

Let me know what you think about this article. 

Load data from multiple tables into single table by using SSIS

Introduction:

              In this article, I will explain How to Load data from multiple tables (6 Tables) into single table by using SSIS.

Solution:

• The solution of the above problem by using Execute SQL Task, Foreach Loop Container, Script Task and Data Flow Task as follows.

• First you create three variables with default values (Scope-Package level).

• Using Execute SQL Task to get the table names from database and stored that information into variable [@Tablenames].

• Foreach Loop Container is used to get single table name at a time and stored into variable [@Sourcename].

• Script Task is used to create the Query and stored into variable [@ConnectionQuery].

• By using Dataflow Task to get data from Database and stored into Destination table.

Note: The Source 6 tables and Destination table contains same structure.

Let me know what you think about this article. 

How to increase cache memory size in SSIS

Introduction:

           In this article, I will explain cache modes and in which cases we can increase cache memory size.

Description:

            The most important setting of the Lookup Transform is the Cache Mode- It can greatly impact your dataflow performance and affects overall package design.

The Default Cache mode in Lookup is FullCache and Connection type is OLE DB Connection manager.

• In Full cache mode, the reference dataset is loaded into cache before the Lookup transformation runs. This approach uses the most memory, and adds additional startup time. By using this mode to reduce the number of queries sent to the server.

• In No cache mode, the lookup transform doesn’t maintain cache memory. In every time its hits database and get the data. This cache mode is mainly used the reference table is changing frequently.

• In Partial cache mode, the rows with matching entries in the reference dataset and optionally, the rows without matching entries in the dataset are stored in cache. When the memory size of the cache is exceeded, it automatically removes the least frequently used rows from the cache memory.

                In Full cache mode and No cache mode, there is no chance to increase the cache memory size. Only in Partial cache mode, you can increase the size of cache by following below steps.

Steps:

• Select Partial Cache mode in Lookup Transformation.

• Right click on Lookup transformation and select “Show Advanced Editor...” option.

• In Advanced Editor select Component properties. There are actually two separate values – one for 32 bit execution and one for 64 bit. The default value is 25 (it means 25 MB).

• The maximum size of cache memory in Partial mode is 3072 (it means 3 GB).

Note: If you try to increase the value more than 3 GB. Its shows the error as follows

Let me know what you think about this article.

Difference between Businesskey and Surrogate key

Introduction
In this article, I will explain the Difference between Business key and Surrogate key

Business key	Surrogate key
• Def:  A key is an index which identifies uniqueness of a row based on columns that exist naturally in a table according to business rules.  For Example, In a company are issued Employee Identification number (Eid) that is unique to them.	• Def: A key that is auto generated identiy seed to efficiently associate a fact table or dimension table. For Example, to create identification number at the time of Develop of Dimension or Fact
• This keys are specified by user or company.	• This keys are generated at run time.
• Business keys are not only numeric, sometimes its date, text also.	• Surrogate keys are commonly a numeric number.
• Business keys are changed or updated depends on business rules.	• Surrogate keys will not be updated over time.
• A business key is one or more existing data attributes that are unique to the business concept. For Example, In Employee table there are two business keys like Employee id and Department id	• Introduce a new column, which is a key has no business meaning.
• Business keys index will be larger because these are typically large is size.	• Surrogate keys are typically integers, so it having small index structure means better performance for Join operations.
• Business keys are a good way of avoiding duplicate records in the tables.	• Surrogate keys are more efficient that business keys. So that is the reason surrogate keys are recommended joining a fact table to a dimension table in cube.

Let me know what you think about this article.

Split a single column value to multiple columns with Space

Introduction
In this article, I will explain how to splitting a single column value into multiple with space and stored into Table
Let’s start off with an example including step by step operations.

Steps:
• Create a table with the following structure

Create table Emp_Data

(Id int identity(1,1),

FullName varchar(50))

• Insert the values into the table

insert into Emp_Data values('Ken J')

insert into Emp_Data values('Terri Lee')

insert into Emp_Data values('Rob M')

insert into Emp_Data values('Diane L')

insert into Emp_Data values('Janice M')

insert into Emp_Data values('Kevin F')

Id	FullName
1	Ken J
2	Terri Lee
3	Rob M
4	Diane L
5	Janice M
6	Kevin F

• In the above table FullName value into split into two names like first name and last name, stored into same table
• Add two columns by using the below command

alter table Emp_Data add firstname varchar(20),lastname varchar(20)

&

• Update the Firstname and lastname from fullname by using below command

update emp_data  set firstname =(LEFT(FullName, Charindex(' ', FullName) - 1)) FROM  Emp_Data b where id=b.id

update emp_data  set lastname =(RIGHT(FullName, Charindex(' ', Reverse(FullName)) - 1)) FROM  Emp_Data b where id=b.id

• Final Output is

Id	FullName	firstname	lastname
1	Ken J	Ken	J
2	Terri Lee	Terri	Lee
3	Rob M	Rob	M
4	Diane L	Diane	L
5	Janice M	Janice	M
6	Kevin F	Kevin	F

Let me know what you think about this article.

Load data to OLEDB Destination in SSIS by using Fast load

What is fast load property in OLEDB Destination? By using this option is Increase Performance tuning of package?

       OLE DB Destination is available in the Dataflow task in SSIS, which provides a couple of options to push data into the destination table.

• First one is “Table or view” option, which inserts one row at a time.
• Second is “Table or view fast load” option, which internally uses the bulk insert statement to send data into destination table. It’s always provides better performance compared to other options. Once you choose “fast load” option it gives you more control to manage the destination table behavior during various options like Keep identity, Keep nulls, Table lock and Check Constraints.

Set fast load property in OLE DB Destination

1. Double-click the OLE DB Destination and select “Table or view - fast load” in the Data access mode menu.

2. In Fast load option, we can specify the sort order in the Editor. To access this editor, right click the component and click Show Advanced Editor, and then select the Component Properties tab as shown below.

3. Click OK.

Example: To transfer 2 lakh’s records in a flat file to database by using fastload option.

• By using Table or view

• After that we use “Table or view – fast load” and Sort order by ID.

So speed gain is quite large when using Fastload.

I was also surprised that SSIS –With Fastload method was faster by 80% than with out using Fastload in OLEDB Destination.

Let me know what you think about this article.

Check points in SSIS with Example

Introduction

        In this article we will explain about Check point’s usage in SSIS. The configuration of check points helps us to resume from the last task in the package. Check point saves the configuration details in a XML file which acts the source for later execution section. For example if multiple tasks in a package, if there is any failure in any task. It stores the fail task point in the check point file, once we restart the package the check point helps to start from last failure point specified in check point file.

        This feature is an added advantage for SSIS package which provides a better performance in order to achieve complex tasks.

Configuration of Check points

       SSIS stores the configuration information in an XML file whose name and location information. To implement check points in your package, you must follow the specific rules as follows.

CheckpointFileName: Specifies the full path and filename of your checkpoint file.

SaveCheckpoints: Specifies whether the package saves check points. [Set to TRUE , Default is FALSE]

CheckpointUsage: Specifies when to use checkpoints. 

àThe property supports the following three options:

o Never    : A checkpoint file is not used. (This is default)

o IfExists : A checkpoint file is used if one exists. This option is the one most commonly used if enabling checkpoints on a file.

o Always  : A checkpoint file must always be used. If a file doesn’t exist, the package fails.

To set the check points to the package

àRight click on anywhere in the Design area of the Control flow tab and select properties.

Example:

·  Take three Execute SQL Tasks are configure as

·  Select each Execute SQL Task à Properties àChange the FailPackageOnFailure property to TRUE.

·  Open the properties menu at the package level (Just open properties in the Control Flow without any task or connection manager selected)

Change the properties

Checkpoint Filename: c:\Checkpointexample1.xml

Checkpoint Usage: IfExists

SaveCheckpoints : True

·  Run the package and you will see the package is failed on the Second Execute SQL Task.

·  And checkpoint file is created at C:\

·  Correct the problem: Open the Execute SQL Task 2 and Configure the SQLStatement property: "Select 1".

·  The package has now completed and skipped the first step which already executed.

Note: Using checkpoints in the package can avoid repeating operations and save the time also. 

Let me know what you think about this article.

Package security in SSIS

In SSIS, Package security is possible by using password protection. It helps us to prevent from

• Unauthorized Deployment

• Unauthorized manipulation to the packages

&

In BIDS (Business Intelligence Development Studio) having better security, along with password you choose one of the property “Protection Level” also. This property tells SSIS how to handle sensitive information stored within your packages. [Sensitive information means package connection strings, user defined variables, enumerators etc.]

The options are available in protection level are explained as follows.

• DontSaveSensitive

When the package is saved, sensitive values will be removed. This will result in passwords needing to be supplied to the package, through a configuration file or by the user.

• EncryptSensitiveWithUserKey

This will encrypt all sensitive data on the package with a key based on the current user profile. This sensitive data can only be opened by the user that saved it. It another user opens the package, all sensitive information will be replaced with blanks. This is often a problem when a package is sent to another user to work on.

• EncryptSensitiveWithPassword

Sensitive data will be saved in the package and encrypted with a supplied password. Every time the package is opened in the designer, you will need to supply the password in order to retrieve the sensitive information. If you cancel the password prompt, you will be able to open the package but all sensitive data will be replaced with blanks. This works well if a package will be edited by multiple users.

• EncryptAllWithPassword

This works the same as EncryptSensitiveWithPassword except that the whole package will be encrypted with the supplied password. When opening the package in the designer, you will need to specify the password or you won’t be able to view any part of the package.

• EncryptAllWithUserKey

This works the same as EncryptSensitiveWithUserKey except that the whole package will be encrypted. Only the user that created the package will be allowed to open the package.

• ServerStorage

This option will use SQL Server database roles to encrypt information. This will only work if the package is saved to an SSIS server for execution.

To set or change the protection level of a package

Steps:

1. In Business Intelligence Development Studio, open the solution that contains the Integration Services project.

2. Click anywhere in the design area of the Control flow tab, its shows package properties.

3. Select “Encrypt all with password” option.

4. And Choose PackagePassword property.

5. Build the Solution.

After finish the building process to test the package in either ways

• By opening the solution again

• By deploying the manifest file

In the above two situations it asks password to farther changes in package of opening Sample1 Project as follows. 

Let me know what you think about this article.

What is Deployment utility in SSIS

               Deployment utility is used to move the packages from one place to another place same as import/export package features in SQL Server Management Studio. The deployment utility is a folder that contains the files you need to deploy the packages in an Integration Services project on a different server.

                   When u builds the project, all packages and package dependencies (like configuration, Manifest files) are automatically included.

To create a package deployment utility as follows

Steps :

1. In Business Intelligence Development Studio, open the solution that contains the Integration Services project for which you want to create a package deployment utility.

2. Right-click the project and click Properties.

3. In the Property Pages dialog box, click Deployment Utility.

The following table lists the deployment utility properties.

Property	Description
AllowConfigurationChange	A value that specifies whether configurations can be updated during deployment.
CreateDeploymentUtility	A value that specifies whether a package deployment utility is created when the project is built. This property must be True to create a deployment utility.
DeploymentOutputPath	The location, relative to the Integration Services project, of the deployment utility.

4. To update package configurations when packages are deployed, set AllowConfigurationChanges to True.

5. Set CreateDeploymentUtility to True.

6.Optionally, update the location of the deployment utility by modifying the DeploymentOutput Path property.

7. Click OK.

8. In Solution Explorer, right-click the project, and then click Build.

9. View the build progress and build errors in the Output window.

Note: You can see the packages in the above diagram at Deployment Output path.

Let me know what you think about this article.