I am trying to complete what I thought would be a very simple task but after hours of looking thru various articles and attempting different methods, I still have not been able to Write to a specific Cell using SSIS.
All I am trying to do is write "DOB" in cell D2 in an excel sheet.
I tried using SQL COMMAND in the Execute SQL Task componenet to do the UPDATE of the 1 cell but kept getting error messages. Below is the code I tried. SSIS came back with an error saying it was expecting at least 1 paramater…
update [Sheet1$D2:D2] SET F1='DOB'
I also tried
INSERT INTO [Sheet1$D2:D2] VALUES ('DOB')
but got the following error message:
This table contains cells that are outside the range of cells defined in this spreadsheet.
I tried a few different C# and VB scripts but none of them did the trick. Any ideas or suggestions?
I tried modifying the script in the below article to accomplish my task but was unsuccessful
http://bidn.com/blogs/KeithHyer/bidn-blog/2475/updating-a-single-excel-cell-using-ssis
I'm thinking there's got to be an easier way.
Best Answer
Worked. It appears that you can only use UPDATE if there is already data in the cell and you can only use INSERT if the cell is blank (which it was in my case).
There was a lot of very useful information relating to this topic in the following link
http://www.winautomation.com/forum/updating-excel-through-sql-statement