It works fine in that it INSERT's the data. However, I need it to
UPDATE the table, based upon a ProjectID. Can this be done?
Can a DTS package be fired from a SP using parameters?
Eg UPDATE tProjects SET MyField1=XLS.Sheet1.CellA1,
MyField2=XLS.Sheet2.CellA1 WHERE ProjectID = @.ProjectID.
Also, it must handle dynamic XLS file names, eg 981-Budget.xls,
513-Budget.xls, xyz-Budget.xls
Is this the best way to go? Other suggestions most welcome?
Thanks everyone in advance!Sorry, you also has another question:
"Can a DTS package be fired from a SP using parameters? "
Yes, you have to run the DTSRUn in a cmdshell with XP_cmdshell and hand
over the paramters to global paramerters defined in the DTS package.
(consider the /A switch and look in the BOL for more syntax
information)
HTH, Jens Suessmeyer.|||I would do the querying once and put the data in a temporary table:
<SQLCode>
DECLARE @.Folder varchar(200)
DECLARE @.Filename varchar(200)
DECLARE @.Workbook varchar(200)
DECLARE @.Sqlstring varchar(4000)
SET @.FileName = 'SomeSheet.xls'
SET @.Folder = 'C:\SomeFolder\'
Set @.Workbook = 'SomeWorkbook'
SET @.SQLString = 'SELECT * FROM OpenDataSource(
''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @.Folder + @.FileName +
'";User ID=Admin;Password=;Extended properties=Excel 5.0'')...' +
@.Workbook
Create Table #SomeTable
(
<YourTableDefinitionhere>
)
INSERt INTO #SomeTable
EXEC(@.SQLString)
</Do anything with the data
</SQLCode
HTH, Jens Suessmeyer.
