Wednesday, June 6, 2012

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 :)

Friday, June 1, 2012

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? Consist a interactive tutorial to get started with Haskell. A guide for beginners. :: A more detailed Haskell book to learn real programming in Haskell with real examples.

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