sql - Office side automation from SSIS -

please me out. migrating dts 2000 packages sql 2005 version. have package has vb script in it. job of vb script download data avaya server , update templates. post select specific cells in excel templates , update more templates , run stored procedure further update table.

i know can replaced ssis scripting. have come know office side automation not recomended anymore sql server.

so best possible solution. strict requirement update excel templates ssis.

i saw question on over dba.se , seemed interesting approach. installing ace oledb driver, not full blown office install, can use of familiar dml (insert, update, delete) statements modify values in excel. i've used approach query excel table before never thought try modify contents.

after installing driver, create ole db connection excel resource , use execute sql task fire off updates. syntax add value dob cell d2 on sheet1 be

insert [sheet1$d2:d2] values ('dob')  

other examples wordpress article

insert [nameofexcelsheet] values('firsttextcol', 2, '4/11/2009'); -- [i assume first column char field, 2nd col integer, 3rd date]  delete [nameofexcelsheet] secondintcol=2;  update [nameofexcelsheet] set secondintcol = 3 firsttextcol = ‘firsttextcol’; 

references

• http://www.winautomation.com/forum/updating-excel-through-sql-statement
• http://yoursandmyideas.wordpress.com/2011/02/05/how-to-read-or-write-excel-file-using-ace-oledb-data-provider/
• http://bidn.com/blogs/keithhyer/bidn-blog/2475/updating-a-single-excel-cell-using-ssis