Excel format lost while populating thru SSIS -


i have formatted excel destination 1 of columns being percentage , being currency. i'm loading excel data sql table using ssis. excel not formatted after load. happening?

i able export 2 columns of information (percent , dollar value) excel file (97-2003 format) ssis package created in visual studio 2010. data sourced table on server using sql server 2012.

steps:

  1. created excel file containing column called percent , called cost. both formatted accordingly , located in first row.

  2. saved file in 97-2003 format , closed it.

  3. created table in database , populated couple of records.

     create table test_export (         mypercent numeric(18,2),         mydollar numeric(18,2))
      insert test_export values (2.1, 50.00)     insert test_export values (4.5, 120.00)

  1. opened visual studio 2010 , created new ssis package. created oledb connection database.

  2. under control flow tab added data flow task , double-clicked on after added. should highlight data flow tab.

  3. add ole db source , point newly added database connection, under data access mode select table or view , select table created in step #3. select ok button.

  4. add excel destination making create new excel connection manager , point sheet1$. make indicate first row contains headers. select ok button , connect ole db source task excel destination task below. since numbers involved, there no need apply data conversion task between two. in excel destination task remember select mappings , connect left right source columns match destination columns.

  5. in solution explorer pane, right-click on solution , select properties drop-down menu. open configuration properties on left , select debugging under it. on right under debug options, run64bitruntime , change true false if have not done so. select ok button.

  6. run ssis package.

  7. stop ssis package when of tasks show green completed.

  8. open excel file , contain values imported database table excel percent , dollar formats preserved step #1.

i ran these steps in test , worked perfectly. if formats not being preserved, leaving out step.

hope helps , please indicate if answered question.


-

Comments

Post a Comment