0

I have a SQL Server table with different delivery dates from a set of products, and I was wondering if there's a way to create an Excel destination where the number of columns would depend on how many different delivery dates are.

Input:

PRODUCT         QUANTITY_EXPECTED       DELIVERY_DATE
Prod. A                5                 20/03/2020
Prod. A                10                25/03/2020
Prod. B                200               21/03/2020
Prod. C                10                20/03/2020
Prod. C                10                27/03/2020
Prod. C                10                31/03/2020
Expected Excel Output: 
   PRODUCT        Q_EXP_1      DELIVERY_DATE_1    Q_EXP_2     DELIVERY_DATE_2    Q_EXP_3   DELIVERY_DATE_3
   Prod. A           5             20/03/2020        10           25/03/2020
   Prod. B          200            21/03/2020
   Prod. C           10            20/03/2020        10           27/03/2020        3        31/03/2020

There isn't a fixed maximum of delivery dates for each product, and moreover, this would be a weekly report, so one week there could be 2 different delivery dates for a product, the next week just one, the following 5... Data are completely dynamic.

Any ideas on how could I implement this?

Christopher Moore
  • 15,626
  • 10
  • 42
  • 52
A.L
  • 25
  • 3
  • 1
    I don't think that SSIS handles dynamic metadata for a destination. You should do this using a script (C# or VB) or even [T-SQL](https://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file) – Hadi Mar 19 '20 at 20:16
  • You can't implement dynamic metadata in SSIS. What's the purpose of the excel file - is it a report? You can use other means besides SSIS to generate reports. textfile export using BCP, Powershell, SSRS, Power BI – Nick.Mc Aug 11 '20 at 04:50

0 Answers0