ssis outuput zero to excel

nononame2021 256 Reputation points
2021-11-12T02:50:24.503+00:00

I need to output table A in sql server, one of the column define data type with nvarchar (50) and stored '0000' 4 zero in the database. when I output the table record to excel using ssis, it become '0' instead of '0000'

how can I out '0000' to excel?
I am using vs2012.

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

6 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 40,511 Reputation points
    2021-11-12T06:07:07.283+00:00

    Hi @nononame2021 ,

    Please ensure that the excel cell's format is text.

    You may check with below.

    148726-screenshot-2021-11-12-140338.jpg

    Check in your SSIS package to see if you have changed the datatype to numeric which will miss the leading zeros in Excel.

    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.


  2. nononame2021 256 Reputation points
    2021-11-12T06:23:06.697+00:00

    how to check? in data conversion in data flow task to set? would you tell me how to setup in ssis to export '0000' in excel

    show me how to setup?

    0 comments No comments

  3. ZoeHui-MSFT 40,511 Reputation points
    2021-11-12T08:52:36.327+00:00

    Hi @nononame2021 ,

    You may check in advanced editor of the destination manager. Also you may use the data conversion to check. From my side, it works fine.

    148815-screenshot-2021-11-12-165130.jpg

    As you mentioned of csv in the comment, if you import data to csv, do not open the file with Excel, open it with notepad, it could be '0000'.

    148816-screenshot-2021-11-12-164932.jpg

    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.


  4. ZoeHui-MSFT 40,511 Reputation points
    2021-11-15T07:20:18.53+00:00

    Hi @nononame2021 ,

    I think it is related with the setting of the csv file.

    If you want to open the csv in Excel with leading zeros, check below steps.

    Create a new excel file--data--from text--choose the csv file--comma--text.

    Refer this:

    https://provider.www.upenn.edu/computing/da/bo/webi/qna/iv_csvLeadingZeros.html

    149303-screenshot-2021-11-15-151735.jpg

    Noticed that you have raised a lot of issues in Q&A and you are keep asking in Q&A, if the issue has been resolved, don’t forget to click "Accept Answer" on the reply so that we could archive the case. : )

    https://learn.microsoft.com/en-us/answers/support/accepted-answers

    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.


  5. ZoeHui-MSFT 40,511 Reputation points
    2021-11-15T08:54:24.82+00:00

    Hi @nononame2021 ,

    You can add a derived column and have the expression adding leading single quotation mark (').

    I think this could be a workaround when you load into csv it will have leading zeros.

    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.