Wednesday, January 20, 2016

Excel file upload to the sql server table by using SSIS

Let’s start with creating a small package in SSIS
We will start creating small project which loads data from source flat file to destination SQL Server database. here i have written few ways to load data. There may other ways that would be discussed later in our tutorials
  1. Import And export wizard (32 bit/64 bit)
  2. SSIS package
Loading data from excel/flat file to sql server:-
  1. Open SSDT press Ctrl+Shift+N (or) Select New Project Solution from File menu.
  2. You find  options to create a project

  1. Name your project. Whenever you create a project a solution file will be created in location folder c:\users\xxxx\documents\visual studio 2010\Projects. Click ok
  2. Go to ssis tool box drag data flow transformation from the tool box. And double click on it you will automatically entered into Control flow pane

  1. After entering into the Control flow  Drag excel source and flat file transformations from the control flow

  1. Here we are using two sources to load data into destination. So the column name should be same in both sources
  2. Select flat file and excel file which you want load into SQL server.

  1. Click on new and select create the connection excel file as show above picture
  2. Go browse and select the excel file where you stored

  1. Click ok and select name of the excel sheet. As 1st page of excel xxxx$

  1. Go to columns and click ok. You can check preview also. Next configure  Flat File source

  1. Click on new  create new flat file connection

  1. Click on browse select Flat file you want to load go to columns and click ok.
  2. Remember you must go to columns tab otherwise you can’t map the inputs to the outputs
  3. we are using merge join transformation to merge flat file and excel file

  1. before configuring merge join we need to set the property    IsSort to true for both transformations

17. Right click on the excel source go to show advanced editor

  1. go to input and output properties  click on external source output on the right pane you will find Is Sorted Propertie make it true and set out put orders of the columns id,ename,salary

  1. Set orders of the output columns  do same process for flat file also

  1. After setting both transformations sort key orders drag merge transformation

  1. Take one ole db. destination save and execute the package.
  2. For excel File loading Right click on the package go to debugging Run64bitruntime :false
  3. Press F5 to debug.

Check SQL SERVER table for loaded data

Installing sql server 2012

Before installing SQL server or its related tools check your machine configuration whether it is compatible or not. You can download SQL server 2012 from Microsoft site or Download here else you can use DVD set up to install.  Minimum system requirements to install setup given below
PROCESSOR: 1.5 GHz and above
Other versions may require additional set up files
Sometimes you require .Net framework to support actual setup installation. double click on setup file it will starts installation wizards.

Click on “new SQL Server stand-alone installation or add features to an existing installation. You will see screen like below picture
Click ok for setup support rules screen
Click ok for installation rules.
Click on next. And installation type click on next Enter product key if you have or try for evaluation period

Click next and accept agreement by the Microsoft. Click next select server role as SQL server feature installation for manual selection or all features with defaults

Select features and click next define installation rules and name your instance.

You can also change installation directory by browsing “instance root directory”. Click on next button

Click on next.  You will entered into server configuration wizard
Configure as show above figure. You can change these settings after installation in windows services click next to database engine configuration.
Here you can add current user to operate it and Authentication mode as mixed mode. So you can login with windows credentials or SQL credentials .define a password for sql authentication. Click next.
Add current user again. You can add administrator to access your instance

Again add current user. Click next you will entered into Distributed Replay controller and click next after error reporting click next done.

Click next you will see below screen select install. installation will be finished in few minutes

How to use data profiling task in ssis

Data profiling Task is used to find potential issues and data quality in the SQL Server table data. This task will not work on other data sources or third party applications data to run this task you must have table creation permissions on the temp db.
Configuring the Data profiling task:-
Go to connection manager right click a create a connection to target server.
And select target database and click ok

Drag data profiling task from SSIS Tool box to control flow and double click on it.
Go to general and destination type select file connection
In destination select create new connection.
Select usage type as create file. Select destination give name to it and click ok
Click on Quick profile
Select connection and select your connection string and select the table which you want perform profiling. Select the profile which you want to profile and click ok go to profile requests this is another way configuring profiling tasks click ok.
Output xml file will be generated in destination folder. Open the data profiler task and click on open profile viewer. You will see below window which shows statistics of table