none
How to use a script transformation component to divide buffer data into multiple tabs of the same excel file and download it RRS feed

  • Question

  • I am trying to get the buffer data into an excel file which will multiple tabs. So for example, if the buffer day has employee data with one of the field/column being the city, I would want individual tabs for each city and the employee information in it. So the tab Boston would have all the employees living in Boston. The column "City" can have any number of cities, so this is a dynamic field.

    The approach I want to take is first all loop through the values in the "City" column and save the unique values to an array. Once we get the unique values loop through the data again and save the rows to its corresponding cites. I am new to C# language, but any pointers would be greatly appreciated.


    Subham

    Saturday, November 9, 2019 12:40 AM

All replies

  • Hi Subham,

    We could use Merge Join Transformation in SSIS.

    The following link will be helpful:

    Merge Join Transformation in SSIS

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, November 11, 2019 10:00 AM
  • Hi Mona, 

    could you please elaborate how i can use Merge join transformation in this scenario. Thank you


    Subham

    Tuesday, November 12, 2019 10:11 PM
  • Hi Subham,

    Could you please share the example data of your source and the data you want to get?

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 13, 2019 7:47 AM
  • Hi Mona,

    Thank you for your reply

    This is the data that my buffer (source) has.

    And the data i want to get is an excel file with employee data of each city


    Subham


    Wednesday, November 13, 2019 4:33 PM
  • Hi Subham,

    According to your description, we can use Conditional Split Transformation in SSIS Data Flow Task.

    Please refer to the following link and pictures:

    Conditional Split Transformation in SSIS.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 14, 2019 9:55 AM
  • Hi Mona,

    This is would work if we knew how many city we would have to divide the data to. But the number of unique cities that we would get during every run is dynamic. We could get 2 cities in one run and 20 in the next. How do we address this dynamic requirement? Thank you.


    Subham

    Thursday, November 14, 2019 3:16 PM
  • Hello there,

    Does this need to be done in SSIS? It seems to me this is a simple Excel VBA job, since you will have to automate Excel anyway.

    I would approach it like this :

    1. Create an Excel workbook object. (of course you need an Excel application object first)
    2. Add an empty workbook.
    3. Create a recordset object from the list of distinct cities.
    4. For each city in #3, add a tab to the workbook
    5. For each city in #3, extract corresponding records and write to relevant tab
    6. Save (download) workbook to target location.

    This can also be done in a script task.

    Hope this helps.

    LP


    EaglePrince

    Thursday, November 14, 2019 3:55 PM
  • Hi EaglePrince,

    Thank you for your reply. Yes this has to be done in SSIS. We dont know how many distinct cities there would be in a single run. There could be 5 or 50 cities. I have tried doing it in a script task in SSIS and errors. Can you tell me how it can be done in a script task in SSIS. Thanks.


    Subham

    Thursday, November 14, 2019 4:06 PM
  • Hi Subham,

    Please see: Using The SSIS Script Component With Multiple Outputs

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, November 15, 2019 7:23 AM