March 7, 2015 at 9:01 am
I have an SSIS pkg. that copies an existing Excel spreadsheet template to numerous new folders and then runs a loop to run a query w/different criteria in each loop to populate each of those new Excel spreadsheets with unique data.
At the end of each spreadsheet's data, I'd like to insert a bolded, red text statement in column "A" that simply reads, "Proprietary Information". I'd like to skip a row after the spreadsheet's last data row to insert this statement.
So, if one spreadsheet has 100 rows of data, I'd like to insert the "Proprietary Information" text in cell A102.
If another spreadsheet gets populated with 207 rows of data, the "Proprietary Information" text needs to be inserted in cell A209.
And so forth...
The machine where this is being developed as well as the production machine where the finished code will run neither have Microsoft Excel installed, so I'm using the OLD DB Connection object in SSIS as far as gaining access to the Excel spreadsheets to populate them. Both the development/production machines also have the Microsoft Access Database Engine 2010 Redistributable component installed on them for purposes of SSIS being able to deal with the Excel spreadsheet files.
I've researched this extensively and tried numerous examples as to how to turn around after population of each spreadsheet and basically do something like "Update [Sheet1$A102:A102] Values 'Proprietary Information'" and this won't work at all. I know my syntax in this example of the "Update" statement is prob. incorrect, but I'm doing this from memory. Regardless of the syntax used for the "Update" statement, when I attempt this via a Execute SQL Task or in a Script Task, it throws various errors.
This would seem to be very simple to do but I'm having zero luck getting this accomplished.
If anyone could point me in a better direction in accomplishing this or if you've done this yourself on a machine using the OLE DB Connection object in SSIS on a machine that doesn't have Excel installed and you've been successful, I'd be highly appreciative of any info. you can provide.
Thanks!
March 7, 2015 at 9:15 am
Update here:
This might be a good article... just found it, but I'm still appreciative of anything else someone might be able to push along info-wise if you've accomplished this successfully.
Thanks again.
http://go4answers.webhost4life.com/Example/update-single-cell-excel-sheet-oledb-91721.aspx
March 7, 2015 at 8:32 pm
brad.mccollum (3/7/2015)
Update here:This might be a good article... just found it, but I'm still appreciative of anything else someone might be able to push along info-wise if you've accomplished this successfully.
Thanks again.
http://go4answers.webhost4life.com/Example/update-single-cell-excel-sheet-oledb-91721.aspx
Without exception, all of those examples use the old "Jet" drivers, which worked quite well but don't work on a 64 bit version of SQL Server. For the 64 bit versions, you have to use the "Ace" drivers and you might just be out of luck on UPDATEs. See the following CONNECT item and understand that I recently tried updates and it always errors out because it's actually a broken feature.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply