SSIS Package : Export Data from Database, Daily to New Excel Without Using Script Task

Export data from database to excel sheet with new excel per day

SSIS => SQL Server Integration Services, is used for ETL (Extract, Transform and Load) type of work. It is advanced version of DTS we can say. Here we can schedule packages as jobs and it will execute without human intervention.

In this article we will export the data from SQL database table to excel sheet day wise.

To start with this you will need to install SQL Server 2008 with BIDS (Business Intelligence Development Studio)

Now moving forward the first step would be go to

File => New Project => Select “Business Intelligence Projects” from left panel and “Integration Service Project” from right panel. Give proper name and save at desired location.

On OK click, it will open Package.dtsx.

Create a Template folder somewhere on your hard drive. In that folder create a sample excel with just the headers that you want in the final excel.

We will use this template to create new excel every day with same format and new data.

Select File System Task from toolbox and drag on Control Flow Tab.

This task will be used to create excel every day before the actual code runs.

Change the name of task to if you want.

To change the properties of task, double click on it. Popup File System Task Editor will open up

Set the Source Connection to Template excel. Operation to Copy file (Which is set by default)

While setting the source connection do below things..

Click within the area in front of Source connection and select <new connection..>.

Below popup should be displayed.. 

Select Existing File there and click on browse, select the template excel over here

Click on ok.

Now let’s set the destination connection.

Set IsDestinationPathVariable to true.

And select the Destination variable

Change the name of variable if you want and click on Ok.

Set OverwriteDestination to true or false according to need

Now Select the Variable in left panel and press F4 to check the properties of it.

   

Set EvaluateExpression to true.. Now change the expression and set to below

Now go to Control Flow tab and double click on “Data Flow Task”.  It will switch to Data Flow Tab. Now check the toolbox it has been changed now.

It will contain three sections, source, destination and transformation. Now drag and drop the OLE DB Source, Data Conversion and Excel Destination to Data Flow Pane respectively.

And connect the green output of OLE DB Source to Data Conversion (Which will be useful for converting between non-unicode and unicode data) to Excel Destination as below

Now double click on OLEDB source and set the connection to whatever database you have.

Once this is done go to Columns section as below 

After that double click on excel destination and set the excel connection to Template excel for now.

.

Click on ok and do the modifications as in below figure. I’m selecting Data sheet of my Template excel workbook as it contains final columns that will exist in your excel workbook.

Once this is done, Click on mappings and do the mappings as necessary.

After source and destination settings are done. Right click on Data Conversion task and select edit Advanced Properties. And set the input output column names and data type to Unicode strings.  

Click on Ok. Data Conversion part is done.

Netx is again go to Excel Connection Manager, which is visible in below pane. Click on expression tab and map the Excel file path property to the variable that we created earlier as shown below

  

 

 

Select dataflow task and Set the delay validation to true, to avoid getting some errors at compile time related to excel sheet which may come.

Run the application now and package will copy template excel sheet from Template folder to destination folder with new name which will include today’s date. In the next date package will copy the data from database to excel sheet.

Happy Learning :)

CodeProject

Introduction to Haskell

What is Haskell?

In short Haskell is yet another computer programming language. Haskell is named after Haskell Brooks Curry, a mathematician whose work is a foundation for all functional programming languages. Haskell is a polymorphically statically typed, lazy, purely functional language, way different from other biggies of current programming languages.

What is Functional Languages?

Haskell is a functional language. Functional programming is a programming paradigm that treats computation as the evaluation of mathematical functions and avoids state and mutable data. It emphasizes the application of functions, in contrast to the imperative programming style, which emphasizes changes in state.

Why to Learn Haskell? 

Haskell is very different language then currently popular languages like Java, C#, PHP, JavaScript or C++. And switch is very smooth in between these. In my internship I started working on C++ and there only I switched to Java without any learning or training and it was very smooth transition as Java is very close to C++ and easy compared to C++.

When I started with Haskell it was quite hard for me to grasp its fundamentals i.e. statically typed, laziness and compile time binding of type to variables.

Learning Haskell is a wonderful and very different experience for me. It was like going back to school. Learning new things daily, failing daily and then moving ahead with added experience. And it was fun again to do basic programming.

• Haskell provides shorter cleaner and maintainable code.
• Code written in Haskell is way less buggy then code written in imperative languages i.e. Java/C# etc.
• Haskell is more productive as you can do lot of work with smaller amount of code.

What you need to Learn Haskell?

You need lot patience and open mind to learn Haskell. Don’t expect a easy ride. Expect a joy ride full of thrill.

If you have above qualities then your machine will require a Haskell Compiler so you can try your hands on Haskell.

You can download GHC compiler from here

Or you can install Haskell Platform which is GHC with Batteries from here

Haskell Platform contains GHC compiler plus other utilities

GHCi, RunHaskell, HTTP, CGI and many more

Where to learn Haskell?

TryHaskell.org:: Consist a interactive tutorial to get started with Haskell.

http://learnyouahaskell.com:: A guide for beginners.

http://book.realworldhaskell.org :: A more detailed Haskell book to learn real programming in Haskell with real examples.

Haskell in industry

Many companies are using Haskell in real projects, few of them are

ABN AMRO Amsterdam, The Netherlands

Aetion Technologies LLC Columbus, Ohio

Amgen Thousand Oaks, California

Ansemond LLC

AT&T

Bank of America Merril Lynch

Barclays Capital Quantitative Analytics Group

Credit Suisse Global Modelling and Analytics Group London, UK; New York City

Deutsche Bank Equity Proprietary Trading, Directional Credit Trading

Google

WCF Service Creation With C#

CodeProject

Basic WCF Service Creation in C#.Net => Part1

Hello All, this is the start up guide to create WCF service. This article is a pictorial step by step help to create WCF application.

WCF (Windows Communication Foundation) is a part of .Net 3.0. So you need to install Visual Studio 2008 to use WCF.

It is platform for SOA. WCF provides a way to create loosely coupled application.

WCF is intended to provide services that are distributed and interoperable.

It unifies ASMX and .NET Remoting, MSMQ and can be easily extended to support new protocol.

When we create a Service, to expose the functionality to the world, it needs to be hosted into Host Process. Service Host will be used to expose the end point of the service to other Service or Clients.

Let’s start with the steps...

We will start with VS 2008. Create a File -> New Project -> WCF ->WCFClassLibrary project as shown below

Select WCF in left panel and WCF Service Library in the right panel.

Give appropriate name in the Name Box. Click Ok.

The Project will contain a sample Service1.cs & IService.cs files. Delete it. So that we can add our own service and understand the things in better way.

Right click on MathsLibrary -> Add -> New Item -> Select Class1.cs. 

Rename it to MathsOperations.cs

This will create a simple class file. Open the file. Make the class public.

Similary add one more class IMathsOperations.cs, which will be an interface, which will provide a list of what all operations a WCF service will perform.

Open IMathsOperations.cs and change it to public interface IMathsOperations

And add the below code to it..

To make IMathsOperations as WCF Service Contract, add an attribute [ServiceContract] to it.

Also what all operation you want to make visible to client should be decorated with [OperationContract].

[ServiceContract] and [OperationContract] are included in System.ServiceModel namespace.

Now once decided with the contract, we can implement this interface into our srevice as below

Build the project once you are done up to this.

Let us add app.config to Host solution.

App.config contains endpoint details, which includes ABC (Address, Binding and Contract)

Address is the Address WHERE the service can be found.

Binding the HOW to access the service

Contract is WHAT the service contains.

Now let’s modify App.config. Right click on App.config, click on Edit WCF Configuration

Below popup should appear

Select the Service1 from left panel, following window should show up

Click on ellipses button and go the path where the MathsLibrary.dll exists.

Click on it, it will give you the name of the service it contains.

Similarly go to Endpoints, and select proper contract with same steps as above. 

Also we can change the binding to be used as below…

Select Host on left hand side and it will show you base address, edit the base address to whatever you want..

Once this is done. Build the project.

VS 2008 provides you the way to host the service. We can also host it in console application or Windows Service or IIS.

We will learn those in next parts. Here we will use the host provided by VS 2008.

Now let’s create a client application. Add console application (Or any other project type) project in same solution or different solution.

Add service reference to the MathsService that we have created. It will automatically load all necessary dlls. 

Then with the below code we can give a call to the service..

This will give you below output

In the next sub-sections we will see how to host this service in IIS, Windows services, Console applications etc.

What Firefox Users Want

Firefox was 1st choice of all techy guys before chrome and still it have a large user base and people love it. Still there are few things which users demands in Firefox from other browsers. 
 
Here is the list what Firefox users wants

• This extension and separate/concurrent private browsing tabs and/or windows (Opera has private browsing tabs, which in my opinion are really useful). - by morally_bankrupt77
  
• In Chrome you can have both private and normal session together but in Firefox starting private session suspend normal session and vice varsa. Dont know why private browsing is not implemented that way. - by dvgaba
• Auto-add of search engines and accessing them using tab in the awesome bar.
  When I visit amazon.com in chrome and search for something, and then the next time I open up a tab and start typing amazon.com, there is this option to hit tab and then search within amazon, without having to go to the page first. That is by far one of the things I miss the MOST from chrome - by c0d3M0nk3y
  
• Multi-process architecture, so one tab does not lock or crashes the others. I really don't know why this is not on their top priority list. I'm willing to bet that this will fix all of the complains about Firefox being slow or hanging. All the MemShrink progress is great but Firefox still feels bloated and slower than Chrome. -_rs
• The sandbox. And this is not something only from Chrome, at this moment, Firefox is the only big browser without one, even Internet Explorer has it. by -_rs
  
• hoverzoom image-viewer by Kylde
  
• In-chrome settings but that's already planned. Also, Australis Theme and better built-in personalization tools like Stratiform. by samingue   

Here is what is listed in Firefox Bugzilla

Firefox and great add-ons

Firefox is a great browser to have as your default browser. Great speed, security and always one step ahead in innovation. And Mozilla itself takes a deep interest in users privacy and usability. Feature like "Do Not Track" and BrowserId are created for users. 
Chrome is a decent browser but its a wrapper on webkit only where Firefox is true innovation and real product. Google created Chrome/Android keeping there ads business in mind. In terms of speed Firefox and Chrome are very close to each other but in terms of add-ons Firefox knockout Chrome. Install Firefox is you want innovation in browsers and you dont want to be tracked by Big "G". 

And IE dont have anything to be compared with Firefox/Chrome as its a dead browsers and MS even dont roll new versions to Old versions for there own Old Windows OS. 

• Noscript - Best Add-on to have in your kitty. Want to know why then better try it today. Feel little annoying in starting but trust me it worth the headache. 
• Adblock Plus - Name itself tells what it do. 
•  Firebug - Best friend of all web developers.

Getting Started With ForkJoinPool - Map & Reduce of Java

CodeProject

ForkJoinPool - Java's Map & Reduce

ForkJoinPool ::FJP is a executor service for Fork-Join Taks or tasks which can computed using divide and conqor or we can say FJP is inbuild Map & Reduce framework in Java.
               FJP implements work-steal so all threads try to find and execute task submitted by other tasks in FJP or external program. FJP try to maintain active threads as per number of processor available.
  FJP provides below methods to submit task ::==>

Call Type	External Task Clients i.e. main method	Inner Task Divide and Conquer Calls
	Call from non-fork/join clients	Call from within fork/join computations
Arrange async execution	execute(ForkJoinTask)	ForkJoinTask.fork()
Await and obtain result	invoke(ForkJoinTask)	ForkJoinTask.invoke()
Arrange exec and obtain Future	submit(ForkJoinTask)	ForkJoinTask.fork() (ForkJoinTasks are Futures)

Pre-requiste :: FJP is in-builted in Java 7. You can download and install the same from Oracle.
You can either configure Java in your system path and also set JAVA_HOME to home directory of JDK installation.
Or you can use Eclipse and configure JRE in eclipse.

You can also use FJP with Java 6 but you need to do some configuration to make it work.
1st you need to download JSR166 from here.
Add JSR166 in your classpath in eclipse or using -cp when compling 
And when running your program you need to pass VM Arguments as -Xbootclasspath/p:jsr166.jar in eclipse or using Java command line utility.

Getting Started ::
Classes which we use in our example
ForkJoinPool :: ForkJoinPool is used to create a pool of threads. You can pass number of processers as arguments.
RecursiveTask<V> :: RecursiveTask implements ForkJoinTask and its compute method computes the result and return a result of type V.
RecursiveAction  :: Similar to RecursiveTask but its compute method doesn't return anything means void.
ForkJoinTask     :: Superclass of RecursiveAction and RecursiveTaks. Provides methods like fork, join, invokeAll to create subtaks from current task and submit to Pool.

Creating ForkJoinPool

You can create ForkJoinPool as
ForkJoinPool fjp = new ForkJoinPool(); // Use all available processors
or
ForkJoinPool fjp = new ForkJoinPool(numperOfProcessors); // Use number of processors passed

Creating RecursiveTask<V>
In RecursiveTask compute method is similar to run method of Thread/Runnable and call method of Callable interface.
So we will not call compute method ourselves. But all logic to divide big task into smaller task will be in compute method.
You need to create a subclass of RecursiveTask<V> to start get going. Also we need to pass data to newly created class.
So best way is to create parameterized constuctor. And Store the data at instance level. So compute method can access the data and work on it.

Code With Comments 

package com.thekarna.fjp;

import java.util.Random;
import java.util.concurrent.ForkJoinPool;
import java.util.concurrent.Future;
import java.util.concurrent.RecursiveTask;

/**
 * This class glues all code required for FJP Demo.
 * In many cases given implementation can perform worse then single threaded max finder
 * So don't use this to benchmark, 
 * this is only a Hello World Kind of program to get started with FJP   
 * @author DVG 
 *
 */
public class MaxFinderFJP {
 
 /**
  * This class extends RecursiveTask class
  * RecursiveTask class is quite similar to Thread/Runnable/Callable in context to ExecutorService
  * And compute method of this class is similar to run/call method of Runnable/Callable
  * compute method is the place where you will write your problem solution and also logic to divide & conquer
  * You can also extend RecursiveAction but that return void
  * @author DVG
  *
  * @param <T>
  */
 @SuppressWarnings("all")
 private static class MaxFinderTask<T> extends RecursiveTask<T> {
  private static final int THRESHOLD = 10000;
  private T targetArray[] = null;
  private int low;
  private int high;
  
  /**
   * Parameterized so the callee can pass data
   * @param arr
   */
  public MaxFinderTask(T arr[]) {
   targetArray = arr;
   low = 0;
   high = targetArray.length;
  }
  
  private MaxFinderTask(T arr[], int low, int high) {
   targetArray = arr;
   this.low = low;
   this.high = high;
  }

  /**
   * Bread & Butter for ForkJoinPool 
   * All Logic Go Below
   * @return
   */
  @Override
  protected T compute() {
   
   /*
    If Task is small then compute it normally as diving more then required will
    result in negative way
    */
   if (high - low <=MaxFinderTask.THRESHOLD) {
    return this.computeNormal();
   }
   int mid = (high - low) / 2;
   T result = null;
   T result2 = null;
   //Divide The Big Task in Smaller Task So All Processor Can Get There Share
   MaxFinderTask<T> leftTask = new MaxFinderTask<>(this.targetArray, low, low+mid);
   MaxFinderTask<T> rightTask = new MaxFinderTask<>(this.targetArray, low+mid, high);
   //Fork 1st Task, Fork is a non-blocking call so current thread will move ahead.
   leftTask.fork();
   //Call compute directly which will result in again divide & conquer
   result2 = rightTask.compute();
   //Calling Join Result in blocking call but Divide & conquer will go ahead in newly created leftTask
   result = leftTask.join();
   //Merge Results and Return
   return ((Comparable) result).compareTo(((Comparable) result2)) > -1 ? result
     : result2;
  }
  
  /**
   * This Method Found Max in Normal Way
   * @return
   */
  protected T computeNormal() {
   if (this.targetArray.length == 1) {
    return this.targetArray[0];
   }
   Comparable cMax =  (Comparable)this.targetArray[0];
   for(int i=low; i<high; i++) {
    Comparable obj =  (Comparable)this.targetArray[i];
    if(obj.compareTo(cMax) > 0) {
     cMax = obj;
    }
   }
   return (T)cMax;
  }
 }

 /**
  * This is where all things get going on
  * @param args
  */
 public static void main(String[] args) {
  //Create a array of millions/billion entries
  Integer d[] = new Integer[100_000_0];
  
  //Fill it with random data
  for (int i = 0; i < d.length; i++) {
   Random r = new Random(System.nanoTime());
   d[i] = r.nextInt();
  }
  final long startTime = System.currentTimeMillis();
  //Create a ForkJoinPool
  final ForkJoinPool forkJoinPool = new ForkJoinPool();
  
  //Create Object of MaxFinderTask and Call Invoke which is a blocking call
  //You can also use submit method and can use submit which returns Future
  //final Future<Integer> futureResult = forkJoinPool.submit(new MaxFinderTask<Integer>(d));
  final Integer result = forkJoinPool.invoke(new MaxFinderTask<Integer>(d));
  //final Future<Integer> futureResult = forkJoinPool.submit(new MaxFinderTask<Integer>(d));
  forkJoinPool.shutdown();
  
  System.out.println("Max == " + result);
  System.out.println("Time Taken FJP == " + (System.currentTimeMillis() - startTime));

 }
}