locked
Export numeric and datetime fields into an existing Sheet RRS feed

  • Question

  • I am attempting to export a view from SQL Server 2005 to and Excel 97-2003 spreadsheet. The view has 2 integer and 1 date columns, as well as a number of text columns.

    I created a SSIS package to do this using the Import and Export Wizard. If I output to the default sheet, I can set the datatypes on the 3 fields and they copy into Excel as the integer and datetime datatypes.

    What I'd really like to do is have SSIS copy to an existing sheet where a company logo and other information sits in the first few rows. I can construct a package to perform this copy and the data shows up onto the existing sheet, but the 3 fields in question are all text fields.

    Any help would be appreciated.

    Thanks in advance
    Sunday, September 25, 2011 1:03 AM

Answers

  • Workaround suggestion: Export into a csv flat file and read it into Excel from there.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Proposed as answer by Eileen Zhao Thursday, September 29, 2011 9:18 AM
    • Marked as answer by Eileen Zhao Monday, October 3, 2011 8:28 AM
    Tuesday, September 27, 2011 8:15 PM

All replies

  • Hi,

     If you right-click on the green data flow path arrow and choose Edit, on the Metadata tab of the Data Flow Path Editor dialog box you can see the metadata for all columns in that data flow path in one place. and then you can determine the data types of your two columns if these are non integer columns then you need to add the data conversion or derived column component to explicitly convert it data types to integer.

     

    Regards,

    Zaim Raza.

    Sunday, September 25, 2011 2:08 AM
  • My Data Flow consists of a Source (SQL Server view) going to Data Conversion 1 going to a Destination (Excel existing sheet whose first few rows are all text of various types).

    When I right-click path and Edit, the Metadata shows me that the 3 fields from Data Conversion 1 being mapped to 3 columns in the output are DT_DATE, DT_I4 and DT_I4.

    Yet, when they arrive in the output they are all character strings.

    Could this have anything to do with seeing that the first few columns of the spreadsheet (onto which the data is being appended by SSIS) are character strings?

    Any other thoughts?

    Thanks

    Sunday, September 25, 2011 4:02 PM
  • Further investigation convinces me that the problem is that there are text headings above the columns, which are important to keep. Any ideas?
    Sunday, September 25, 2011 5:01 PM
  • Hi, 

    The best option for you is to create a template excel with the header info before loading the actual data. What you can do is before the ETL move this template file from a location to the destination location and then perform the ETL on this file. What this will do is retain the data in the file that you have written and continue from the next line.

    Ensure that you do not select the property of the connection overwrite existing file & once the ETL is done you move this file to an archieve location for your reference or delete it as per ur requirement..


    My Blog    |      Ask Me     
    Sunday, September 25, 2011 5:29 PM
  • Thank you for your idea.

    I have done virtually nothing with Excel templates. I can take my workbook with the headers in it and save it as an .xlt. Then you say to 

     

    move this template file from a location to the destination location

     

    Do you mean just doing a Windows copy? When I do this. does it remain an .xlt or should I change the name to .xls. I would assume I don't want it to be .xls, but SSIS won't let me use .xlt as destination.

     

    Thanks

    Sunday, September 25, 2011 6:54 PM
  • By Template I dont mean the MS Template file .XLT, I mean a normal XLS file which has the base structure of the file you wish to have. Now when you use this file you can start writing after the original header you provide. Let me know if you have an issue with this.
    My Blog    |      Ask Me     
    Sunday, September 25, 2011 6:58 PM
  • Sadly, I do. What you describe is exactly what I've been trying to do.

     

    I have column names in row 2 for each field say:

    Name    Date   Position   Age

     

    when I use SSIS to copy date from SQL Server appending under the first row, all the data will be text (in SQL Server it's text, date, text and integer).

     

    If I clear the cells containing the words "Date" and "Age", the data goes in as text,date,text and integer, just as it is in SQL Server. So, it appears that the presence of text already in the column causes SSIS to convert the date and integer data to text - which is what I'm trying to keep from happening.

     

     

    Sunday, September 25, 2011 7:40 PM
  • I seriously hate excel for ETL :)

    Having said that can you try by putting a blank row after the header row and set the data type to default. Also for the excel connection try with IMEX=1 and let us know the outcome.


    My Blog    |      Ask Me     
    Sunday, September 25, 2011 7:45 PM
  • I already have a blank line in between. When I add IMEX=1, the export fails with

     

    [Destination - BckOrdr$ [55]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E09.

     

     

    Sunday, September 25, 2011 8:06 PM
  • Workaround suggestion: Export into a csv flat file and read it into Excel from there.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Proposed as answer by Eileen Zhao Thursday, September 29, 2011 9:18 AM
    • Marked as answer by Eileen Zhao Monday, October 3, 2011 8:28 AM
    Tuesday, September 27, 2011 8:15 PM