none
How to add drop downs to excel exports RRS feed

  • Question

  • I am creating dynamic excel files with each file consisting of 2 worksheets in it. In both the worksheets I am looking to create a drop down with few values for few of my columns. 

    I am not getting it to be done using ssis excel destination.

    Thanks

    Thursday, September 26, 2019 2:27 PM

All replies

  • Hi there,

    This is because Excel programmability is not part of ETL (SSIS).

    If you are able to program in C# or VB than you can do it:

    https://www.codeproject.com/Tips/1089368/Apply-Data-Validation-to-Excel-Cells-in-Csharp


    Arthur

    MyBlog


    Twitter

    Thursday, September 26, 2019 2:33 PM
    Moderator
  • Hi there,

    This is because Excel programmability is not part of ETL (SSIS).

    If you are able to program in C# or VB than you can do it:

    https://www.codeproject.com/Tips/1089368/Apply-Data-Validation-to-Excel-Cells-in-Csharp


    Arthur

    MyBlog


    Twitter

    Hi Arthur,

    I need to use the oledb data source and excel destination in order to create dynamic excel files and by using the script task I won't be able to today what I am doing right now.

    Thanks,

    Minhal

    Thursday, September 26, 2019 2:45 PM
  • Hi Minhal,

    The data source has nothing to do with what your deliverable is.

    The Excel destination is hardly either. Frankly, you do not need SSIS at all.

    What you need is, either you use an Excel file as a template that you fill in programmatically (although based on the description it is unclear whether this is feasible) or you need a step after the Excel file is generated to programmitcally manipulate on it using the modified code example I gave you.


    Arthur

    MyBlog


    Twitter

    Thursday, September 26, 2019 3:06 PM
    Moderator
  • Hi Minhal,

    The data source has nothing to do with what your deliverable is.

    The Excel destination is hardly either. Frankly, you do not need SSIS at all.

    What you need is, either you use an Excel file as a template that you fill in programmatically (although based on the description it is unclear whether this is feasible) or you need a step after the Excel file is generated to programmitcally manipulate on it using the modified code example I gave you.


    Arthur

    MyBlog


    Twitter

    I am using the ssis package to create dynamic excel files around 300 files with 2 worksheets in each file. My package is working fine for that. All I am looking for is a way to just add a few drop downs under few columns in my existing excel files or before the excel files are created.

    Thanks,

    Minhal

    Thursday, September 26, 2019 4:17 PM
  • Hi minhalraffat,

    It seems that we can just create a drop down in excel files.

    Please refer to How to Create a Drop Down List in Excel.

    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, September 27, 2019 7:36 AM
  • Hi minhalraffat,

    It seems that we can just create a drop down in excel files.

    Please refer to How to Create a Drop Down List in Excel.

    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

    Hi Mona,

    I am looking to create these drop downs in my excel using ssis. When I am doing my excel export I am looking a way to have these drop downs already in there.

    Thanks,

    Minhal

    Friday, September 27, 2019 3:24 PM
  • Hi Minhal,

    The following link will be helpful:

    Export to SSIS and maintain Drop down

    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, October 3, 2019 8:50 AM
  • Hi Minhal,

    The following link will be helpful:

    Export to SSIS and maintain Drop down

    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

    Hi Mona,

    I tried by doing the way you suggested but it is not working. When new excel files are created dynamically it doesn't have the pre-existing drop down lists from the source excel file.

    Thanks,
    Minhal

    Friday, November 1, 2019 4:31 PM