I have a source/table mapping document in Excel used in a large datamart application.
I am trying to make the target assignments from the source tables....but there are many of them...some on different servers.
What I would love to do is Open Excel from within T-SQL and have that procedure just append to column names and data types to a designated worksheet.
The trick is that a parameter would be needed to indicate the worksheet is to be cleared first before being appended.
I saw this article : http://www.sqlservercentral.com/articles/Excel/64838/
but it's somewhat dated....and it only performs a cell-at-a-time transfer.
Outside of SSIS, can anyone think of a better way of doing this ?
My intent is to use this tool throughout the project.....as we have over 128 KPI's to develop.....from hundreds of target tables.
Yes, I could do this manually, but that would be brutal..and error-prone as with anything manually done in Excel.