Turning some of columns into rows was one of the tasks had to be done recently. Even though I couldn’t use “SSIS UNPIVOT transformation” for that, I had a chance to play with it. As it is really a useful data flow item for some operations, thought to make a post on it.

The given below is the data contain in a text file.

ProjectName  equipments  transportation  rental  software  hardware
CCN       54632.56       78433.00       9876       0       0
FX5       100547.55       205465.00       99526       78000       45465

Assume we need to load above information structured like below.

ProjectName ExpenseType Amount
CCN  equipments  54632.56
CCN  hardware  0.00
CCN  rental  9876.00
CCN  software  0.00
CCN  transportation  78433.00
FX5  equipments  100547.55
FX5  hardware  45465.00
FX5  rental  99526.00
FX5  software  78000.00
FX5  transportation  205465.00

Simple create a SSIS package and add necessary source (for the text file) and destination items. Then add a UNPIVOT transformation item and set the source output path to it. Open the UNPIVOT transformation editor and set equipments, transportation, rental, software and hardware as Input Column. Do not select ProjectName. Set “Amount” for all Destination Column of all Input Columns. The Pivot Key Value will be same as Input Column name. Enter “ExpenseType” for Pivot key value column name. Set the output of UNPIVOT transformation item to the destination. It is done!

Since my requirement was little bit different, I had to load them to the SQL Server temp table and use UNPIVOT TSQL command. But for scenario like this, this method can be easily applied.

You may be adding Data Conversion item to convert data if the destination is SQL Server.

Even thoug it is a known issue, it is worth to blog. You might have seen this problem before and you might have found the solution too. The problem is loading data from Excel into SQL Server. There are some instances that all columns are not populated with values with what excel file has. For example, if we have a excel file (book1.xls) with three columns like below;

Col1       Col2       Col3
A                5.3
B            454
C           5454
D            454
E              54
F              52
G              5.3
H            564
I               58
J             588          55
K            454        565.56
L              54          55

If we upload this file to SQL Server using import/export wizard, the wizard creats a table with three columns (nvarchar, float and nvarchar) and load all data from Col1 and Col2 but Col3. The thrid column in the table contains null. Even if you use SSIS or procedure with OPENROWSET, you will get the same result.

So, what is the problem? Why it does not fill values for Col3? This is because the type and values are determined by first 8 rows in the excel file. Since the first 8 rows do not contains values, Col3 of all rows are populated with null. If one of first 8 rows are filled, that value is used for determining the type of the column.

Now how we can correct it? The solution is IMEX option. The IMEX has to be added for the connection string with the value of 1. The below code load all data from excel file without going through first 8 rows.

SELECT a.*
INTO ExcelTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\Book1.xls;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]') AS a

I couldn't find any property that I can use for setting this option in SSIS's excel source but can be added the option by opening the package through text editor.

It has been few weeks now, exams and assignments kept me away from posting anything. As usual, I didn't give much priority for them and finally I was running out of time and had had to complete all within few days. The module for the OBAD (which might be easiest one for me; supposed to be developed within two months), developed within two days but couldn't complete the documentation of it. Whom should I blame? Who else? :) Anyway, decided to do other subjects’ properly-on time.

Bought some DVDs, including 24 5th seasons that contains 6 DVDs. Though it was nicely packed, quality is very poor of 24 because it has been captured from TV it seems. Some of other good movies are "Waist Deep - Tyrese Gibson", "Moster House", "Crak".