How to add drop downs to excel exports

Category: sql server ssis

Question

minhalraffat on Thu, 26 Sep 2019 14:27:26


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

Replies

ArthurZ on Thu, 26 Sep 2019 14:33:12


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

minhalraffat on Thu, 26 Sep 2019 14:45:03


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

ArthurZ on Thu, 26 Sep 2019 15:06:11


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.

minhalraffat on Thu, 26 Sep 2019 16:17:14


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

Mona Lv on Fri, 27 Sep 2019 07:36:08


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

minhalraffat on Fri, 27 Sep 2019 15:24:32


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

Mona Lv on Thu, 03 Oct 2019 08:50:18


Hi Minhal,

The following link will be helpful:

Export to SSIS and maintain Drop down

Best Regards,

Mona

minhalraffat on Fri, 01 Nov 2019 16:31:39


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