Importing Excel data that spans rows

Hi,

I need to import and transform data from an Excel spread sheet where the information spans two rows. The file layout is something like:

Row1Product1 QTY Store1 Store2 Store3 ...
Row2Product1 AMT Store1 Store2 Store3
Row3Product2 QTY Store1 Store2 Store3
Row4Product2 AMT Store1 Store2 Store3

The output would look like

Product1 Store1 QTY AMT
Product1 Store2 QTY AMT
...
Product2 Store1 QTY AMT
Product2 Store2 QTY AMT
...

We currently use a VB6 program using Office Tools to handle this. Is there a way to handle this with the out-of-the-box SSIS?

Thanks in advance,

John

Script Data Flow Component seem like a good candidate. You can probably reuse a lot of your VB code.

Thanks.

|||

Bob,

Do you need Visual Tools for Office 2005 to acess the worksheet from the script task?

jOHN

|||

Not necessarily.

You may get the data as it is using the SSIS Excel Source, and then manipulate the loaded data in the Script Component.

Thanks.

|||Hi John,

As you've already discovered, SSIS does not provide an out-of-the box solution to your problem. I think you may find DataDefractor a useful tool to do just that. It is a custom SSIS data source component designed to extract and normalize data captured in semi-structured data sources such as Excel and CSV data reports. With the help of its example-driven user interface you can map out a flexible schema to extract data straight out of your data source without writing one line of code.

You can download a free trial of DataDefractor at http://www.datadefractor.com

Cheers,
Vassil Kovatchev