Learn Simple SQL Server Integration Service example database to text file transformation in 5 minutes

Dear Friends,
Today we are going to create simple SSIS task which we will create a text file output using ETL (Extract Transform Loading) of SSIS.
For this first basic this which we need to know
1) Source Database
2) Output file name & path

Step 1:- Open SQL Server Business Intelligence Development Studio (BIDS) and create a new Integration project as shown in below fig.


Step 2:- Now drag and drop Data Flow Task from control flow item’s toolbar as shown in below fig.

Step3:- Once you done with this just double click on Data flow task and you will get a new screen for data flow. Now we have to choose data source for our ETL process for this just drag drop data source from data flow source tool bar.
Step 4:- configure source database. In this example we are using OLEDB data source and SQL server Database. You can choose other database source too.  To configure OLEDB data source just right click the OLEDB data source and click on Edit.


You will get OLEDB Source Editor as shown in fig.
Just click on new button as highlighted in above fig set database. Once database source is done we can choose any data mode like table or view, SQL Command as shown in fig.

I am choosing table employees of North wind database here.  After doing this just choose desire column which you want to export in file. You can choose this column by selecting columns option.

Step 5:- Now Drag drop destination control as we require flat file destination and configure it. We can configure destination same as we did for data source. Just right click on flat file destination control and edit it.


Step6: – Once you done with configuration of file click on mapping and map the columns. As shown in below fig.

Step 7:- Now all set just run using”F5” 

If everything is green then our SSIS package is done successfully.
In next session we will learn how to deploy it, how to use configuration for it.
Till than enjoy SSIS.

Thanks
Rajat Jaiswal

Advertisements

Replication – A way of disaster management

What is Replication?

     Replication is another way of disaster recovery with the help of this we can recover our database and we do not need to do much manual efforts.

Replication is the way by which you can transfer data or database object from one server to another server, synchronize them, and maintain them identical.

The interesting part is you can implement replication for any location like LAN, WAN, Internet without much effort.

What is the initial requirement for Replication?

 The replication can work on SQL server 2000 2005, 2008.

What are the good points of Replication?

  There are many advantage of replication

  • High availability solution
    • Minimum error
    • Easy to implement

 What are the points which we would not like for replication?

  • Main server overhead increase
  • If there is failover  occurs then  we have to handle that manually

 What are the terminologies we used in Replication?

We used following terminologies in replication

1)   Publisher:-

The publisher is main database source which publish data or other data object to different server or we can say subscriber.

2)    Subscriber :-

The Subscriber by name it is clear that it subscribe the database from publisher server.

 3)   Distributor :-

The distribution is the server which manages flow of the data. It not mandatory to have distributor always.  A publisher sever can manage role of both (publisher & distributor).

4)   Article:-

An article can be any data object like data table, Rows, Store procedure or view. A publisher can be published number of article.

What are the different types of Replication?

 We can implement 3 type of replication

Snapshot Replication, Transactional Replication, Merge Replication.

Snapshot Replication:-

      By the name it is clear that in this publisher take snap shot of entire database and publish to the subscriber.   And when its take full snap shot obviously it taking it consumes more resource. Or we can say its resource intensive process.  It also known as simple replication. It used where database changes are rarely.

Transactional Replication:  It also known as dynamic replication.

In this the publisher and subscriber are always synchronized. It used where we need exact same copy on subscriber with immediate effect. It can be useful when subset of database or table is required.

Merge Replication:-

   It’s very interesting replication by the name it’s clear that it use merge method. In this both publisher and subscriber has freedom to work without network and when they connect the replication agents check both the database and merge each other’s changes. In merging if any conflict occurs than preference is given to publisher.

Later on in the post we will get a brief  introduction how to implement  replication tillthan enjoy definition :).

 Thanks & Regards

Rajat

Log Shipping – A way for disaster management

 Hello friends,

Today we will  see  how log shipping is helpful to us. so just checkout basic first  then we will  implement it.

What is Log Shipping?

 Log Shipping is another one of most fascinating feature provided by SQL Server for disaster management. With the help of this technique we can update keep distributed copy of our database on another server. It can backup transaction log, restore them, distribute them to different location. To achieve this automated process SQL server used SQL server job agents.

Or we can say Log shipping has following functionality

  • It backups transaction log of main server
  • It  transfers transaction log to predefine distributed location
  • It restore transaction log to secondary server

 

What is the initial requirement for achieving the Log shipping?

Log shipping is handle with SQL server 2005 version onwards no other  extra software or hardware require to achieve this.

What are the different terminologies used for Log Shipping?

For Log shipping we have used following terminologies  

  • Primary server:-

Primary server is main server from where we have to copy the database transaction log it contain main database or primary database server.

  • Secondary Server :-

Secondary server is another server or we can say its stand by server on which transaction log is restored to maintain database availability. The secondary database can have same database objects & data after Log shipping.

  • Monitor server :-

Monitor server by name it’s clear that it maintain or monitoring the activity of log shipping.

   What are the benefits of Log Shipping?

  • Easy  to implement   no extra efforts required to maintain it
  • No extra software or hardware required
  • Fast then other previous backup process
  • Multiple secondary server is available

What the unlike things with Log Shipping?

The things which we can say not like for our use is

  • No automated failover recovery  we have to manually shift the server  setting to activate secondary server  when primary server is down
  • It not reflects immediate effect on secondary server.
  • Log shipping cannot be scripted it means you cannot mimic production server logs hipping to testing server.
  • Possible Database Loss if primary server fails.
  • Difficult troubleshooting

In next few post i will show how to implement Log shipping.

Till than enjoy the  SQL Server.

Thanks

Rajat