Tuesday, May 12, 2015

Loading excel and text file to the OLEDB destination

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. We included few ways to load data. There may other ways that would be discussed later in our tutorials
1.       Import And export wizard (32bit/64bit)
2.       SSIS package
Loading data from excel/flat file to sql server:-
3.       Open SSDT press Ctrl+Shift+N (or) Select New Project Solution from File menu.

4.       You find  options to create a project 
a     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

5.       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
6.       After entering into the Control flow  Drag excel source and flat file transformations from the control flow

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

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

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

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

13.       Click on new  create new flat file connection

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

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

18. Right click on the excel source go to show advance editor 

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

20.  set orders of the output columns  do same process for flat file also

21.       after setting both transformations sort key orders  drag  merge transformation

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

Check SQL SERVER table for loaded data

Expression Error In Derived Column

How to resolve Expression Error IN Derived column

Expression Error In Derived Column

Change data types in Show Advance editor of Source transformation which causes error. Right click and go to Show Advance editor and go to tab Input and output properties. Select excel source properties and drill down the columns select the column which you want Under Data type properties change the data type. Here above picture indicating that data type that is DT_DATE (DATE) and DT_WSTR (STRING) miss match.  Whatever the data types coming from the source u need to use those data types in sequential transformations. Then only you won’t get these type of errors.

Monday, May 11, 2015

How to use Look up in SSIS

Using lookup in SSIS:-
                Let me go with a scenario where we use lookup. The functionality of lookup is verifies the data coming from source to destination if the data is not available in destination. It will pass the data to other transformation or destination. If the same data coming from source it prevent the passage of data to other transformation. Configuration is given below Drag lookup transformation from the ssis tool box and configure to source and destinations. When you double click on lookup it will pop up configuration window like below picture

Cache mode: Full Cache for better performance (depends on requirement)
Connection type: OLEDB connection manager
Specify how to handle rows with no matched entries: Redirect rows to no matched output .later
Go to connection and select which table you want look up for existing data.
Go to columns option and check mappings in that for avoiding null inserts

I used adventure works for explaining look up transformation
Source table is  [Sales].[CreditCard]  and destination I selected in test database. [CreditCard] 
Step1:- open lookup transformation and specify how to move rows.

Step2:- go connection select which table you want to look up for existing data
Step3:- go to columns give reference mappings need to be compare source with destination. Like below window.

Click on ok. Drag ole dB destination and select input output selection. Select “Lookup no match output.”This option will transfer only not matched records .

Click ok execute,
When you execute it first time it transfer all the rows from source to destination, for second time it won’t transfer the rows.
1st time 
2nd time

SQL-Cursors How to use cursors in sql

Cursor is a database items to recover information from an outcome set one column at once, rather than the T-SQL orders that work on all the lines in the outcome set at one time. We utilize cursor when we have to redesign records in a database table in singleton style means push by column.

Life Cycle of Cursor

1.Declare Cursor

A cursor is declared by defining the SQL statement that returns a result set.
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL] --define cursor scope
[FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
FOR select_statement --define SQL Select statement
FOR UPDATE [col1,col2,...colon] --define columns that need to be updated

A Cursor is opened and populated by executing the SQL statement defined by the cursor.
Opened locally or globally. By default it is opened locally.

    OPEN [GLOBAL] cursor_name --by default it is local


When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation. Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option.
FROM [GLOBAL] cursor_name
INTO @Variable_name[1,2,..n]

After data manipulation, we should close the cursor explicitly.
Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:

CLOSE cursor_name --after closing it can be reopen


Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.
DE allocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:

     DEALLOCATE cursor_name --after de-allocation it can't be reopen