Thursday 18 February 2016

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!!!

Wednesday 10 February 2016

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.

Monday 8 February 2016

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!!!



Tuesday 2 February 2016

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.

Popular Posts