ETL | From SQL into Excel using SSIS, Specific Format

Question

I am trying to pull data from SQL into an Excel spreadsheet however, the sheet needs to show the data in a specific format.

I am using functions in SSIS to pull the data onto an excel sheet but I need further transformation to create a specific excel format

This is how I want it to look, EXCEL SPREADSHEET (see screenshot)

Notice the spaces between PRODUCTA and PRODUCTB in the SKU column are BLANK.

I can load the data from SSIS into Excel, but I can not find the functions to transform the excel sheet into this format

Hadi · Accepted Answer · 2019-10-17 16:30:19Z

1

There are two approaches you can go with:

If you have a row_number column (similar to TIER_PRICE shown in the image), you can simply add a derived column with as similar expression:

[TIER_PRICE] != 5 ? NULL(DT_I4) : [TIER_PRICE]

After importing data to Excel, you can write a C# Script Task and use a library like Microsoft.Interop.Excel to apply the transformation you need.

answered Oct 17, 2019 at 16:30

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rodrigo Mendoza · Accepted Answer · 2019-10-17 19:42:31Z

0

Use the data conversion component in ssis toolbox. There change the desired format for each column

answered Oct 17, 2019 at 19:42

Rodrigo Mendoza

265 bronze badges