Vertical Excel Spreadsheet Load to SSIS Package

Question

We have a requirement where the Business will input their data vertically to an Excel Spreadsheet. I'm assuming then we will need to pivot that table to create rows then and then load those rows one by one in order to do our SQL Server Lookups and Inserts Accordingly.

Is that the General idea?

Can anyone tell me the easiest way to then pivot this table and I'm assuming use a Data Task to read each pivoted row and process accordingly.

Example...

ID 123456
Client Client Name
Program Program Name
Shipping Container Special Client Branded Box
Shipping Information 1 UPS
Shipping Information 2 FedEx

Etc...

Answer 1

I would recommend you check the standard Pivot transformation.

Answer 2

Hi @Bobby P ,

You may use Pivot Transformation in SSIS.

Here are two tutorials for your reference.

an-overview-of-ssis-pivot-and-ssis-unpivot-transformation

refresh-excel-pivot-tables-automatically-using-ssis-script-task

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.