none
How to Format a excel template sheet column data using ssis RRS feed

  • Question

  • Hi,
    I am filling an excel sheet named sheet1 of a predefined template using SSIS. The template has many other sheets which are calculated using predefined formulas referring sheet 1. Currently I am able to successfully fill the data in sheet1 but other sheets not getting populated until I manually perform Text Column conversion on few columns like Date,Time(hh:mm:ss). Could some one please help me to achieve this task. Can I automate the Text to Column conversion operation after filling the excel.

    Thanks in advance,
    Jithu
    Monday, October 6, 2014 1:59 PM

All replies

  • Hi Jithu,

    If you tried to write the the template the proper (textual) datatypes and the column is and it does not work then

    I only can suggest to write a macro that does the Text Column conversion and have the macro triggered from within the package perhaps better off using a separate process (e.g. a console app that is done using Office interop libraries) as doing it right in the package is quite laborious. 


    Arthur

    MyBlog


    Twitter

    Monday, October 6, 2014 2:29 PM
    Moderator
  • Do you mean that the other sheets with some columns like Date,Time(hh:mm:ss) cannot be populated in Excel Source, only when you change it to text in excel file? Right?

    Tuesday, October 7, 2014 8:22 AM
  • Hi ,

    Ya just trying to go with macro as an option , need work on the macro code to the job 1st.

    Thanks,

    Jithu

    Wednesday, October 8, 2014 6:28 PM
  • We are only filling sheet1 and all the records from sheet1 are referenced in formulas defined in other sheets.

    problem is sheet1 has 1 Date column and 2 Time columns which are not letting the formulas to work, unless Text to Column conversion is applied on these fields.

    Thanks,

    Jithender

    Wednesday, October 8, 2014 6:33 PM
  • have the Excel file processed outside SSIS this is the only way out to doing such things.

    Perhaps leverage the Office Interop type of projects


    Arthur

    MyBlog


    Twitter

    Thursday, October 9, 2014 12:39 AM
    Moderator