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

nononame2021 256 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.
2,642 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 40,511 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.

    0 comments No comments

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

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

    0 comments No comments

  3. nononame2021 256 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"

    0 comments No comments

  4. nononame2021 256 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

    0 comments No comments

  5. ZoeHui-MSFT 40,511 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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.