Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2003-01-29 : 09:03:58
|
| I'm using DTS to import an Excel file that is quite dynamic, i.e. the number of columns in the Excel file changes often -- expands and contracts randomly.I have posted earlier on this issue and I understand the basics of DTS now. However, my problem arises after I've hard coded the DTS package and then want to import the latest version of the Excel file, where rows have been added/removed. My SQL table is designed based on the first DTS instance that was run, and, of course, the number of columns in the Excel file has changed since the first package run.Reading up on this, it appears that I need an ActiveX script to handle this dynamically, counting the columns in the target Excel file and then reproducing this in the SQL table dynamically. However, I don't know enough about DTS to understand where in the order this ActiveX script should go, or how I would go about creating such a script. Any ideas?I'm using SQL Server 7.0, my excel file is a single sheet located on a server to which I have full permissions, and all I want to do is import this Excel file on a regular interval, overwriting the existing table on the SQL side each time. Again, columns in the Excel file tend to be added/removed in between each DTS run.Thanks. |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-01-29 : 13:26:01
|
| I've decided that it's much easier, in this specific case, to hit the XLS as a linked server from within SQL Server and just query the worksheet directly using a SELECT INTO. |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-29 : 17:30:35
|
| You can also run an ado query against excel from activex task in dts. |
|
|
|
|
|
|
|
|
DTS transformation for new columns in Excel file?