Load data in pre-formatted excel file in SSIS

Suppose we have requirement like we need to create a daily excel report. In this report having its own format and after that we need to load the data. For loading data in excel is easy but when we trying to load data in formatted excel is bit difficult. We need to set some properties.

This is the pre- formatted excel. We need to load the data on daily basics.

This is my package design.

Let’s learn how we will do it.

Open the SSDT.

Take the File system task.

I have created a master pre-format excel sheet. Whenever we running the page first I need to copy the master excel file to destination folder and then we need to load that data in destination folder excel file.

Taking the file system task

I am creating some variables

Like SourceTemplateFile, DestinationFolder and FileFullName

Value of the SourceTemplateFile is the location of the master formatted excel sheet.

 

Now setting the value of this variable in package.

Similar way setting the value of DestinationFolder

See here I am creating dynamic excel file according to the date so I am creating dynamic name of the excel file using expression

No I am configuring the file system task source and destination values.

1.       Destination Connection

Here I am using destination path is as variable so I select ISDestinationpathVariable is True. Assigning the destination variable and keep Overwritedestination is true.

2.       Operation

Here I am performing Copy operation

3.       Source Connection

Using source as variable and assign the value of the source variable.

Now Copy operation is completed successfully. Running the package and checking the output.

Package executed successfully.

   

Now I am taking data flow task.

Double click on the Data flow task

In data flow task I am taking the Source as OLEDB

Creating the connection

Here I am using AdventureWorksDW2008R2 database.

select p.EnglishProductName as [Product name],pc.EnglishProductCategoryName as [Product category name], psc.EnglishProductSubcategoryName as [Product sub category name], sum(f.SalesAmount) as [Total sales]from DimProductCategory pc inner JOIN DimProductSubcategory Psc ON PC.ProductCategoryKey=psc.ProductCategoryKey inner join DimProduct p ON p.ProductSubcategoryKey=pc.ProductCategoryKey inner join FactInternetSales f ON f.ProductKey=p.ProductKey group by p.EnglishProductName,pc.EnglishProductCategoryName,psc.EnglishProductSubcategoryName

 

 

Map the columns.

Click ok.

Now I am taking the destination as excel.

Now I am creating the connection.

First we mapping the columns of excel. Keep in mind we are creating the excel file.
Remember unchecked the First row column name. 

 

Map the column.

Make the excel connection dynamic using expression.

 

Click Expression and the properties you select Excel path.

Click ok.

Now the most important properties we need to set on Data flow task on DelayValidation is true.

 

And also we need to set this property on Control flow on DelayValidation is true.

 

Now the package is ready to run.

Now I am running this package.

 

Package executed successfully.

See the output.

 

Hope this is helpful for you.

Thanks!!!

Creating shortcut key in sql server

Keyboard shortcuts can boost our productivity if our daily job relies heavily. They just don’t get the work done quickly, but also improves the efficiency.

Let’s see how to create a short cut key in sql server

Open SSMS.

Go to tool

  

Click on Option

  

Go->Environment ->Keyboard->Query Short cuts

On Query short cuts we can write our sql query. Here I wrote the select * from [SalesLT].[Address]  from the database AdventureWorksLT2008.  Now click ok.

Now I am running this using short cut key. Open the new query windows and press

   

Note:  we need to run this under AdventureWorksLT2008 database.

   

Oh!!! We are getting the expected result.

We can also write the completed sql query including different databases.

Select * from AdventureWorksLT2008.SalesLT.Address

Select * from AdventureWorksDW2008R2.dbo.DimEmployee

   

We can also create the short cut key for stored procedure and functions.

    

Running

  

Got the result.

Remove Duplicate Records in SSIS Package

Suppose you are having the flat file in that file there are duplicates records and you want to load only unique record on the destination and duplicate record on the other destination.

Here I am going to take a simple example where we have a flat file which is having some duplicate records and I want to load unique records in a table and duplicate records on other table.

Below is the flat file.

Now I am creating two tables one is the tblEmp (having unique records) and other tbldupEmp (having duplicate records).

CREATE TABLE [dbo].[tblEmp]        (        [EmpID] [nchar](10) NOT NULL PRIMARY KEY ,        [FName] [nchar](50) NULL,        [Lname] [nchar](30) NULL,        [Mob] [nchar](10) NULL,        [Address] [nchar](100) NULL,         ) Inserting some records   Insert into tblEmp values('1','Bagesh','Singh','888880XXXX','pune')   Insert into tblEmp values('2','Rajesh','kumar Singh','888880XXXX','Delhi')   Insert into tblEmp values('3','Mahesh','Kumar','888880XXXX','Chennai')   Insert into tblEmp values('4','Ganesh','Panday','888880XXXX','Mumbai')   Insert into tblEmp values('5','Naresh','yavad','888880XXXX','Buxar')   Insert into tblEmp values('6','Ram','Kumar','888880XXXX','Patna') --duplicate table CREATE TABLE [dbo].[tbldupEmp ]        (        [EmpID] [nchar](10) NULL,        [FName] [nchar](50) NULL,        [Lname] [nchar](30) NULL,        [Mob] [nchar](10) NULL,        [Address] [nchar](100) NULL,        [NumberOfRecords] int         )

Let’s start

Taking data flow task

Double click on the Data Flow task

Take source as flat file

Create the file connection. See the flat file is comma (,) delimiter so here you select the comma delimiter.

Click ok. Now Map the columns.

Click on ok. Now file connection has been created.

Now I am taking Aggregate transformation.

Double click on this transformation.

Now checked the all columns for find the unique records. You can rename the Count Output name. Here I am renaming the Output column name is NumberOfRecords.

Click Ok.

Now I am taking conditional split transformation. Here I am checking if the NumberOfRecords = 1 then it means that record is unique otherwise records are duplicate.

Double click on Conditional split

Click ok.

Taking two destination one for unique record and second is for duplicate records.

Creating a connection and mapping the columns for both destinations.

Now package is ready to run.

Before run the package table value

 

Let’s run this package and see the result.

Oh!!! Package is completed successfully.

Flat file data

Let’s see the records in table

Hope this will help you.

Thanks!!!

Raw File Source and Destination in SSIS

The Raw file is a native format of the SSIS. The Raw File Source and Destination is a fast way of moving data between data flows, and can also be used when having to stage data to avoid slow writes to other locations.

The use of raw files as SSIS data source is highly recommended for huge volume of data due to its superior read/write performance. Raw files store data in a very basic format that requires almost zero translation and parsing. This enables faster data reads/writes when compared to Flat Files and even faster than Database tables.

Raw file Destination

First I am going to create a raw file. In this file I am store the table data. Let’s see how to create the Raw file destination. Farther I use this file as source.

Taking Data Flow task

 

Double click the DFT.

Taking the OLEDB source from here I am reading the data.

Now I am creating the connection.

 

Click on ok.

Taking Raw File destination.

 

Double click on the Raw File destination transformation.

 

See the access mode.

We can take file name directly or through variable. Better to use File name from variable because you can configure the file name in configuration file.

File Name Access Mode

  

File Name from Variable Access Mode

If you are using File Name from Variable Access mode. Need to create a variable.

 

You can use expression. As you wish.

 

Now we need to map the columns.

 

Now we need to generate initial RAW file.

   

Click on the Generate initial raw file

 

Now most important option we need to set “Write option”. I mean how you want to write this RAW file.

 

Create Always: - Every time creating the RAW file with the same name.

Create Once: - First time when we run the package it will create the RAW file. But when we will be running the package it will throw the error.

Append: - Every time appending the value in the raw file.

Truncate and append: - Truncates an existing file and then writes the data to the file. The metadata of the appended data must match the file format.

Select any of the above as per your requirement. Click on OK.

Now Package is ready to run. I am running the package.

 

Package executed successfully. Now I am seeing the RAW file.

 

That is not a human readable format.

RAW File Source

Now I am going to use this RAW file as Source and loading this data in the database.

Step by step to using RAW file Source.

 

Double Click on the Data Flow task.

Take RAW File Source.

Create the connection.

Access Mode is similar to RAW File destination. Either file name directly or use variable.

 

Map the columns.

 

Click ok.

Now I am taking the OLEDB destination to load the data in the database.

Create the connection.

 

Map the columns.

Click Ok.

Package is ready to run. Now I am executing the package.

 

Package executed successfully.

Now I am seeing the table.

 

Got the expected result.