SSIS - how to create a excel with variable name by using template

nononame2021 246 Reputation points
2021-11-17T04:38:15.537+00:00

i would like to create a export excel by using template.

i setup a excel connection and input output variable into expression.
output varialbe: @[User::outputfolderpath]+@[User::outputfilename]+"_"+@[User::datetime]+".xlsx"

in excel destination, i select the above excel connection in the connection manager. after that i don't know how to setup the data access mode so that i can copy the template to output excel and insert data into excel from database.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,860 questions
No comments
{count} votes

5 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 18,896 Reputation points
    2021-11-17T06:30:40.687+00:00

    Hi @nononame2021 ,

    If the output path contains the @[User::datetime], it is timestamp which is always changing, I don't think it will work in your excel destination.

    Why not load data to the excel and then rename it with datetime and move it to another folder.

    Here is a sample you may take a reference.

    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.

    No comments

  2. nononame2021 246 Reputation points
    2021-11-17T06:49:49.57+00:00

    then how can i create a header in advance before loading data into it?

    No comments

  3. nononame2021 246 Reputation points
    2021-11-17T07:18:26.26+00:00

    in excel destination, my export excel file is variable and not fix. it is depended on "exportexcel_MMMYY.xlsx"
    how to setup in excel destination in data access mode as it is variable and don't have physical file.
    how to generate a new excel based on variable "exportexcel_MMMYY.xlsx"

    No comments

  4. nononame2021 246 Reputation points
    2021-11-17T08:01:43.1+00:00

    how can i clone the template to new path and export data to new create excel

    No comments

  5. ZoeHui-MSFT 18,896 Reputation points
    2021-11-17T08:37:24.453+00:00

    Hi @nononame2021 ,

    In the destination manager, choose the property.

    Expression--Excel path--Use your variable path. It should be work.

    150134-untitled.png

    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.