locked
Numbers converted to text when exporting to Excel RRS feed

  • Question

  • Hi all,

     

    I'm having an issue with data from an OLE DB source being converted from numeric [DT_R8] to text when I export it to Excel.  I had the column in the spreadsheet formatted as Numeric, but when I set it up as the data destination, it is showing in SSIS as [DT_WSTR]. 

     

    The SSIS job runs OK, it just converts my numeric data to text in the spreadsheet.  I'm surprised that the job actually runs, since I have to do explicit data conversions for my OLE DB string data in order to convert it to unicode.  From what I have read elsewhere, the decision was made in SSIS not to use implicit data conversion, but to require explicit conversion.  Yet this export is converting my numeric data to text.

     

    I would appreciate any information on how to set up the Excel Destination to properly handle numeric information.
    Tuesday, December 11, 2007 12:42 AM

Answers

  • Ok, here's my quick and dirty fix. 

     

    I added a second row to my template spreadsheet that is basically a duplicate of my header row, but I replaced all of the dates and numbers with correctly formatted placeholders. (1/1/1900, 0.0).  I then hid that row in the spreadsheet.  On export, the formatting of that second hidden row is picked up and used for subsequent rows.

     

    The downside would be if the end user does a copy and paste, since they will then expose that dummy row, but it is fairly obvious what it is.

     

    From what I've been able to research, there isn't any simple way to delete that dummy row from my Excel spreadsheet in SSIS.

     

    Tuesday, December 11, 2007 10:55 PM

All replies

  • Change the datatypes in SSIS to DT_R8.  You can override those previously chosen data types using the advanced editors.
    Tuesday, December 11, 2007 3:45 AM
  • Hi Phil,

     

    Thanks for the reply.  Please advise how I access the advanced editor for the Excel Destination. 

     

    When I bring up the Excel Destination Editor and go to Mappings, I can see that the destination datatype is [DT_WSTR], but I can't find any option there that allows me to edit it.  I've checked under Package Explorer and brought up the properties of my Excel Connection manager, but it only lets me specify the path to the file, etc. 

     

    Thanks again for your help.

    Tuesday, December 11, 2007 8:03 PM
  • Right-click on the Excel Destination component and select properties.  In the properties window there is a link towards the bottom that says "Show Advanced Editor".  Click on that and ensure that the External and Output column data types are correct for the columns in question.
    Tuesday, December 11, 2007 8:16 PM
  • Hi Phil,

     

    Ok, wasn't looking close enough.  I found the advanced editor, but when I go to Input and Output Properties->External Columns and change the DataType to [DT_R8] and save it, it looks like it works, but when I chech the DataType again it has gone back to DT_WSTR.

     

     

    Tuesday, December 11, 2007 8:17 PM
  •  mrostad wrote:

    Hi Phil,

     

    Ok, wasn't looking close enough.  I found the advanced editor, but when I go to Input and Output Properties->External Columns and change the DataType to [DT_R8] and save it, it looks like it works, but when I chech the DataType again it has gone back to DT_WSTR.

     

     



    Keep going upstream in the data flow looking at all of the components you have.  Somewhere along the line something has converted to DT_WSTR.  It could be the source component -- check there first and then work your way up the stream from the destination.
    Tuesday, December 11, 2007 8:22 PM
  • Hi Phil,

     

    I did a test and rebuilt my connection, using a spreadsheet filled with properly formatted data. After doing that, it correctly exported as [DT_R8].  However, after I deleted the data rows from the spreadsheet and ran it again, it is now exporting that column as [DT_WSTR].  With data in the spreadsheet, it sees the column as DT_R8.  When I purge the data out of the spreadsheet, it goes back to showing the column as DT_WSTR and exporting as text.

     

    What I'm attempting to do here is create a spreadsheet populated with data from our SQL DB, then e-mail the results.  I have a master copy of the .xls file that has the column headers in it, but no data.  In my Control Flow, I make a copy of that master .xls to the .xls file I want to use.  Then I populate that with data.  It looks like my problem here is that without existing data in the .xls, the package is reinterpreting the column types every time it opens the .xls.  Is there any way to get it to lock the column formats? 

     

    Thanks again for the help.

    Tuesday, December 11, 2007 10:15 PM
  • Ok, here's my quick and dirty fix. 

     

    I added a second row to my template spreadsheet that is basically a duplicate of my header row, but I replaced all of the dates and numbers with correctly formatted placeholders. (1/1/1900, 0.0).  I then hid that row in the spreadsheet.  On export, the formatting of that second hidden row is picked up and used for subsequent rows.

     

    The downside would be if the end user does a copy and paste, since they will then expose that dummy row, but it is fairly obvious what it is.

     

    From what I've been able to research, there isn't any simple way to delete that dummy row from my Excel spreadsheet in SSIS.

     

    Tuesday, December 11, 2007 10:55 PM