ETL | From SQL into Excel using SSIS, Specific Format

1
votes

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)

see image

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

2
sql-serverexcelssisetl

2 Answers

1
votes

There are two approaches you can go with:

Using a derived column instead of SKU column:

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]

Using a Script Task

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.

0
votes

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