2

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.

1
INSERT INTO [Sheet1$D2:D2] VALUES ('DOB') 

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

  • 1
    Have you tried using Linked server /openquery or OPENROWSET ? – Kin Shah May 30 '13 at 20:54
  • I did try Openrowset but it was giving me problems (probably because of Header issues I was having). Also, I have only seen the Openrowset property on the excel destionation and excel source components and I did not want to Import the string "DOB" into the cell, I just wanted to write it to the cell. I like how I can just use the 1 SQL Task component and use 1 line of code to get my desired results. I will probably use the Openrowset for a more complex scenario during import with multiple coloumns. I did not try the other options you mentioned. Thanks for bringing those up, will look into them – Juan Velez May 31 '13 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.