March 21, 2011 at 7:03 am
Dear all,
How can I insert the Excel Row number and filename for each row inserted into the DB?
Many tks.
March 21, 2011 at 10:12 am
It's best not to use SQL Server to access Excel. Microsoft does not stand behind any use cases where Excel is being accessed from unattended server-side code:
http://support.microsoft.com/kb/257757
Ideally you should request that files be provided to you in flat-file or XML format (i.e. plain-text). If you have no choice, SSIS is a safer way to access Excel files in an unattended way, however you'll still be working at your own risk implementing Office-automation on a server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 21, 2011 at 10:53 am
I agree with you but
1. Excel format is available (there is no use of Office automation on the server side)....
2. XML also but SSIS refuse to handle it since it says it's too complex... I could pass through a XML->XSLT process and then SSIS but ....
March 21, 2011 at 11:02 am
DZN (3/21/2011)
1. Excel format is available (there is no use of Office automation on the server side)....
No automation in your files is a good thing...you'll still need to install additional drivers to enable accessing the Excel files...not ideal. Avoid it if possible.
DZN (3/21/2011)
2. XML also but SSIS refuse to handle it since it says it's too complex... I could pass through a XML->XSLT process and then SSIS but ....
Too complex? That does not make sense. It sounds like the XML may not be well-formed or may be non-deterministic. Does your data supplier also provide an XSD?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply