How to update edit or change the record in Excel
I have an excel file that has one row with a date in the column. The first row has the header name so I can use it as a column name later in SSIS package and in the second row there is a date which I want to replace with new date at the end of my package execution time. I have tried to delete using the Execute SQL task. I have also tried to use the drop table statement within Execute SQL task. THe drop table deletes all records in sheet which I don't want since I would like to have the column heading not be deleted. I was trying to use the drop table and just name the region A1:A20 as my column name but that didn't work since the excel source could not see the region name. Bottom line the excel file has a date which I capture via execute sql task at the beggining of the package execution and at the end I want to replace, update, delete and insert, etc.. the new timestamp into the file. At any giving point this file would only have on timestamp.
May 25th, 2011 1:17am

Hello , please check this post http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/8d391a6f-68fd-4a15-bbd3-1a4302bd45d5 Thanks http://sqlage.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2011 1:22am

There's a good example here http://jessicammoss.blogspot.com/2008/10/manipulating-excel-spreadsheets-in-ssis.htmlJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: MrWharty.wordpress.com
May 25th, 2011 2:00am

Hello , please check this post http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/8d391a6f-68fd-4a15-bbd3-1a4302bd45d5 Thanks http://sqlage.blogspot.com/ Thanks for your suggestion but I tried this statement Update [Dates$] set Date ="5/30/2011" and I get this error Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "Update [Dates$] set Date ="5/30/2011"" failed with the following error: "Syntax error in UPDATE statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. my Dates$ sheet has "date on A1 and "5/21/2011" on A2. I would like to update A2 with the update statement.
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2011 9:54am

Hi, I think you have to use script to component,this is the script which i have used for one of my solution Public Sub RenameExcelColumnName(ByVal connectionstring As String) Dim excelConnection As OleDbConnection Dim excelCommand As OleDbCommand Dim DynamicSheetName As String = Dts.Variables("RenamedWorkSheetName").Value.ToString Dim cstring As String 'Excel 97-2003 file If connectionstring.Contains("Microsoft.Jet.OLEDB.4.0") Then cstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileName & ";Extended Properties=""Excel 8.0;HDR=NO;""" Else 'Excel 2007 file cstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName & ";Extended Properties=""Excel 12.0;HDR=NO;""" End If excelConnection = New OleDbConnection(cstring) excelConnection.Open() excelCommand = excelConnection.CreateCommand() excelCommand.CommandText = "UPDATE [" + DynamicSheetName.ToString + "I1:I1] SET F1 = 'PHONE#'" excelCommand.CommandType = CommandType.Text excelCommand.ExecuteNonQuery() excelConnection.Close() End Sub  Hope this helps BB
May 25th, 2011 10:27am

Ensure that your sheet name is "Dates". You may be having a problem with the fact that the column name is "Date". Try renaming the column ("datecol" worked for me) or surround the column name with square brackets (that also worked for me). Also try reformatting your date to the SQL/ISO standard of yyyy-MM-dd, and use single quotes. Something like this worked for me: UPDATE [Dates$] SET [Date] = '2011-05-30' Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2011 12:32pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics